PDA

View Full Version : Solved: Named cell reference



frubeng
05-29-2009, 05:22 AM
Hello,

I have named cells on my spreadsheet. These are named: vo_a, vo_b
In my code, I have the following lines

If IsNumeric(Workbooks("Outlier").Sheets("Spreadsheet").range("vo_a")) Then
vo_a = Workbooks("Outlier").Sheets("Spreadsheet").range("vo_a").Value
End If
If IsNumeric(Workbooks("Outlier").Sheets("Spreadsheet").range("vo_b")) Then
vo_b = Workbooks("Outlier").Sheets("Spreadsheet").range("vo_b").Value
End If



These both give me: "Subscript out of range errors". The workbook and sheet names are definitely right.I have other named cells creating the same error.
What really puzzles me is that is was working fine 2 weeks ago when i last opened the workbook.
Any ideas? Thanks!!

Bob Phillips
05-29-2009, 05:30 AM
Not without seeing the workbook, no.

mdmackillop
05-29-2009, 05:35 AM
You're missing .xls in your workbook name.
You coud simplify with

Dim tmp as Variant
tmp = Workbooks("Outlier.xls").Sheets("Spreadsheet").Range("vo_a").Value
If IsNumeric(tmp) Then vo_a = tmp

frubeng
05-29-2009, 05:44 AM
I actually noticed, through messing with it a bit that the error is coming from somewhere else, since the line
Call check(Workbooks("Outlier").Sheets("Spreadsheet").range("A30:A600"), 5)

Gives the same error.
Is it possible that it is not finding my workbook because of some directory location issue?

frubeng
05-29-2009, 05:46 AM
You're missing .xls in your workbook name.
You coud simplify with

Dim tmp as Variant
tmp = Workbooks("Outlier.xls").Sheets("Spreadsheet").Range("vo_a").Value
If IsNumeric(tmp) Then vo_a = tmp



Yes, putting ".xlsm" solves the error! Thanks!!
Puzzling though why it worked beforehand...

mdmackillop
05-29-2009, 05:48 AM
It would work with "Book1" as an unsaved workbook.

mikerickson
05-29-2009, 06:18 AM
Named Ranges do not need to be fully qualified. In my testing, doing so will cause an error unless the parent sheet of the named range is active.
If IsNumeric(Range("vo_a")) Then
vo_a = Range("vo_a").Value
End If
If IsNumeric(Range("vo_b")) Then
vo_b = Range("vo_b").Value
End If