Solved: Named cell reference

[frubeng]

Hello,

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

[VBA]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[/VBA]



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]

Not without seeing the workbook, no.

[mdmackillop]

You're missing .xls in your workbook name.
You coud simplify with
[VBA]
Dim tmp as Variant
tmp = Workbooks("Outlier.xls").Sheets("Spreadsheet").Range("vo_a").Value
If IsNumeric(tmp) Then vo_a = tmp

[/VBA]

[frubeng]

I actually noticed, through messing with it a bit that the error is coming from somewhere else, since the line
[VBA]Call check(Workbooks("Outlier").Sheets("Spreadsheet").range("A30:A600"), 5)[/VBA]

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

[frubeng]

You're missing .xls in your workbook name.
You coud simplify with
[vba]
Dim tmp as Variant
tmp = Workbooks("Outlier.xls").Sheets("Spreadsheet").Range("vo_a").Value
If IsNumeric(tmp) Then vo_a = tmp

[/vba]

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

[mdmackillop]

It would work with "Book1" as an unsaved workbook.

[mikerickson]

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.
[VBA]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 [/VBA]