Experiencing Run-time error '1004': Application-defined or object-defined error in VBA

Question

this vba code to loop through and copy and paste a new line every until it finds a zero keeps giving me a Run-time error '1004'. I can't quite figure out what is causing it. It breaks at Sheets("Regular Invoice").Range("A29:N29").Copy (According to using the F8 key) Any help is greatly appreciated.

Sub BoxTest()

Dim testRow As Integer
Dim pasteRow As Integer


testRow = 2
pasteRow = 30
Worksheets("Boxes").Select
Do Until Worksheets("Boxes").Range(testRow, 13).Value = 0

Sheets("Regular Invoice").Range("A29:N29").Copy
Worksheets("Regular Invoice").Cells(pasteRow, 1).PasteSpecial xlPasteAll

testRow = testRow + 1
pasteRow = pasteRow + 1

Loop
testRow = testRow + 1
pasteRow = pasteRow + 1


End Sub

MutjayLee · Accepted Answer · 2016-06-06 14:58:28Z

3

This line is the problem

Do Until Worksheets("Boxes").Range(testRow, 13).Value = 0

you can do either

Do Until Worksheets("Boxes").Cells(testRow, 13).Value = 0

or

Do Until Worksheets("Boxes").Range("M" & testRow).Value = 0

Only Cells takes the range as Row Number and Column Number.

Range Only takes number as Row, and Columns always have to be Letter

answered Jun 6, 2016 at 14:58

MutjayLee

5683 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tokioi Over a year ago

This was a working solution, thanks for the explanation on cells and range. Really helpful.

Ralph · Accepted Answer · 2016-06-06 19:19:45Z

1

While @MutjayLee has already an excellent answer I'd still like to throw in this additional solution for your consideration:

Sub BoxTest()

Dim testRow As Integer
Dim pasteRow As Integer
Dim lngLastRow As Long

pasteRow = 30

With ThisWorkbook.Worksheets("Boxes")
    .Activate
    lngLastRow = .Cells(.Rows.Count, 13).End(xlUp).Row
    For testRow = 2 To lngLastRow
        If .Cells(testRow, 13).Value = 0 Then Exit For
        With ThisWorkbook.Sheets("Regular Invoice")
            .Range("A29:N29").Copy Destination:=.Cells(pasteRow, 1)
        End With
        pasteRow = pasteRow + 1
    Next testRow
End With

'Not really necessary
'testRow = testRow + 1
'pasteRow = pasteRow + 1

End Sub

It avoids the Do ... Loop and exchanges it with a For ... Next with the option to exit beforehand if your condition is met. These loops are normally preferred as they are definite and not indefinite as Do ... Loop. If the condition is incorrectly set for a Do ... Loop they can easily run indefinitely and crash your Excel.

edited Jun 6, 2016 at 19:19

answered Jun 6, 2016 at 15:31

Ralph

9,4544 gold badges35 silver badges42 bronze badges

2 Comments

Tokioi Over a year ago

I believe that this does work, but it just won't seem to work for me. I keep receiving error 1004 when I try to run this.

Ralph Over a year ago

Sorry, I did it again and used Range when there should have been Cells. I corrected the error. Now it should work.

Collectives™ on Stack Overflow

Experiencing Run-time error '1004': Application-defined or object-defined error in VBA

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related