Error 1004 - Application defined or object defined error when using Sheets in range command

Question

I'm new to VBA and have an issue with a command, I've separated it out in to a separate Excel spreadsheet and still can't get it to work.

If I use the following on the active work sheet all is fine

Range(Cells(1, 1), Cells(99, 2)).Value = "Test"

But when I use:

Sheets("one").Range(Cells(1, 1), Cells(99, 2)).Value = "Test"

I receive the error 1004 application defined or object defined error.

I have the sheet named "one" in my workbook.

I spent the last few hours searching through Google, but no luck, can anyone suggest what is wrong?

Siddharth Rout · Accepted Answer · 2012-09-23 17:34:48Z

4

You have to also qualify the Cells() object

Sub Sample()
    With Sheets("one")
        .Range(.Cells(1, 1), .Cells(99, 2)).Value = "Test"
    End With
End Sub

answered Sep 23, 2012 at 17:34

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

1 Comment

Thank you, I forgot how explicit you have to be with VBA.