When I assign a range to an array without qualifying the range the result is OK.

However if I try to qualify (reference) the range with the workbook/worksheet I get “Run-time error ’13’: Type mismatch”.

But data types are the same in both cases, the do not change. Can anyone explain me why it works in one case but fails in the other one?

My test code is here below to help understanding.

010:    Public Sub test()
020:        Dim i As Long, stRow As Long, length As Long
030:        Dim arr()
040:       
050:        stRow = 2
060:        length = 256
070:    
080:        ThisWorkbook.Worksheets(2).Activate
090:        arr = Range("b2:b257")                                        'it works
100:        arr = Range(Cells(stRow, 2), Cells(stRow + length - 1, 2))    'it works
110:    
120:        With ThisWorkbook.Worksheets(2)
130:            i = .Range("b2:b257").Count
140:            i = .Range(.Cells(stRow, 2), .Cells(stRow + length - 1, 2)).Count
150:            arr = .Range("b2:b257")                                       'it fails
160:            arr = .Range(.Cells(stRow, 2), .Cells(stRow + length - 1, 2)) 'it fails
170:        End With
180:    End Sub

Hints:

1. Rows 80 to 100 are to test the range is correctly assigned to the variable arr
2. Rows 130 and 140 are just to check the syntax of the range is correct
3. Rows 150 and 160 give error