'Method 'Range' of object '_Worksheet' failed

Question

i have never had an issue with this until now. Please take a look at the code below, it is on a Module in the workbook (not code on the worksheet), the code breaks when it tries to set a range.

Private Sub UpdateTickerList()
    Dim MyWS As Worksheet
    Dim a, b, c As Integer
    Dim NewStockRng As Range
    Dim RealTickFeed As Range
    'On Error Resume Next (i took this out to get the error)
    'initializes variables
    a = 0
    b = 0


    'defines the worksheet we are going to work on
    Set MyWS = Workbooks("Portfolio.xlsm").Worksheets("Feed")
    'finds last row
    b = MyWS.Range("a10000").End(xlUp).Row + 1
    'finds how large is the new stock universe
    a = 8 'UBound(NPSeCompran)

    'defines a new range in which to copy the new symbols
    Set NewStockRng = MyWS.Range(Cells(b, 1), Cells(b + a - 1, 1)) 'i need to 
    'use b+a-1 to reflect the fact that if i
    'have row97 as my first row and 11 elements then i need to count row 97 
    'as#1, otherwise i end up with one more row
    'copies the stocks to the range
    NewStockRng.value = Application.Transpose(NPSeCompran)

    'now sort the list
    Set RealTickFeed = MyWS.Range("a3").CurrentRegion
    RealTickFeed.Sort key1:=MyWS.Range("a3"), Header:=xlYes

    'now get rid of duplicates
    RealTickFeed.RemoveDuplicates Columns:=Array(1, 9), Header:=xlYes

    ErrorHandler:
End Sub

btw, I put a=8 for now in reality the rest of the line is good — Frank, Nov 03 '17 at 14:52
`Set NewStockRng = MyWS.Range(MyWS.Cells(b, 1), MyWS.Cells(b + a - 1, 1))` ***ALL*** range objects need to specify the parent, or the inner range objects may be referring to a different sheet, thus the error. — Scott Craner, Nov 03 '17 at 14:52

Gary's Student · Accepted Answer · 2017-11-03T15:18:36.177

3

I would put dots on the Cells():

Set NewStockRng = Range(MyWS.Cells(b, 1),MyWS.Cells(b + a - 1, 1))

(there may be other problems)

EDIT#1:

Here is a simple example:

Sub qwerty()
    a = 12
    b = 15
    Sheets("Sheet2").Activate
    Set MyWS = Worksheets("Sheet1")

    Set NewStockRng = Range(MyWS.Cells(b, 1), MyWS.Cells(b + a - 1, 1))

    MsgBox NewStockRng.Address(o, o) & vbCrLf & NewStockRng.Parent.Name
End Sub

EDIT#2:

The key issue is that Cells() is already tiny range itself. Thus:

Range(whatever.Cells(1,1), whatever.Cells(3,3))

is fully qualify.

edited Nov 03 '17 at 15:18

answered Nov 03 '17 at 14:55

Gary's Student

95,722
10
59
99

don't you want `MyWS.` in front of `Range(...` also? – Scott Craner Nov 03 '17 at 15:00
@ScottCraner You don't need to qualify `Range()` if you have already qualified `Cells()` within. – Gary's Student Nov 03 '17 at 15:04
Would that not give the same error, that Range would be looking at the activesheet while the cells are looking at another? – Scott Craner Nov 03 '17 at 15:06
@ScottCraner See my **EDIT#1** for a specific example. – Gary's Student Nov 03 '17 at 15:12
Yeah, all my life I have been living a lie.......Interesting, another assumption down the drains. – Scott Craner Nov 03 '17 at 15:14
The joys of VBA. I remember spending an hour trying to work out what was wrong with my code before I learnt that exact lesson. – Tigregalis Nov 03 '17 at 15:30
Now, I strictly only ever use the unprefixed `Range` method for the purpose of getting the range between two `ws.Range` ranges. I never use the unprefixed, and only use the prefixed, `ws.Cells`, `ws.Columns`, `ws.Rows` or `ws.Range` methods. – Tigregalis Nov 03 '17 at 15:43
1

related: [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) – Nov 03 '17 at 15:44

'Method 'Range' of object '_Worksheet' failed

1 Answers1