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

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

CodePudding user response：

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

CodePudding user response：

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