VBA: Text to column for a specific columns

Question

I'm trying to do a macro that can find a column by the name of the header , then to select entire column and run the "text to column" command.
I've recorded the macro based on the current position of the columns:

Sub TTC()

    Columns("A:A").Select 'text to column and format it as TEXT
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=" ", FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True

    Columns("D:D").Select 'text to column and format it as GENERAL
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=" ", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End Sub

I also have this macro to find the columns number:

Set txt = Sheet1.Cells(1, 1).EntireRow.Find(What:="Text", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
 CT = txt.Column
Set val = Sheet1.Cells(1, 1).EntireRow.Find(What:="Value", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
 CV = val.Column

How can I combine these two macros?

score 1 · Accepted Answer · answered Dec 15 '17 at 10:00

You should not use val as variable name because it is a reserved word.
You should avoid using Select or Selection this is a bad practice.
You should use Option Explicit and declare all your variables.

This should give you an idea how to combine such commands:

Option Explicit

Public Sub TTC()
    'text to column and format it as TEXT
    Dim RangeTxt As Range
    Set RangeTxt = Sheet1.Cells(1, 1).EntireRow.Find(What:="Text", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

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


    'text to column and format it as GENERAL
    Dim RangeVal As Range
    Set RangeVal = Sheet1.Cells(1, 1).EntireRow.Find(What:="Value", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

    Columns(RangeVal.Column).TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=" ", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End Sub

Thank you for the advices! But I still can't make the macro to work. :( — George OST, Dec 15 '17 at 10:59
@GeorgeOST "*can't make macro to work.*" is a useless error description. — Pᴇʜ, Dec 15 '17 at 11:00
`Columns(RangeTxt.Column).TextToColumns Destination:=Cells(1,RangeTxt.Column), DataType:=xlDelimited,` (syntax error) — George OST, Dec 15 '17 at 11:09
Doesn't throw an error for me. Check the value of `RangeTxt.Column` and if there exists a cell containing `Text` and `Value` in the range you run `find`. The code runs without issues for me. Also you showed only a part of the line therefore we cannot say if there is a syntax error or not. — Pᴇʜ, Dec 15 '17 at 11:12
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161291/discussion-between-peh-and-george-ost). — Pᴇʜ, Dec 15 '17 at 11:16

VBA: Text to column for a specific columns

1 Answers1