Why does Range.Cell fail at the first declaration, but not the second?

Question

I'm trying to figure out a 1004 error I'm getting in my Excel macro. The line for set numRange fails if I use the Range(Cells()) format, but succeeds if I use an explicit Range call (i.e. Range("b2")).

Further down the function, I use the same format (and the same variable name), and it works like a charm.

Any help would be appreciated!

Function GetCopyRange(wbName, wsIndex, vnIndex)
    Dim rowsCounter As Integer
    Dim numRows As Integer
    Dim numCols As Integer
    Dim numRange As Range
    
    rowsCounter = 6 'GetStartCell()
    
    Set numRange = Workbooks(wbName).Worksheets(wsIndex).Range(Cells(rowsCounter, 2)) 'This line fails

    [ ... ]

    Set GetCopyRange = Workbooks(wbName).Worksheets(wsIndex).Range(Cells(rowsCounter, 3), Cells(numRows, numCols)) 'This line succeeds
End Function

Edit: The error I'm getting is a "1004", Application-defined or object-defined error

Also the `Set GetCopyRange` is actually problematic, see [this](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) why. — BigBen, Mar 07 '22 at 17:55
Thanks @BigBen . I saw that article, but the way I qualified it didn't solve the error. How would I return the range? — asent, Mar 07 '22 at 18:07
First thing to do is to start using some `Workbook` and `Worksheet` variables: `Dim wb As Workbook`, `Set wb = Workbooks(wbName)`, `Dim ws As Worksheet`, `Set ws = wb.Worksheets(wsIndex)`, `With ws`, `Set GetCopyRange = .Range(.Cells(rowCounter, 3), .Cells(numRows, numCols))`, `End With`. Note the periods `.`, they are essential. — BigBen, Mar 07 '22 at 18:09
Okay, thanks. Just to clarify, are the periods `.` in `.Cells` specific to the `With` statement? I'd tried the `.` without `With` and it failed, saying there wasn't an object to reference — asent, Mar 07 '22 at 18:12
That's correct, the `.` mean that `Cells` and `Range` are qualified by `With ws`. — BigBen, Mar 07 '22 at 18:13
The reason your first line fails is when you pass a single parameter to Range, it is expected to be a string that can be interpreted as an address. When you pass a Cell as the single parameter, it's the Cell.Value that is passed to Range. So for a single cell use `Workbooks(wbName).Worksheets(wsIndex).Cells(rowsCounter, 2)` as Ben said — chris neilsen, Mar 07 '22 at 18:15

FunThomas · Accepted Answer · 2022-03-07T18:23:06.450

The Range-function can be called in 2 variants:

(1) With one parameter: In that case the parameter is an address, eg
Range("A1")
Range("B10:D20")
Range("A1:F" & lastrow)
It may also be a named range, eg Range("MyDataRange").

(2) With two parameters: that specify the first (top left) and the last (bottom right) cell of a Range Range(Range("B10"), Range("D20"))
Range(Cells(10, 2), Cells(20, 4))

Cells always get 2 parameter (row and column) and return a Range that contains one single cell. In VBA, you often use Cells because you have numbers as row and column index.

Unrelated but also important: When you write

Set GetCopyRange = Workbooks(wbName).Worksheets(wsIndex).Range(Cells(rowsCounter, 3), Cells(numRows, numCols))

it will fail when the Workbook is not the active workbook. This is explained in the link BigBen provided in the comments, because Cells is used unqualified (you don't tell VBA which worksheet you want to use).

Usually, it's a good idea to use Workbook and Worksheet variables and/or a With-statement. Makes the code easier to read and easier to maintain. Note the leading dots before Range and Cells - they tell VBA that you are referring to the object of the With-statement.

Dim wsIndex As Worksheet
Set wsIndex = Workbooks(wbName).Worksheets(wsIndex)
With wsIndex 
    Set GetCopyRange = .Range(.Cells(rowsCounter, 3), .Cells(numRows, numCols))
End With

score 0 · Answer 2 · answered Mar 07 '22 at 19:17

Set numRange = Workbooks(wbName).Worksheets(wsIndex).Range(Cells(rowsCounter, 2)) 'This line fails

Workbooks(wbName).Worksheets(wsIndex).Range(

is all well and good. It specifies a range object of the specified worksheet.

Cells(rowsCounter,2)

however, is referring to the cells property of the active worksheet not that of wsIndex.

Like BigBen stated, it is best to specify the range object utilizing the cells property of the worksheet.

Set numRange = Workbooks(wbName).Worksheets(wsIndex).Cells(rowsCounter,2)

I also find it useful to specify the argument types when defining functions as well as the expected return type.

Function GetCopyRange(wbName as String, wsIndex as Integer, vnIndex as Integer) As Range

Why does Range.Cell fail at the first declaration, but not the second?

2 Answers2