Method Range of Object Worksheet Failed error

[SBF12345]

I am receiving the method range of object worksheet failed error on the line below:

G() = PBK.Range(Cells(2, 1), Cells(N, 1))

G is a variant, PBK is "Set PBK = Workbooks("A_LA_CARTE.xls").Sheets("Sheet1")", and N is an integer variable

I am not sure why the range method used to select the range of cells is in conflict with the worksheet. Any ideas why there could be conflict here?

 

[Fluff]

You are receiving the error because that line of code cannot work.
It would help if you supplied the entire code, along with an explanation of what you are trying to do.

 

[SBF12345]

The purpose of the line is to create an array defined by a range of cells in a workbook worksheet (PBK).

Below is all the code above the line that is in conflict.

Dim TS As Worksheet:            Set TS = Workbooks("TS.xls").Sheets("Sheet1")Dim PBK As Worksheet:           Set PBK = Workbooks("PBK.xls").Sheets("Sheet1")
Dim TBL As Worksheet:           Set TBL = Workbooks("TBL.xls").Sheets("Sheet1")
Dim A As Integer
Dim B As Integer
Dim C As Variant
Dim D As Variant
Dim E As Double
Dim F As Variant
Dim G() As Variant
Dim H As Integer
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim M As Double
Dim N As Integer
Dim O As Double
Dim P As Double
Dim Q As Integer
Dim R As Integer
Dim S As Integer
Dim T As Integer
Dim U As Integer
Dim V As Integer
Dim W As Variant
Dim X As Double
Dim Y As Integer
Dim Z As Double
Dim Arr() As Variant
Dim Arr1() As Variant
Dim Arr2() As Variant
Dim aa As Integer


B = TS.Cells(Rows.Count, "A").End(xlUp).Row


For A = B To 2 Step -1


    C = TS.Cells(A, 1).value
    D = TS.Cells(A, 2).value 
    E = TS.Cells(A, 3).value 
    
    N = PBK.Cells(Rows.Count, 1).End(xlUp).Row
    
    G() = PBK.Range(Cells(2, 1), Cells(N, 1))

 

[Rick Rothstein]

Dim you Variant variables without the parentheses and then simply assign the range to the variable... a two-dimensional array will automatically be stored in it.

Dim G As Variant
G = PBK.Range(Cells(2, 1), Cells(N, 1))

 

[MARK858]

Rick, I think the Cells should be qualified with the worksheet

Dim G As Variant
G = PBK.Range([COLOR="#FF0000"]PBK.[/COLOR]Cells(2, 1), [COLOR="#FF0000"]PBK.[/COLOR]Cells(N, 1))

 

[Rick Rothstein]

Rick, I think the Cells should be qualified with the worksheet

Dim G As Variant
G = PBK.Range([COLOR="#FF0000"]PBK.[/COLOR]Cells(2, 1), [COLOR="#FF0000"]PBK.[/COLOR]Cells(N, 1))

Duh, of course (that's what I get for testing on the active sheet)!!!!

 

[SBF12345]

Nice, it seems to be running.

G = PBK.Range(PBK.Cells(2, 1), PBK.Cells(N, 1))