asked on

Text to row

Hello All,
I posted this question before and I got the answer for it, but after further analysis, i found out an error in the result. so please look at the attached file to understand my request. you can also find below the code i have been useing.

Sub MakeTheList()
    
    Dim arr As Variant
    Dim r As Long, c As Long
    Dim DestR As Long
    Dim Countries As Variant
    
    arr = ActiveSheet.UsedRange.Value
    Worksheets.Add
    [a1:e1] = Array("Country Code", "date", "value", "points", "City Code")
    
    DestR = 1
    
    For r = 2 To UBound(arr, 1)
    
    If arr(r, 5) = "" Then
            Cells(DestR, 1) = arr(r, 1)
            Cells(DestR, 2) = arr(r, 2)
            Cells(DestR, 3) = arr(r, 3)
            Cells(DestR, 4) = arr(r, 4)
            Cells(DestR, 5) = arr(r, 5)
           End If
        Countries = Split(arr(r, 5), ",")
        For c = 0 To UBound(Countries)
            DestR = DestR + 1
            Cells(DestR, 1) = arr(r, 1)
            Cells(DestR, 2) = arr(r, 2)
            Cells(DestR, 3) = arr(r, 3)
            Cells(DestR, 4) = arr(r, 4)
            Cells(DestR, 5) = Trim(Countries(c))
           
        Next
    Next
    

End Sub

Open in new window

Problem.xlsx

Jerry Miller

If you format the cells as custom and choose 00 as the format code, it will keep your leading zero's.

jaffan72

ASKER

I am not sure i know how to do that.. can you explain it please.

jaffan72

ASKER

I just tried that, I dont think that would work because all ur doing is adding zero to the beginning of the number, these are area code so it should be exactly as it was. if I add a zero to destination that did not have zeros originally, it will throw everything out of whack

Jerry Miller

Right click the destination cells, choose Format Cells>Custom>select 0 in the listbox. In the type box above, add 00. It will give you a sample above if there is anything in the cell.

I have E8 & E9 formatted that way in my attachment.
Book1.xls

Jerry Miller

You could format them as text, this will keep your format in place. Since they are area codes, I assume that you are not going to perform any math functions on them.

jaffan72

ASKER

thank you for all your help, I just tried formating them as text and its still not working

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial