Excel macro to format report

Solved/Closed
LaurenM Posts 3 Registration date Tuesday April 6, 2010 Status Member Last seen April 6, 2010 - Apr 6, 2010 at 06:16 PM
 ramu sanga - Dec 29, 2014 at 02:08 AM
Hi everyone,

I am new to this forum..........so new I've already typed my question, then clicked "ask your question" again instead of submit..........der! So now I have to try and remember it all over again!

Hopefully I can word what I am looking for in a way that makes sense to everyone. I have a customer report that I run and it exports in an ugly way. It looks like this:
Address
Suburb State Postcode
Delivery Instructions
Phone Number
Fax Number
Email Address

All the above information is in column A, and I would like to create a macro that has the information in column A-F so each heading has a new column.
I can record really really basic macros, so I will probably need someone to write one for me so I can simply copy and paste *sorry*

Hopefully this is really simple to someone and they are willing to help me out. Any suggestions/advice is greatly appreciated!

Thanks,

Lauren

2 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 6, 2010 at 06:23 PM
Why you need macro. How about using Transpose functionality when you do copy and then select pastespecial
0
LaurenM Posts 3 Registration date Tuesday April 6, 2010 Status Member Last seen April 6, 2010
Apr 6, 2010 at 06:26 PM
Tried that! Silly thing just ends up putting all the data from A1 into B1 and instead of it being as above it now reads:
Address Suburb State Postcode Delivery Instructions Phone Number Fax Number Email Address
Very frustrating!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 6, 2010 at 07:06 PM
could you post your sample book at some share site like https://authentification.site and post here the link to the file
0
LaurenM Posts 3 Registration date Tuesday April 6, 2010 Status Member Last seen April 6, 2010
Apr 6, 2010 at 07:18 PM
Ok done.
I have deleted all the information except for the first line due to privacy of customers and changed the information, but this will give you the idea of what the silly thing is doing!
Here is the link:
https://authentification.site/files/21815119/Xl00000001.xls
And here is the password I was given:
catazubajeji

Thanks so much for taking the time!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 7, 2010 at 05:47 AM
Ok, You do need a macro to do it. It is because as I suspected the data is in one row of excel. Since you had only one row, I dont know if every thing else is on same row or each address block on listed on a row,
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 7, 2010 at 09:20 AM
Objective:
To splits lines is a cell and move each line to a new cell on the same row.

Assumptions
1. Data start at ROW 2
2. The data is in column A
3. Column next to A is where the data needs to be populated
4. Each line in the data cells should be moved to a new cell in the same row

Steps:
1. Read the assumptions
2. Make a backup of the file
3. Press ALT + F11 and insert a new module
4. Paste the code (after steps)
5. Execute the code

Code:


Sub SpiltData()

Dim lMaxRows As Long
Dim lRowBeanCounter As Long
Dim vPos As Variant
Dim sHold As String
Dim sTemp As String
Dim iCellCounter As Integer
Dim lStartAtRow As Long
    
    lStartAtRow = 1
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    For lRowBeanCounter = lStartAtRow To lMaxRows
    
        sTemp = Cells(lRowBeanCounter, "A")
        iCellCounter = 2
        
        Do While sTemp <> ""
            vPos = 0
            vPos = InStr(1, sTemp, Chr(10))
            
            If vPos > 0 Then
                sHold = Left(sTemp, vPos - 1)
                sTemp = Trim(Mid(sTemp, vPos + 1))
            Else
                sHold = sTemp
                sTemp = ""
            End If
            
            iCellCounter = iCellCounter + 1
            Cells(lRowBeanCounter, iCellCounter) = sHold
        Loop
        
    
    Next lRowBeanCounter
End Sub
0
Not working
0