VBA: errors with Worksheets object

Question

I have a line of code that sets the reference for a range:

Dim rCheck As Range
Dim lRow As Long
lRow = Worksheets("Sales").Range("salesHeader").Row + 1
Set rCheck = Range(Cells(lRow, 1), Cells(lRow, 3)).End(xlDown)

This works without error. However I would like to set this reference for a range on a specific sheet, Sheet2, or "Sales". When I add the worksheets object I get errors, shown below.

Set rCheck = Worksheets("Sales").Range(Cells(lRow, 1), Cells(lRow, 3)).End(xlDown)

Run-time error '1004': Application-defined or object-defined error

Set rCheck = Worksheets(Sheet2).Range(Cells(lRow, 1), Cells(lRow, 3)).End(xlDown)

Run-time error '13': Type mismatch

Michael Petch · Accepted Answer · 2014-09-27T00:38:10.827

I think a bit of restructuring and you'll have a solution. You effectively want to use "Sales" as the WorkSheet to act upon so I think this is better done using a With statement like this:

With Worksheets("Sales")
   Set rCheck = .Range(.Cells(lRow, 1), .Cells(lRow, 3)).End(xlDown)
End With

Notice that we use .Cells with a period in front. That says we want Cells to operate on the sheet we are using in our With statement. More on that can be found in the documentation:

Be aware that the period in front of each occurrence of the Cells property. The period is required if the result of the preceding With statement is to be applied to the Cells property—in this case, to indicate that the cells are on worksheet one (without the period, the Cells property would return cells on the active sheet).

If however you wanted to use the Cells from the currently active sheet and access Sheet Sales then you could do this:

With Worksheets("Sales")
   Set rCheck = .Range(Cells(lRow, 1), Cells(lRow, 3)).End(xlDown)
End With

I edited your answer. You said you have `.Cells` but I don't see it so I added it :). Feel free to re-edit it though. — L42, Sep 27 '14 at 00:38
In fact I did at the same time you were doing it. At the same time I added the other possibility the OP was looking for (and saw my mistake in the first version) - thanks. — Michael Petch, Sep 27 '14 at 00:39

score 1 · Answer 2 · answered Sep 27 '14 at 00:25

Firstly, this line won't work unless "Sales" is the current active sheet:

Worksheets("Sales").Range(Cells(lRow, 1), Cells(lRow, 3)).End(xlDown)

You could try

Worksheets("Sales").Range(Cells(lRow, 1).Address, Cells(lRow, 3).Address).End(xlDown)

Secondly, you can't refer to Sheet2 using Worksheets(Sheet2). You should use Worksheets("Sheet2") or Worksheets(2)

VBA: errors with Worksheets object

2 Answers2