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?

Locatie	Product	30.01.2017	31.01.2017	01.02.2017	02.02.2017	03.02.2017	04.02.2017	05.02.2017
6527	11800	0,740	0,646	1,202	2,012	5,305	3,599	0,000
6527	11801	0,312	0,886	0,951	1,661	2,584	1,715	0,000
6527	12148	0,826	0,765	0,914	1,185	1,860	1,827	0,012
6527	14063	1,340	1,442	4,658	2,949	1,870	3,496	0,000
6527	14064	0,679	3,422	2,369	2,591	2,139	6,627	0,000
6527	15439	0,535	0,496	0,592	0,768	1,205	1,184	0,008

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

After running the code

Locatie	Product	30.01.2017	31.01.2017	01.02.2017	02.02.2017	03.02.2017	04.02.2017	05.02.2017
6527	11800	0.74	0.646	1202,000	2012,000	5305,000	3599,000	0,000
6527	11801	0.312	0.886	0.951	1661,000	2584,000	1715,000	0,000
6527	12148	0.826	0.765	0.914	1185,000	1.86	1827,000	0.012
6527	14063	1.34	1442,000	4658,000	2949,000	1.87	3496,000	0,000
6527	14064	0.679	3422,000	2369,000	2591,000	2139,000	6627,000	0,000
6527	15439	0.535	0.496	0.592	0.768	1205,000	1184,000	0.008

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

thank you very much!!