Object defined error?

[cheoksoon]

Hi there,

I'm baffled by a seemingly trivial problem in VBA, maybe I didn't define something? I was hoping ya'll can help shed light on this.

Basically, this code works:

Dim roster As Worksheet
With ThisWorkbook
        Set roster = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        roster.Name = "Day shift"
End With
roster.Range("A1") = some value

But this code doesn't work?

Dim roster As Worksheet
With ThisWorkbook
        Set roster = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        roster.Name = "Day shift"
End With
roster.Range(Cells(1, 1), Cells(1, 2)) = some value

It says runtime error 1004.

Am I missing something?

 

[Fluff]

You need to fully qualify the range like

roster.Range(roster.Cells(1, 1), roster.Cells(1, 2)) = some value

or

With roster
    .Range(.Cells(1, 1), .Cells(1, 2)) = some value
End With

 

[cheoksoon]

You need to fully qualify the range like

roster.Range(roster.Cells(1, 1), roster.Cells(1, 2)) = some value

or

With roster
    .Range(.Cells(1, 1), .Cells(1, 2)) = some value
End With

Worked like a charm.

Thanks Fluff! Merry Christmas!

 

[Fluff]

You're welcome & thanks for the feedback