Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem referring to a particular sheet in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem referring to a particular sheet in VBA
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("'" & .... tbone wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem referring to a particular sheet in VBA
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 -- Steve "tbone" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem referring to a particular sheet in VBA
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 wrote: 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 -- Steve "tbone" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem referring to a particular sheet in VBA
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. -- Steve "Dave Peterson" wrote in message ... 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 wrote: 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 -- Steve "tbone" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem referring to a particular sheet in VBA
On Mon, 18 May 2009 20:28:34 -0500, Dave Peterson
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to the only sheet | Excel Programming | |||
Formula referring to a different sheet | Excel Discussion (Misc queries) | |||
Referring to a sheet | Excel Programming | |||
referring to previous sheet | Excel Discussion (Misc queries) | |||
problem referring to Range and Cells | Excel Programming |