CODEWITHSUNDEEP
vba
Gilbert
Gilbert

Reputation: 3

Error when splitting columns with macro

I'm new with VBA and I'll appreciate any help. I run a few macros in my document, and one of them is a macro to copy a column with names, paste it on next column right, and use texttocolumns to split it into two, names and last names. When the column it copies the names from is not empty, it runs fine, but if the range is empty it gives

Run-Time Error 1004 TextToColumns method of Range class failed.

Sheets("PUNTA").Select
Range("C2:C45").Select
Selection.Copy
Range("D2:D45").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Columns("D:D").Select

Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Sheets("Tours").Select

End Sub

enter code here

Upvotes: 0

Views: 457

Answers (1)

Scott Craner
Scott Craner

Reputation: 152515

Avoid Select and test whether there are values in the column before using text to columns.

With Sheets("PUNTA")
    .Range("D2:D45").Value = .Range("C2:C45").Value
    If Application.WorksheetFunction.CountA(.Range("D:D")) > 0 Then
        .Range("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    End If
End With

Upvotes: 1

Related Questions