Method 'Range' of object '_Global' failed error when referencing Named Ranges

0
votes

I got a Method 'Range' of object '_Global' failed error when I'm trying to reference a workbook scope Named Range. I've referenced other Named ranges with the same code, and they all work fine. Only this particular range throws an error.

The function for the named range is this

=OFFSET(INDIRECT(ADDRESS(91,MATCH(Calculation!$G$89,Calculation!$C$90:$S$90,0),,,"Calculation")),,-2,1,5)

I couldn't figure out what's the issue with this, could anyone help me out please? Thanks!

This is the line that throws the error

Set rngVs = Range("'CAR Dashboard.xlsm'!PenLineSelected1Y")
1
excelvba
I suspect that formula is not returning what you expect it to return. If you put that offset formula into a worksheet, it probably will return an error. – David Zemens

1 Answers

0
votes

You may want to try:

Set rngVs = Names("PenLineSelected").RefersToRange

If that still fails, then one likely cause of error:

You have -2 for the cols argument in your Offset function. If the Address function returns an address in columns A or B, this will raise a #REF! error.

I put each part of the formula in its own cell just to see where it is failing. Observe that a match in the 3rd column of C90:S90 will not cause an error:

enter image description here

But if I change the search term to "World" (which will be found in column B), the Offset function will fail.

enter image description here