CODEWITHSUNDEEP
excelvba
ifnotX
ifnotX

Reputation: 73

VBA TextToColumns - setting destination range from a variabe

Having trouble using TextToColumns with variables.

This works:

IDCol = Rows(8).Find("ID", LookAt:=xlWhole).Column
Columns(IDCol).Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
   Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
   :=Array(1, 1), TrailingMinusNumbers:=True

This doesn't though, and since I don't always know where the column is going to be. The issue seems to be setting the Destination. 

IDCol = Rows(8).Find("ID", LookAt:=xlWhole).Column
Columns(IDCol).Select
Selection.TextToColumns Destination:=Range(Cells(1, IDCol)), DataType:=xlDelimited, _
 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

Thanks if anyone can help.

Upvotes: 0

Views: 1739

Answers (1)

warner_sc
warner_sc

Reputation: 848

Try this:

Sub test()

Dim id_col As Long
id_col = Rows(8).Find("ID", LookAt:=xlWhole).Column

Dim target_col As Range

If id_col <> vbNullString Then

    Set target_col = Columns(id_col)

    With target_col
        .TextToColumns , DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    End With

End If

End Sub

Upvotes: 1

Related Questions