VBA disliking named range with multiple cells

[paleolith]

What I'm trying to do is pretty simple. I made it even simpler in trying to figure out what's going on. And it seems that the VBA code I'm writing works fine when a named range has a single cell, but not when it has multiple cells. And I don't know why.

(As background, I've been programming for almost 45 years, but have only meager experience with VBA.)

The Define Name dialog box shows

mycell =Sheet1!$C$8
mycells =Sheet1!$E$7:$E$9

and my VBA function is

Option Explicit

Function whatever()
Dim rnum As Integer
Dim nm

For Each nm In Names
MsgBox nm.Name + " " + nm.RefersTo
Next nm

rnum = [mycell].Offset(0, 0).Value
MsgBox rnum

rnum = [mycells].Offset(0, 0).Value
MsgBox rnum

End Function

I put =whatever() in some cell. The MsgBox displays the two names as expected. The MsgBox to display the value in [mycell] works as expected. Then the next MsgBox never comes up and the cell shows #VALUE!.

(The final code is intended to have something other than 0,0 for the offset.)

Can anyone give me a clue?

Edward

 

[Michael M]

Try

Set mycell = Sheet1!.Range("[$C$8")
Set mycells = Sheet1!.Range("$E$7:$E$9")

 

[paleolith]

Thanks. Still fails (even after correcting the sin tax arrows ). And in any case, that would embed the range definitions in the VBA, which I was trying to avoid. And would not explain what's different about a multi-cell range compared with a single-cell range, which I was hoping to learn for better understanding of what I'm doing ... whatever that is.

Edward

 

[AlphaFrog]

rnum is dimmed as an integer

If MyCell value is an integer, then that would work fine.

MyCells would be three values and that would cause a Type mismatch error with rnum. Three values (even if each are integers) can't be in one integer variable.

Maybe try something like this...

Dim rnum As String, c As Range
Dim nm

For Each nm In Names
MsgBox nm.Name + " " + nm.RefersTo
Next nm

rnum = [mycell].Offset(0, 0).Value
MsgBox rnum

rnum = ""
For Each c In [mycells].Offset(0, 0)
    rnum = rnum & c.Value & " "
Next c
MsgBox Trim(rnum)

 

[paleolith]

Ah, that's it! I was assuming that Offset would return a single cell. I have changed it to use Cells instead, since my intent was to select one cell.

Thank you!

Edward