Problem using a named range

[Guest]

Using Excel 2003, I have a workbook with a single sheet. A single cell on
that sheet is a named range with name "bbb".

If I try to run:

Sub jnk()
Dim i As Long

i = Range(Names("bbb"))
End Sub

I get an "application defined or oblect drfined error", yet if I type
i = Range(Names("bbb"))
?i
in the immediate window, I get no error and the correct value of i is
displayed.

What is wrong with my Sub?

Thanksfor your help.

 

[GS]

(e-mail address removed) formulated on Friday :

Using Excel 2003, I have a workbook with a single sheet. A single cell on
that sheet is a named range with name "bbb".

If I try to run:

Sub jnk()
Dim i As Long

i = Range(Names("bbb"))
End Sub

I get an "application defined or oblect drfined error", yet if I type
i = Range(Names("bbb"))
?i
in the immediate window, I get no error and the correct value of i is
displayed.

What is wrong with my Sub?

Thanksfor your help.

Just use Range("bbb")

 

[Dave Peterson]

As long as BBB referred to a single cell, I'd use:

Option Explicit
Sub Junk()
dim i as long
i = activesheet.range("bbb").value
end sub

This assumes that the value is numeric, too!

 

[Guest]

(e-mail address removed) formulated on Friday :

Just use Range("bbb")

Thanks for your reply. I tried that and got the same result. It works in
the immediate window but not in the sub.

 

[GS]

Just use Range("bbb")

Thanks for your reply. I tried that and got the same result. It works in
the immediate window but not in the sub.

I tried this and it worked for me!

Sub Junk()
Dim i As Long
i = Range("bbb")
Debug.Print i
End Sub

 

[Guest]

Using Excel 2003, I have a workbook with a single sheet. A single cell on
that sheet is a named range with name "bbb".

-- Snip --

I started noticing other odd things happening with cells in that column.
The value in the cell was an integer and the column format was "General" so
that wasn't the problem.

Cells in other columns were OK and the code that I said was not working did
work for cells in other columns. I have no idea what was going on.

The fix: Delete the offending column and insert a new one and paste the
values into it.

Thanks for your replies.