CODEWITHSUNDEEP
excelvbarange
55bg
55bg

Reputation: 3

VBA Runtime error 1004 while setting a range

Basically I'm trying to optimize the process of copying values in dynamic ranges.

This is what I currently have and it works:

For i = 0 To 6
    Worksheets("S").Cells(wstaty, (4 + i)).Value = Worksheets("B").Cells(id, (13 + i)).Value
Next i

I would like to replace iteration with faster method, which example is:

Range("A1:A5").Value = Range("B1:B5").Value

But when I try:

Worksheets("S").Range(Cells(wstaty, 4), Cells(wstaty, 10)).Value = Worksheets("B").Range(Cells(id, 13), Cells(id, 19)).Value

I get

Run-time error 1004

So I've decomposited the statement to find out where problem is, so I have:

Dim r1, r2 As Range

        Set r1 = Worksheets("S").Range(Cells(wstaty, 4), Cells(wstaty, 10))
        Set r2 = Worksheets("B").Range(Cells(id, 13), Cells(id, 19))  ' <-- error here
        r1.Value = r2.Value

and debugger says an error occurs at Set r2 line, which doesn't make much sense to me

(variable ID is always > 2 )

Upvotes: 0

Views: 208

Answers (1)

fourthquartermagic
fourthquartermagic

Reputation: 135

Cells(…) is related to the current worksheet. So to be safe you better always address a range with its full name. In your example it would be

Worksheets("B").Range(Worksheets("B").Cells(…), Worksheets("B").Cells(…)) etc.

To avoid lengthy code I would propose to use a variable for the sheet instead.

Upvotes: 1

Related Questions