Problem referring to a particular sheet in VBA

[tbone]

I need to reference cells in VBA using sheet-level names, which I have
set up using Name Manager. But I ran into some troubles, so I tried to
boil it down to figure it out. I have arrived at the situation below,
and I am baffled as to why this doesn't work:

For i = 1 To Sheets.Count
Debug.Print "Sheet " & i & " is named """ & Sheets(i).Name &
""""
Debug.Print Range("'" & Sheets(i).Name & "'!A1").Address
Next i

I get:
Sheet 1 is named "Current"
$A$1
Sheet 2 is named "Past"
**error here**

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

I tried it with and without the apostrophes to delimit the sheet name.
I even copied the Current sheet into a new workbook in case there was
some hidden corruption. No luck.

Any ideas? What am I missing?

Thanks
tbone

 

[Dave Peterson]

I'd use:

Debug.print sheets(i).range("A1").address(external:=true)

This will include the workbook, worksheet and address.

===
If your code is in a general module, then I would think it would work -- if all
the sheets are worksheets.

But if your code is behind a worksheet, you could also try:

debug.print application.range("'" & ....

 

[AltaEgo]

To get the name you need to use '.Name' rather than '.Address'. I get no
error using your code but get the same error when using '.Name' when no name
exists in the relevant sheet-range.


For i = 1 To Sheets.Count
Debug.Print "Sheet " & i & " is named """ & Sheets(i).Name & """"
Debug.Print Range("'" & Sheets(i).Name & "'!A1").Name
Next i


The following will debug.print all names:

Sub WBNames()
For Each n In ActiveWorkbook.Names
Debug.Print n
Next n
End Sub


HTH

 

[Dave Peterson]

Actually, I think you'd need:

Debug.Print Range("'" & Sheets(i).Name & "'!A1").Name.Name

And that will fail if A1 isn't named.

You could ignore the error:

on error resume next
Debug.Print Range("'" & Sheets(i).Name & "'!A1").Name.Name
on error goto 0

or use a variable:

dim myName as string
myName = ""
on error resume next
myname = Range("'" & Sheets(i).Name & "'!A1").Name.Name
on error goto 0

if myName = "" then
debug.print "no name here!"
else
debug.print myname
end if

 

[AltaEgo]

Oops! Forgot a .Name. Thank you Dave and sorry tbone.
Without the additional .Name, it produces an address (but crashes unless a
name exists).
With the additional .Name it produces a name or crashes if the name does not
exist.

 

[tbone]

If your code is in a general module, then I would think it would work -- if all
the sheets are worksheets.

But if your code is behind a worksheet, you could also try:

debug.print application.range("'" & ....

Due to laziness I guess, my code was in one of the worksheets. When I
tested out everyone's suggestions, I happened to put the code in a
handy module and it all worked.

Your suggestion of "Application.Range" also worked fine in the
spreadsheet code.

I think my lesson here is to be more diligent with putting code in the
proper place. I figure this kind of general code should be in a
module.

Thanks for all the help
tbone