1

I have a table with 5 columns (B to F) and a variable number of filled rows. I want to copy the last 3 filled cells to a fixed range on the same column starting on row 101.

This the code I'm using:

Dim WSPL As Worksheet
 For i = 2 To 6
        For j = 7 To 1 Step -1
            If Not IsEmpty(WSPL.Cells(j, i).Value) Then
                WSPL.Range(Cells(j - 2, i), Cells(j, i)).Copy Destination:=WSPL.Cells(101, i)
                Exit For
            End If
        Next j
    Next i

This is returning error:

Run-time error: 1004
Method 'Range' of object'_Worksheet' failed

On line 5 of my code above. What is wrong with this code?

Improve this question
5
  • Have you tried debugging? Commented Nov 21, 2013 at 10:10
  • where do you set your WSPL ? Commented Nov 21, 2013 at 10:11
  • I set it before the loop above, like: Set WSPL = Worksheets("Sheet2") Commented Nov 21, 2013 at 10:12
  • @NunoNogueira you're looping backward from 7 to 1 , so when your j = 2 the expression j-2 in the loop evaluates to 0 which causes the error. Cant access row or column with an index of 0 Commented Nov 21, 2013 at 10:13
  • @mehow that could be the case but I know that j is never < 4 Commented Nov 21, 2013 at 10:15

1 Answer 1

Reset to default
2

The problem is this line

WSPL.Range(Cells(j - 2, i), Cells(j, i)).Copy Destination:=WSPL.Cells(101, i)

Your cells object is not fully qualified

Try this

With WSPL
    .Range(.Cells(j - 2, i), .Cells(j, i)).Copy Destination:=.Cells(101, i)
End With

Notice the DOTs before Cells?

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

2 Comments

Yes, that solved the problem, although I don't understand why the dots before the Cells
@NunoNogueira When you don't put the dots, the cells will refer to the activesheet but you want to work with the WSPL and not activesheet :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.