Method Range of Object Worksheet Failed error

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
I am receiving the method range of object worksheet failed error on the line below:

Code:
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
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.

Code:
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))
 
Upvote 0
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))
 
Upvote 0
Rick, I think the Cells should be qualified with the worksheet :biggrin:
Code:
Dim G As Variant
G = PBK.Range([COLOR="#FF0000"]PBK.[/COLOR]Cells(2, 1), [COLOR="#FF0000"]PBK.[/COLOR]Cells(N, 1))
 
Last edited:
Upvote 0
Rick, I think the Cells should be qualified with the worksheet :biggrin:
Code:
Dim G As Variant
G = PBK.Range([COLOR="#FF0000"]PBK.[/COLOR]Cells(2, 1), [COLOR="#FF0000"]PBK.[/COLOR]Cells(N, 1))
:banghead: Duh, of course (that's what I get for testing on the active sheet)!!!!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top