Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
Hi
XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
This works
With Range("CompNames") Set c = .Find(Range("A1")) End With -- Jacob "Dave" wrote: Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
Hi Jacob,
When I use: With Range("CompNames"), I get: Runtime error '1004': Method 'Range' of object '_Worksheet' failed Regards - Dave. "Jacob Skaria" wrote: This works With Range("CompNames") Set c = .Find(Range("A1")) End With -- Jacob "Dave" wrote: Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the workbookname if the named range is present in the active workbook. Activeworkbook.Names("Compnames").Parent.name -- Jacob "Dave" wrote: Hi Jacob, When I use: With Range("CompNames"), I get: Runtime error '1004': Method 'Range' of object '_Worksheet' failed Regards - Dave. "Jacob Skaria" wrote: This works With Range("CompNames") Set c = .Find(Range("A1")) End With -- Jacob "Dave" wrote: Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
Hi Jacob,
Your line of code runs ok, returning the name of the active workbook. I've checked the spelling, even pasted it straight from the name box into the code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any variant on that I could think of. Dave. "Jacob Skaria" wrote: Check out the exact name of your named range...It seems it is not present in your active workbook.. Try the below code which should return the workbookname if the named range is present in the active workbook. Activeworkbook.Names("Compnames").Parent.name -- Jacob "Dave" wrote: Hi Jacob, When I use: With Range("CompNames"), I get: Runtime error '1004': Method 'Range' of object '_Worksheet' failed Regards - Dave. "Jacob Skaria" wrote: This works With Range("CompNames") Set c = .Find(Range("A1")) End With -- Jacob "Dave" wrote: Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
Try to add worksheet object qualifier with sheet name hosting the named range
like this: Worksheets("hostsheet_name").Range("CompNames") I don't know why, but I had cases when this workaround worked. -- Regards! Stefi Dave ezt *rta: Hi Jacob, Your line of code runs ok, returning the name of the active workbook. I've checked the spelling, even pasted it straight from the name box into the code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any variant on that I could think of. Dave. "Jacob Skaria" wrote: Check out the exact name of your named range...It seems it is not present in your active workbook.. Try the below code which should return the workbookname if the named range is present in the active workbook. Activeworkbook.Names("Compnames").Parent.name -- Jacob "Dave" wrote: Hi Jacob, When I use: With Range("CompNames"), I get: Runtime error '1004': Method 'Range' of object '_Worksheet' failed Regards - Dave. "Jacob Skaria" wrote: This works With Range("CompNames") Set c = .Find(Range("A1")) End With -- Jacob "Dave" wrote: Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
Just to add to Stefi's advice:
Since you're in the worksheet module (using the worksheet_activate event), an unqualified range like: With Range("CompNames") will refer to the range named CompNames on the worksheet that owns the code. And your compnames range is on a different sheet. with worksheets("someothersheetnamehere".range("compnam es") or even specify the workbook: with thisworkbook.worksheets("someothersheetnamehere".r ange("compnames") You could go through the workbook's name collection, too: with thisworkbook.names("CompNames").referstorange ==================== Ps. I would qualify all the ranges in the code--and I wouldn't use the Activesheet: Private Sub Worksheet_Activate() with thisworkbook.worksheets("Names".range("compnames") 'Me refers to the sheet that owns the code Set c = .Find(Me.Range("A1")) 'I like the block if/end if If Not c Is Nothing Then A = thisworkbook.Sheets("Names").Cells(c.Row, 3) end if End With If me.Name < A Then me.Name = A End Sub Stefi wrote: Try to add worksheet object qualifier with sheet name hosting the named range like this: Worksheets("hostsheet_name").Range("CompNames") I don't know why, but I had cases when this workaround worked. -- Regards! Stefi Dave ezt *rta: Hi Jacob, Your line of code runs ok, returning the name of the active workbook. I've checked the spelling, even pasted it straight from the name box into the code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any variant on that I could think of. Dave. "Jacob Skaria" wrote: Check out the exact name of your named range...It seems it is not present in your active workbook.. Try the below code which should return the workbookname if the named range is present in the active workbook. Activeworkbook.Names("Compnames").Parent.name -- Jacob "Dave" wrote: Hi Jacob, When I use: With Range("CompNames"), I get: Runtime error '1004': Method 'Range' of object '_Worksheet' failed Regards - Dave. "Jacob Skaria" wrote: This works With Range("CompNames") Set c = .Find(Range("A1")) End With -- Jacob "Dave" wrote: Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
Thanks Dave for the explanation, I didn't realized that being in a worksheet
module may cause this problem, now I know what should I focus on when encountering this problem again. -- Regards! Stefi Dave Peterson ezt *rta: Just to add to Stefi's advice: Since you're in the worksheet module (using the worksheet_activate event), an unqualified range like: With Range("CompNames") will refer to the range named CompNames on the worksheet that owns the code. And your compnames range is on a different sheet. with worksheets("someothersheetnamehere".range("compnam es") or even specify the workbook: with thisworkbook.worksheets("someothersheetnamehere".r ange("compnames") You could go through the workbook's name collection, too: with thisworkbook.names("CompNames").referstorange ==================== Ps. I would qualify all the ranges in the code--and I wouldn't use the Activesheet: Private Sub Worksheet_Activate() with thisworkbook.worksheets("Names".range("compnames") 'Me refers to the sheet that owns the code Set c = .Find(Me.Range("A1")) 'I like the block if/end if If Not c Is Nothing Then A = thisworkbook.Sheets("Names").Cells(c.Row, 3) end if End With If me.Name < A Then me.Name = A End Sub Stefi wrote: Try to add worksheet object qualifier with sheet name hosting the named range like this: Worksheets("hostsheet_name").Range("CompNames") I don't know why, but I had cases when this workaround worked. -- Regards! Stefi âžDaveâ ezt Ã*rta: Hi Jacob, Your line of code runs ok, returning the name of the active workbook. I've checked the spelling, even pasted it straight from the name box into the code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any variant on that I could think of. Dave. "Jacob Skaria" wrote: Check out the exact name of your named range...It seems it is not present in your active workbook.. Try the below code which should return the workbookname if the named range is present in the active workbook. Activeworkbook.Names("Compnames").Parent.name -- Jacob "Dave" wrote: Hi Jacob, When I use: With Range("CompNames"), I get: Runtime error '1004': Method 'Range' of object '_Worksheet' failed Regards - Dave. "Jacob Skaria" wrote: This works With Range("CompNames") Set c = .Find(Range("A1")) End With -- Jacob "Dave" wrote: Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. -- Dave Peterson . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
It never hurts to qualify ranges/objects--even if you're in a General module and
refering to the activesheet. I like to use: dim wks as worksheet set wks = activesheet with wks .range("A1").value = "..." by using a variable that's declared nicely, I get all that help from the VBE's intellisense feature. Saves a lot of (my!) typing errors. Stefi wrote: Thanks Dave for the explanation, I didn't realized that being in a worksheet module may cause this problem, now I know what should I focus on when encountering this problem again. -- Regards! Stefi Dave Peterson ezt *rta: Just to add to Stefi's advice: Since you're in the worksheet module (using the worksheet_activate event), an unqualified range like: With Range("CompNames") will refer to the range named CompNames on the worksheet that owns the code. And your compnames range is on a different sheet. with worksheets("someothersheetnamehere".range("compnam es") or even specify the workbook: with thisworkbook.worksheets("someothersheetnamehere".r ange("compnames") You could go through the workbook's name collection, too: with thisworkbook.names("CompNames").referstorange ==================== Ps. I would qualify all the ranges in the code--and I wouldn't use the Activesheet: Private Sub Worksheet_Activate() with thisworkbook.worksheets("Names".range("compnames") 'Me refers to the sheet that owns the code Set c = .Find(Me.Range("A1")) 'I like the block if/end if If Not c Is Nothing Then A = thisworkbook.Sheets("Names").Cells(c.Row, 3) end if End With If me.Name < A Then me.Name = A End Sub Stefi wrote: Try to add worksheet object qualifier with sheet name hosting the named range like this: Worksheets("hostsheet_name").Range("CompNames") I don't know why, but I had cases when this workaround worked. -- Regards! Stefi âžDaveâ ezt Ã*rta: Hi Jacob, Your line of code runs ok, returning the name of the active workbook. I've checked the spelling, even pasted it straight from the name box into the code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any variant on that I could think of. Dave. "Jacob Skaria" wrote: Check out the exact name of your named range...It seems it is not present in your active workbook.. Try the below code which should return the workbookname if the named range is present in the active workbook. Activeworkbook.Names("Compnames").Parent.name -- Jacob "Dave" wrote: Hi Jacob, When I use: With Range("CompNames"), I get: Runtime error '1004': Method 'Range' of object '_Worksheet' failed Regards - Dave. "Jacob Skaria" wrote: This works With Range("CompNames") Set c = .Find(Range("A1")) End With -- Jacob "Dave" wrote: Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. -- Dave Peterson . -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to a named range
Thanks for the suggestions!
-- Regards! Stefi Dave Peterson ezt *rta: It never hurts to qualify ranges/objects--even if you're in a General module and refering to the activesheet. I like to use: dim wks as worksheet set wks = activesheet with wks .range("A1").value = "..." by using a variable that's declared nicely, I get all that help from the VBE's intellisense feature. Saves a lot of (my!) typing errors. Stefi wrote: Thanks Dave for the explanation, I didn't realized that being in a worksheet module may cause this problem, now I know what should I focus on when encountering this problem again. -- Regards! Stefi âžDave Petersonâ ezt Ã*rta: Just to add to Stefi's advice: Since you're in the worksheet module (using the worksheet_activate event), an unqualified range like: With Range("CompNames") will refer to the range named CompNames on the worksheet that owns the code. And your compnames range is on a different sheet. with worksheets("someothersheetnamehere".range("compnam es") or even specify the workbook: with thisworkbook.worksheets("someothersheetnamehere".r ange("compnames") You could go through the workbook's name collection, too: with thisworkbook.names("CompNames").referstorange ==================== Ps. I would qualify all the ranges in the code--and I wouldn't use the Activesheet: Private Sub Worksheet_Activate() with thisworkbook.worksheets("Names".range("compnames") 'Me refers to the sheet that owns the code Set c = .Find(Me.Range("A1")) 'I like the block if/end if If Not c Is Nothing Then A = thisworkbook.Sheets("Names").Cells(c.Row, 3) end if End With If me.Name < A Then me.Name = A End Sub Stefi wrote: Try to add worksheet object qualifier with sheet name hosting the named range like this: Worksheets("hostsheet_name").Range("CompNames") I don't know why, but I had cases when this workaround worked. -- Regards! Stefi ââ¬Å¾Daveââ¬Â ezt ÃÂ*rta: Hi Jacob, Your line of code runs ok, returning the name of the active workbook. I've checked the spelling, even pasted it straight from the name box into the code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any variant on that I could think of. Dave. "Jacob Skaria" wrote: Check out the exact name of your named range...It seems it is not present in your active workbook.. Try the below code which should return the workbookname if the named range is present in the active workbook. Activeworkbook.Names("Compnames").Parent.name -- Jacob "Dave" wrote: Hi Jacob, When I use: With Range("CompNames"), I get: Runtime error '1004': Method 'Range' of object '_Worksheet' failed Regards - Dave. "Jacob Skaria" wrote: This works With Range("CompNames") Set c = .Find(Range("A1")) End With -- Jacob "Dave" wrote: Hi XL2003 I am having trouble using a named range in a macro, even after refering the Help. The named range lives in the active workbook on another sheet. My code is this: Private Sub Worksheet_Activate() With [CompNames] Set c = .Find(Range("A1")) If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3) End With If ActiveSheet.Name < A Then ActiveSheet.Name = A End Sub You will see that I have had to resort to using [ ] around the named range, which is the only way I could get the code to run. I tried Range("CompNames") and Range(CompNames) and tried including the sheet name and the workbook name, but none of those worked. What am I doing wrong? Regards - Dave. -- Dave Peterson . -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refer to a named range indirectly | Excel Worksheet Functions | |||
refer to a named range in this.workbook | Excel Programming | |||
???Refer to a specifc cell in a named range | Excel Worksheet Functions | |||
How does one refer to the n-1 element of a named range? | Excel Discussion (Misc queries) | |||
How do you refer to a dynamic named range? | Excel Programming |