Easier way converting text to columns using VBA. I'm a VBA beginner.

Stefan vd Kerkhof

New Member
Joined
Jan 31, 2017
Messages
3
Hello everyone,

I'm running this code to convert a range of columns (from personal.xlsb). The reason for using this code is that it only converts numbers to text and doesn't change for example dates.

It works but there has to be another way to let the code loop or run until the last filled column (row 1 always contains data) i think.

Can you help a new guy out?
Thanks a lot!!

Fragment of code:

Code:
Sub Converteer_getal_2()
'
' getal_converter Macro


' tekstnaargetallen Macro


    Application.CutCopyMode = False
    ActiveSheet.Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True


        ActiveSheet.Columns(2).TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        
        ActiveSheet.Columns(3).TextToColumns Destination:=Range("c1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        
        ActiveSheet.Columns(4).TextToColumns Destination:=Range("d1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Perhaps.
Code:
Sub Converteer_getal_2()
Dim col As Range
'
' getal_converter Macro


' tekstnaargetallen Macro


    Application.CutCopyMode = False

    For Each col In ActiveSheet.UsedRange.Columns
        col.TextToColumns Destination:=col.Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 1), TrailingMinusNumbers:=True
    Next col

End Sub
 

Stefan vd Kerkhof

New Member
Joined
Jan 31, 2017
Messages
3
that easy.... i'm embarrassed :eek:
I was searching the entire evening for a solution on all kind of forums, you made my day

Thank you very much!!! This makes my life easier haha
 

Stefan vd Kerkhof

New Member
Joined
Jan 31, 2017
Messages
3
Hello again,

One more question, if I run the code on for example the file below.
It seems it doesn't understand the comma.
It changes values above 1,000 to 1000 but not in every cell, and values below 0,999 it changes the comma into a dot but also not in every cell.

I pasted the results after the code below.

Can this be fixed?


LocatieProduct30.01.201731.01.201701.02.201702.02.201703.02.201704.02.201705.02.2017
6527118000,7400,6461,2022,0125,3053,5990,000
6527118010,3120,8860,9511,6612,5841,7150,000
6527121480,8260,7650,9141,1851,8601,8270,012
6527140631,3401,4424,6582,9491,8703,4960,000
6527140640,6793,4222,3692,5912,1396,6270,000
6527154390,5350,4960,5920,7681,2051,1840,008

<colgroup><col span="2"><col span="7"></colgroup><tbody>
</tbody>

After running the code

LocatieProduct 30.01.201731.01.201701.02.201702.02.201703.02.201704.02.201705.02.2017
652711800 0.740.6461202,0002012,0005305,0003599,0000,000
652711801 0.3120.8860.9511661,0002584,0001715,0000,000
652712148 0.8260.7650.9141185,0001.861827,0000.012
652714063 1.341442,0004658,0002949,0001.873496,0000,000
652714064 0.6793422,0002369,0002591,0002139,0006627,0000,000
652715439 0.5350.4960.5920.7681205,0001184,0000.008

<colgroup><col span="2"><col span="7"></colgroup><tbody>
</tbody>

thank you very much!!
 

Forum statistics

Threads
1,141,316
Messages
5,705,708
Members
421,406
Latest member
kluna90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top