Excel VBA - Method 'Range' of object'_Worksheet' failed

Question

I have 3 subs that are run when a certain worksheet is activated within a workbook. However, it fails and I receive the following error:

Run-time error '-2147417848 (80010108)': Method 'Range' of object'_Worksheet' failed

The code that errors is:

Sub MoveData()
    Dim r As Long, lr2 As Long, lr As Long
    lr2 = Worksheets("Status").UsedRange.Rows.Count
    lr3 = Worksheets("Completed").UsedRange.Rows.Count
        If lr3 = 1 Then lr3 = 0
        For r = lr2 To 2 Step -1
            If Worksheets("Status").Range("A" & r).Value <> "" And Worksheets("Status").Range("X" & r).Value = "Y" And Worksheets("Completed").Range("A2").Value = "" Then
                Worksheets("Status").Cells(r, 1).Copy Destination:=Worksheets("Completed").Range("A" & lr3)
                Worksheets("Status").Cells(r, 2).Copy Destination:=Worksheets("Completed").Range("B" & lr3)
                Worksheets("Status").Cells(r, 3).Copy Destination:=Worksheets("Completed").Range("C" & lr3)
                Worksheets("Status").Cells(r, 4).Copy Destination:=Worksheets("Completed").Range("D" & lr3)
                Worksheets("Status").Cells(r, 5).Copy Destination:=Worksheets("Completed").Range("E" & lr3)
                Worksheets("Status").Cells(r, 6).Copy Destination:=Worksheets("Completed").Range("F" & lr3)
                Worksheets("Status").Cells(r, 7).Copy Destination:=Worksheets("Completed").Range("G" & lr3)
                Worksheets("Status").Cells(r, 8).Copy Destination:=Worksheets("Completed").Range("H" & lr3)
                Worksheets("Status").Cells(r, 9).Copy Destination:=Worksheets("Completed").Range("I" & lr3)
                Worksheets("Status").Cells(r, 10).Copy Destination:=Worksheets("Completed").Range("J" & lr3)
                Worksheets("Status").Cells(r, 11).Copy Destination:=Worksheets("Completed").Range("K" & lr3)
                Worksheets("Status").Cells(r, 12).Copy Destination:=Worksheets("Completed").Range("L" & lr3)
                Worksheets("Status").Cells(r, 13).Copy Destination:=Worksheets("Completed").Range("M" & lr3)
                Worksheets("Status").Cells(r, 14).Copy Destination:=Worksheets("Completed").Range("N" & lr3)
                Worksheets("Status").Cells(r, 15).Copy Destination:=Worksheets("Completed").Range("O" & lr3)
                Worksheets("Status").Cells(r, 16).Copy Destination:=Worksheets("Completed").Range("P" & lr3)
                Worksheets("Status").Cells(r, 17).Copy Destination:=Worksheets("Completed").Range("Q" & lr3)
                Worksheets("Status").Cells(r, 18).Copy Destination:=Worksheets("Completed").Range("R" & lr3)
                Worksheets("Status").Rows(r).Delete Shift:=xlUp
                Else:
            End If
        Next r
        For r = lr2 To 2 Step -1
            If Worksheets("Status").Range("A" & r).Value <> "" And Worksheets("Status").Range("T" & r).Value = "Y" And Worksheets("Completed").Range("A2").Value <> "" Then
                Worksheets("Status").Cells(r, 1).Copy Destination:=Worksheets("Completed").Range("A" & lr3 + 1)
                Worksheets("Status").Cells(r, 2).Copy Destination:=Worksheets("Completed").Range("B" & lr3 + 1)
                Worksheets("Status").Cells(r, 3).Copy Destination:=Worksheets("Completed").Range("C" & lr3 + 1)
                Worksheets("Status").Cells(r, 4).Copy Destination:=Worksheets("Completed").Range("D" & lr3 + 1)
                Worksheets("Status").Cells(r, 5).Copy Destination:=Worksheets("Completed").Range("E" & lr3 + 1)
                Worksheets("Status").Cells(r, 6).Copy Destination:=Worksheets("Completed").Range("F" & lr3 + 1)
                Worksheets("Status").Cells(r, 7).Copy Destination:=Worksheets("Completed").Range("G" & lr3 + 1)
                Worksheets("Status").Cells(r, 8).Copy Destination:=Worksheets("Completed").Range("H" & lr3 + 1)
                Worksheets("Status").Cells(r, 9).Copy Destination:=Worksheets("Completed").Range("I" & lr3 + 1)
                Worksheets("Status").Cells(r, 10).Copy Destination:=Worksheets("Completed").Range("J" & lr3 + 1)
                Worksheets("Status").Cells(r, 11).Copy Destination:=Worksheets("Completed").Range("K" & lr3 + 1)
                Worksheets("Status").Cells(r, 12).Copy Destination:=Worksheets("Completed").Range("L" & lr3 + 1)
                Worksheets("Status").Cells(r, 13).Copy Destination:=Worksheets("Completed").Range("M" & lr3 + 1)
                Worksheets("Status").Cells(r, 14).Copy Destination:=Worksheets("Completed").Range("N" & lr3 + 1)
                Worksheets("Status").Cells(r, 15).Copy Destination:=Worksheets("Completed").Range("O" & lr3 + 1)
                Worksheets("Status").Cells(r, 16).Copy Destination:=Worksheets("Completed").Range("P" & lr3 + 1)
                Worksheets("Status").Cells(r, 17).Copy Destination:=Worksheets("Completed").Range("Q" & lr3 + 1)
                Worksheets("Status").Cells(r, 18).Copy Destination:=Worksheets("Completed").Range("R" & lr3 + 1)
                Worksheets("Status").Rows(r).Delete Shift:=xlUp
                lr3 = lr3 + 1
            Else:
            End If
        Next r
