Offest and FillDown: Run time Error '1004' Method 'Range' of object '_Worksheet' failed

Question

I have a bit of code below. That is getting "Run time Error '1004' Method 'Range' of object '_Worksheet' failed". I can get this to work by using ws.Activate method (see second block), but I would like to understand why this doesn't work.

The failure only occurs: 1) on lines with .FillDown 2) when the code is running on the inactive worksheet.

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Worksheets(1)
Set ws2 = Worksheets(2)

ws1.Range("D2").Formula = "=CONCATENATE(B2,"" "", C2)"
ws1.Range("D2", Range("C2").End(xlDown).Offset(0, 1)).FillDown

ws2.Range("D2").Formula = "=MID(C2, 3, 6)"
ws2.Range("D2", Range("C2").End(xlDown).Offset(0, 1)).FillDown



'''This code works.

Worksheets(1).Activate

Range("D2").Formula = "=CONCATENATE(B2,"" "", C2)"
Range("D2", Range("C2").End(xlDown).Offset(0, 1)).FillDown

Worksheets(2).Activate

Range("D2").Formula = "=MID(C2, 3, 6)"
Range("D2", Range("C2").End(xlDown).Offset(0, 1)).FillDown

SJR SJR · Accepted Answer · 2017-11-03T14:36:23

If you don't specify a sheet for every cells/range reference the code will assume the active sheet applies so your range may cover two sheets, hence the error. You can use the With statement - note the dots in front of the range references, and then you don't need to activate any sheets.

With ws1
    .Range("D2").Formula = "=CONCATENATE(B2,"" "", C2)"
    .Range("D2", .Range("C2").End(xlDown).Offset(0, 1)).FillDown
End With

With ws2
    .Range("D2").Formula = "=MID(C2, 3, 6)"
    .Range("D2", .Range("C2").End(xlDown).Offset(0, 1)).FillDown
End With

Your code could also be shortened to e.g.

ws1.Range("C2", ws1.Range("C2").End(xlDown)).offset(,1).Formula = "=CONCATENATE(B2,"" "", C2)"

Offest and FillDown: Run time Error '1004' Method 'Range' of object '_Worksheet' failed

1 Answers