Text To Column with Comma Loop Generating Error when run, but not in debug

Question

I am attempting to loop through data in a sheet and split them on a comma, when I run the script I get a Run Time Error '1004' Application-Defined or Object defined error.

However, When I step into the script to debug and run it step by step it works perfectly. I was wondering if anyone has seen this and could help me in fixing it.

Sub PopulatePayrollForm()

Dim s As String: s = "Payout Review"

If DoesSheetExists(s) Then

Dim BottomRow As Long
Dim c As Range
Dim splitv() As String

Sheets("Pay Form").Range("A6:AR1000").ClearContents

'Copy to another sheet, Split Columns, Copy and paste full name into 2 cells
Worksheets("Payout Review").Range("A2:A1000").Copy Worksheets("Pay Form").Range("AQ6:AQ1006")

BottomRow = Worksheets("Pay Form").Cells(Rows.Count, "AQ").End(xlUp).Row

    Worksheets("Pay Form").Range("AQ6:AQ" & BottomRow).Activate
    
    For Each c In Selection
    splitv = Split(c.Value, ",")
    If UBound(splitv) > 0 Then
        c.Offset(0, -1).Value = splitv(1)
        c.Offset(0, -1).Value = c.Offset(0, -1).Value
        c.Value = splitv(0)
    End If
Next c
        
Worksheets("Pay Form").Range("AP6:AQ" & BottomRow).Copy Worksheets("Pay Form").Range("C6:C" & BottomRow)
Worksheets("Pay Form").Range("AP6:AQ" & BottomRow).Clear

'Copy and paste Employee Id, Payout AMount, Date Range

Worksheets("Payout Review").Range("B2:B1000").Copy Worksheets("Pay Form").Range("A6:A" & BottomRow)
Worksheets("Payout Review").Range("AB2:AB1000").Copy
Sheets("Pay Form").Range("B6:B" & BottomRow).PasteSpecial xlPasteValues
Worksheets("Payout Review").Range("AD1").Copy Worksheets("Pay Form").Range("J6:J" & BottomRow)
Worksheets("Payout Review").Range("AE1").Copy Worksheets("Pay Form").Range("K6:K" & BottomRow)

Sheets("Pay Form").Visible = True


Else
    MsgBox "Data Does not exist"
End If

End Sub

Function DoesSheetExists(sh As String) As Boolean
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sh)
    On Error GoTo 0

    If Not ws Is Nothing Then DoesSheetExists = True
End Function

The use of `Selection` in `For Each c In Selection` is problematic. Use the `Range` in question that you want to loop over, not `Selection`. — BigBen, Jul 27 '20 at 14:57
Thanks BigBen that worked, Can't figure out how to flag yours as the correct answer, I'll work on that now. — Luke Hoffman, Jul 27 '20 at 16:16

score 0 · Accepted Answer · answered Jul 27 '20 at 16:18

The problem is the use of Selection (and Activate):

For Each c In Selection

Just use the Range in question:

For Each c In Worksheets("Pay Form").Range("AQ6:AQ" & BottomRow)

I recommend reading this for a comprehensive discussion of how to avoid Select.

Text To Column with Comma Loop Generating Error when run, but not in debug

1 Answers1