End Sub

The highlighted line from debug is:

If Worksheets("Status").Range("A" & r).Value <> "" And Worksheets("Status").Range("X" & r).Value = "Y" And Worksheets("Completed").Range("A2").Value = "" Then

Would you be able to provide an explanation of why the error is occurring and what I can do to fix it?

That whole code probably doesn't error, just a specific line or two, so which is the line that's highlighted when it errors? — Tim Williams
– Tim Williams, Commented Feb 23, 2017 at 20:43
If lr3 = 1 Then lr3 = 0 lr3 cannot be zero - that doesn't resolve to any valid row. Also you never increment lr3 ? — Tim Williams
– Tim Williams, Commented Feb 23, 2017 at 20:48
@TimWilliams I've updated the post and indicated the highlighted line and the rest of the code where it's incrementing. — Sol_Kralj
– Sol_Kralj, Commented Feb 23, 2017 at 20:54

Sol_Kralj · Accepted Answer · 2017-02-23 21:20:46Z

1

I fixed the error by removing Range from if:

Old vba

If Worksheets("Status").Range("A" & r).Value <> "" And Worksheets("Status").Range("X" & r).Value = "Y" And Worksheets("Completed").Range("A2").Value = "" Then

New vba

If Worksheets("Status").Cells(r, 1).Value <> "" And Worksheets("Status").Cells(r, 24).Value = "Y" And Worksheets("Completed").Cells(2, 1).Value = "" Then

answered Feb 23, 2017 at 21:20

Sol_Kralj

1341 gold badge6 silver badges23 bronze badges

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

3 Comments

Tim Williams Over a year ago

It's difficult for me to see why those two lines aren't functionally identical...

Sol_Kralj Over a year ago

I'm struggling to find a link, but what I understood is that range wasn't working because the worksheet wasn't active?

feetwet Over a year ago

I had the same problem, and I don't understand why the two expressions aren't functionally identical, but the Cells variant worked for me. In my case also I did not have the referenced worksheet active.

Dan Donoghue · Accepted Answer · 2017-02-23 21:00:01Z

0

How many rows are in Worksheets("Status")

From that error I would imagine it is either zero or one. Has to be 2 or more with your loop

For r = lr2 To 2 Step -1

answered Feb 23, 2017 at 21:00

Dan Donoghue

6,2662 gold badges20 silver badges36 bronze badges

3 Comments

Sol_Kralj Over a year ago

There are 4 rows in the worksheet I am testing with. Once completely populated there will likely never be less than 100 in it at any given moment.

YowE3K Over a year ago

UsedRange.Rows.Count won't return 0 but, even if it did (or if it returned 1), the For r = lr2 To 2 Step -1 loop would never be entered, and therefore code would never reach the line at which the error occurs.

Sol_Kralj Over a year ago

Ok. I'm a little confused by that because when I have the Status worksheet active, I do not receive any errors and it accomplishes what I needed it to. From your explanation, I should be receiving errors always with this code?

Dylan Ellis · Accepted Answer · 2024-07-10 16:19:58Z

It seems that you are referring to column values ( A, B, C, D, etc.) when referencing cells (r, 1), (r, 2), (r, 3), (r, 4), etc. rather than the row values, which is what lr2 is initialized to represent. You might be to fix this potential referencing error by reformatting the:

Worksheets("Status").Cells(r, 1).Copy Destination:=Worksheets("Completed").Range("A" & lr3 + 1) Worksheets("Status").Cells(r, 2).Copy Destination:=Worksheets("Completed").Range("B" & lr3 + 1) Worksheets("Status").Cells(r, 3).Copy Destination:=Worksheets("Completed").Range("C" & lr3 + 1

To the following code:

Worksheets("Status").Cells(1, r).Copy Destination:=Worksheets("Completed").Range("A" & lr3 + 1) Worksheets("Status").Cells(2, r).Copy Destination:=Worksheets("Completed").Range("B" & lr3 + 1) Worksheets("Status").Cells(3, r).Copy Destination:=Worksheets("Completed").Range("C" & lr3 + 1

Additionally, the Shift:=xlUp found in the line:

Worksheets("Status").Rows(r).Delete Shift:=xlUp

might be unnecessary, since I think the row that is being deleted is at the bottom of the tabular data.

Collectives™ on Stack Overflow

Excel VBA - Method 'Range' of object'_Worksheet' failed

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related