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

[Stefan vd Kerkhof]

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:

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

 

[Norie]

Perhaps.

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]

that easy.... i'm embarrassed
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]

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?


[TABLE="width: 714"]
<colgroup><col span="2"><col span="7"></colgroup><tbody>[TR]
[TD]Locatie[/TD]
[TD]Product[/TD]
[TD]30.01.2017[/TD]
[TD]31.01.2017[/TD]
[TD]01.02.2017[/TD]
[TD]02.02.2017[/TD]
[TD]03.02.2017[/TD]
[TD]04.02.2017[/TD]
[TD]05.02.2017[/TD]
[/TR]
[TR]
[TD]6527[/TD]
[TD]11800[/TD]
[TD]0,740[/TD]
[TD]0,646[/TD]
[TD]1,202[/TD]
[TD]2,012[/TD]
[TD]5,305[/TD]
[TD]3,599[/TD]
[TD]0,000[/TD]
[/TR]
[TR]
[TD]6527[/TD]
[TD]11801[/TD]
[TD]0,312[/TD]
[TD]0,886[/TD]
[TD]0,951[/TD]
[TD]1,661[/TD]
[TD]2,584[/TD]
[TD]1,715[/TD]
[TD]0,000[/TD]
[/TR]
[TR]
[TD]6527[/TD]
[TD]12148[/TD]
[TD]0,826[/TD]
[TD]0,765[/TD]
[TD]0,914[/TD]
[TD]1,185[/TD]
[TD]1,860[/TD]
[TD]1,827[/TD]
[TD]0,012[/TD]
[/TR]
[TR]
[TD]6527[/TD]
[TD]14063[/TD]
[TD]1,340[/TD]
[TD]1,442[/TD]
[TD]4,658[/TD]
[TD]2,949[/TD]
[TD]1,870[/TD]
[TD]3,496[/TD]
[TD]0,000[/TD]
[/TR]
[TR]
[TD]6527[/TD]
[TD]14064[/TD]
[TD]0,679[/TD]
[TD]3,422[/TD]
[TD]2,369[/TD]
[TD]2,591[/TD]
[TD]2,139[/TD]
[TD]6,627[/TD]
[TD]0,000[/TD]
[/TR]
[TR]
[TD]6527[/TD]
[TD]15439[/TD]
[TD]0,535[/TD]
[TD]0,496[/TD]
[TD]0,592[/TD]
[TD]0,768[/TD]
[TD]1,205[/TD]
[TD]1,184[/TD]
[TD]0,008[/TD]
[/TR]
</tbody>[/TABLE]

After running the code

[TABLE="width: 714"]
<colgroup><col span="2"><col span="7"></colgroup><tbody>[TR]
[TD]Locatie[/TD]
[TD]Product[/TD]
[TD] 30.01.2017[/TD]
[TD]31.01.2017[/TD]
[TD]01.02.2017[/TD]
[TD]02.02.2017[/TD]
[TD]03.02.2017[/TD]
[TD]04.02.2017[/TD]
[TD]05.02.2017[/TD]
[/TR]
[TR]
[TD="align: right"]6527[/TD]
[TD="align: right"]11800[/TD]
[TD] 0.74[/TD]
[TD]0.646[/TD]
[TD]1202,000[/TD]
[TD]2012,000[/TD]
[TD]5305,000[/TD]
[TD]3599,000[/TD]
[TD]0,000[/TD]
[/TR]
[TR]
[TD="align: right"]6527[/TD]
[TD="align: right"]11801[/TD]
[TD] 0.312[/TD]
[TD]0.886[/TD]
[TD]0.951[/TD]
[TD]1661,000[/TD]
[TD]2584,000[/TD]
[TD]1715,000[/TD]
[TD]0,000[/TD]
[/TR]
[TR]
[TD="align: right"]6527[/TD]
[TD="align: right"]12148[/TD]
[TD] 0.826[/TD]
[TD]0.765[/TD]
[TD]0.914[/TD]
[TD]1185,000[/TD]
[TD]1.86[/TD]
[TD]1827,000[/TD]
[TD]0.012[/TD]
[/TR]
[TR]
[TD="align: right"]6527[/TD]
[TD="align: right"]14063 [/TD]
[TD] 1.34[/TD]
[TD]1442,000[/TD]
[TD]4658,000[/TD]
[TD]2949,000[/TD]
[TD]1.87[/TD]
[TD]3496,000[/TD]
[TD]0,000[/TD]
[/TR]
[TR]
[TD="align: right"]6527[/TD]
[TD="align: right"]14064 [/TD]
[TD]0.679[/TD]
[TD]3422,000[/TD]
[TD]2369,000[/TD]
[TD]2591,000[/TD]
[TD]2139,000[/TD]
[TD]6627,000[/TD]
[TD]0,000[/TD]
[/TR]
[TR]
[TD="align: right"]6527[/TD]
[TD="align: right"]15439[/TD]
[TD] 0.535[/TD]
[TD]0.496[/TD]
[TD]0.592[/TD]
[TD]0.768[/TD]
[TD]1205,000[/TD]
[TD]1184,000[/TD]
[TD]0.008[/TD]
[/TR]
</tbody>[/TABLE]

thank you very much!!