Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to make the name Property of the Range Object Local
Greetings! On page 128 of "Excel 2007 VBA Programmer's Reference" it says
with respect to creating a name which refers to a range: "If you want the name to be local, you can include a worksheet name: Range("F1:F10").Name = "Sheet1!Staff" " In my workbook, BookIssuers.xlsm I have a number of worksheets. The Active Worksheet is "24Iteration" but it could be any of the worksheets in the workbook. I tried to make a range on the Active Worksheet local as follows: First I declared SheetName As String: Dim SheetName As String Then, I retrieved the Sheet Name and displayed it as follows: SheetName = ActiveSheet.Name MsgBox SheetName I also tried: MsgBox ActiveSheet.Name Both of these MsgBoxes indeed displayed "20Iteration" as expected. I then tried to name the local range IssuerLabels as follows: Range("C3:C5").Name = "SheetName!IssuerLabels" When I run the above instruction, I get: "Run-time error '1004': Application-defined or object-defined error" I then tried running: Range("C3:C5").Name = "ActiveSheet.Name!IssuerLabels" and got the same error message. I then substituted in the actual name of the sheet and ran the instruction: Range("C3:C5").Name = "24Iteration!IssuerLabels" which runs without error. Any help or suggestions will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to make the name Property of the Range Object Local
Hi Michael,
The reason is that where you use VBA variables in place of real strings. You do not include the variable between the quotes but concatenate it with the part between quotes using the ambersand. Range("C3:C5").Name = Sheetname & "!IssuerLabels" Note that ActiveSheet.Name is really a variable. Range("C3:C5").Name = ActiveSheet.Name & "!IssuerLabels" The above can be confusing where you are actually using the defined names in a range in lieu of the actual address. Named ranges are not VBA variables but belong to the worksheet and are saved with the worksheet and they are included between the quotes just the same as the cell address as per the following example. Range("C3:C5") becomes Range("IssuerLabels") -- Regards, OssieMac "MichaelDavid" wrote: Greetings! On page 128 of "Excel 2007 VBA Programmer's Reference" it says with respect to creating a name which refers to a range: "If you want the name to be local, you can include a worksheet name: Range("F1:F10").Name = "Sheet1!Staff" " In my workbook, BookIssuers.xlsm I have a number of worksheets. The Active Worksheet is "24Iteration" but it could be any of the worksheets in the workbook. I tried to make a range on the Active Worksheet local as follows: First I declared SheetName As String: Dim SheetName As String Then, I retrieved the Sheet Name and displayed it as follows: SheetName = ActiveSheet.Name MsgBox SheetName I also tried: MsgBox ActiveSheet.Name Both of these MsgBoxes indeed displayed "20Iteration" as expected. I then tried to name the local range IssuerLabels as follows: Range("C3:C5").Name = "SheetName!IssuerLabels" When I run the above instruction, I get: "Run-time error '1004': Application-defined or object-defined error" I then tried running: Range("C3:C5").Name = "ActiveSheet.Name!IssuerLabels" and got the same error message. I then substituted in the actual name of the sheet and ran the instruction: Range("C3:C5").Name = "24Iteration!IssuerLabels" which runs without error. Any help or suggestions will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to make the name Property of the Range Object Local
Hi OssieMac! That solved ALL my problems! You really aced that one. I
THOROUGHLY checked out your solution, and my macros ran perfectly. If I were your boss, I'd give you a HUGH raise. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "OssieMac" wrote: Hi Michael, The reason is that where you use VBA variables in place of real strings. You do not include the variable between the quotes but concatenate it with the part between quotes using the ambersand. Range("C3:C5").Name = Sheetname & "!IssuerLabels" Note that ActiveSheet.Name is really a variable. Range("C3:C5").Name = ActiveSheet.Name & "!IssuerLabels" The above can be confusing where you are actually using the defined names in a range in lieu of the actual address. Named ranges are not VBA variables but belong to the worksheet and are saved with the worksheet and they are included between the quotes just the same as the cell address as per the following example. Range("C3:C5") becomes Range("IssuerLabels") -- Regards, OssieMac "MichaelDavid" wrote: Greetings! On page 128 of "Excel 2007 VBA Programmer's Reference" it says with respect to creating a name which refers to a range: "If you want the name to be local, you can include a worksheet name: Range("F1:F10").Name = "Sheet1!Staff" " In my workbook, BookIssuers.xlsm I have a number of worksheets. The Active Worksheet is "24Iteration" but it could be any of the worksheets in the workbook. I tried to make a range on the Active Worksheet local as follows: First I declared SheetName As String: Dim SheetName As String Then, I retrieved the Sheet Name and displayed it as follows: SheetName = ActiveSheet.Name MsgBox SheetName I also tried: MsgBox ActiveSheet.Name Both of these MsgBoxes indeed displayed "20Iteration" as expected. I then tried to name the local range IssuerLabels as follows: Range("C3:C5").Name = "SheetName!IssuerLabels" When I run the above instruction, I get: "Run-time error '1004': Application-defined or object-defined error" I then tried running: Range("C3:C5").Name = "ActiveSheet.Name!IssuerLabels" and got the same error message. I then substituted in the actual name of the sheet and ran the instruction: Range("C3:C5").Name = "24Iteration!IssuerLabels" which runs without error. Any help or suggestions will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to make the name Property of the Range Object Local
Greetings! One last problem: The sheet name sometimes has blanks in it. I
tried the following instruction to eliminate the blanks from the sheet name: ActiveSheet.Name = Trim(ActiveSheet.Name) When this instruction is executed, there is no diagnostic, but this instruction does not remove the blanks. Hopefully this problem can be as easily resolved. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "OssieMac" wrote: Hi Michael, The reason is that where you use VBA variables in place of real strings. You do not include the variable between the quotes but concatenate it with the part between quotes using the ambersand. Range("C3:C5").Name = Sheetname & "!IssuerLabels" Note that ActiveSheet.Name is really a variable. Range("C3:C5").Name = ActiveSheet.Name & "!IssuerLabels" The above can be confusing where you are actually using the defined names in a range in lieu of the actual address. Named ranges are not VBA variables but belong to the worksheet and are saved with the worksheet and they are included between the quotes just the same as the cell address as per the following example. Range("C3:C5") becomes Range("IssuerLabels") -- Regards, OssieMac "MichaelDavid" wrote: Greetings! On page 128 of "Excel 2007 VBA Programmer's Reference" it says with respect to creating a name which refers to a range: "If you want the name to be local, you can include a worksheet name: Range("F1:F10").Name = "Sheet1!Staff" " In my workbook, BookIssuers.xlsm I have a number of worksheets. The Active Worksheet is "24Iteration" but it could be any of the worksheets in the workbook. I tried to make a range on the Active Worksheet local as follows: First I declared SheetName As String: Dim SheetName As String Then, I retrieved the Sheet Name and displayed it as follows: SheetName = ActiveSheet.Name MsgBox SheetName I also tried: MsgBox ActiveSheet.Name Both of these MsgBoxes indeed displayed "20Iteration" as expected. I then tried to name the local range IssuerLabels as follows: Range("C3:C5").Name = "SheetName!IssuerLabels" When I run the above instruction, I get: "Run-time error '1004': Application-defined or object-defined error" I then tried running: Range("C3:C5").Name = "ActiveSheet.Name!IssuerLabels" and got the same error message. I then substituted in the actual name of the sheet and ran the instruction: Range("C3:C5").Name = "24Iteration!IssuerLabels" which runs without error. Any help or suggestions will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to make the name Property of the Range Object Local
Greetings! I mis-spoke. The Trim just elimianates leading and trailing
blanks. I should be using the Replace Function to eliminate ALL blanks. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "OssieMac" wrote: Hi Michael, The reason is that where you use VBA variables in place of real strings. You do not include the variable between the quotes but concatenate it with the part between quotes using the ambersand. Range("C3:C5").Name = Sheetname & "!IssuerLabels" Note that ActiveSheet.Name is really a variable. Range("C3:C5").Name = ActiveSheet.Name & "!IssuerLabels" The above can be confusing where you are actually using the defined names in a range in lieu of the actual address. Named ranges are not VBA variables but belong to the worksheet and are saved with the worksheet and they are included between the quotes just the same as the cell address as per the following example. Range("C3:C5") becomes Range("IssuerLabels") -- Regards, OssieMac "MichaelDavid" wrote: Greetings! On page 128 of "Excel 2007 VBA Programmer's Reference" it says with respect to creating a name which refers to a range: "If you want the name to be local, you can include a worksheet name: Range("F1:F10").Name = "Sheet1!Staff" " In my workbook, BookIssuers.xlsm I have a number of worksheets. The Active Worksheet is "24Iteration" but it could be any of the worksheets in the workbook. I tried to make a range on the Active Worksheet local as follows: First I declared SheetName As String: Dim SheetName As String Then, I retrieved the Sheet Name and displayed it as follows: SheetName = ActiveSheet.Name MsgBox SheetName I also tried: MsgBox ActiveSheet.Name Both of these MsgBoxes indeed displayed "20Iteration" as expected. I then tried to name the local range IssuerLabels as follows: Range("C3:C5").Name = "SheetName!IssuerLabels" When I run the above instruction, I get: "Run-time error '1004': Application-defined or object-defined error" I then tried running: Range("C3:C5").Name = "ActiveSheet.Name!IssuerLabels" and got the same error message. I then substituted in the actual name of the sheet and ran the instruction: Range("C3:C5").Name = "24Iteration!IssuerLabels" which runs without error. Any help or suggestions will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to make the name Property of the Range Object Local
Hi again Michael,
I haven't been in all day so just got your message. If the sheet name has a space in the name then you need to concatenate single quotes around the name like the following 2 examples. The following code uses the ascii code of a single quote to concatenate the single quotes around the sheet name. Assume sheet name is something like My Sheet with a space between the words. sheetname = ActiveSheet.Name Range("C3:C5").Name = Chr(39) & sheetname & Chr(39) & "!IssuerLabels" The next line uses the single quote enclosed in double quotes to concatenate the single quotes around the sheet name. Range("C3:C5").Name = "'" & sheetname & "'" & "!IssuerLabels" Both methods are basically the same. It is just a matter of preference which method you use. -- Regards, OssieMac "MichaelDavid" wrote: Greetings! I mis-spoke. The Trim just elimianates leading and trailing blanks. I should be using the Replace Function to eliminate ALL blanks. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "OssieMac" wrote: Hi Michael, The reason is that where you use VBA variables in place of real strings. You do not include the variable between the quotes but concatenate it with the part between quotes using the ambersand. Range("C3:C5").Name = Sheetname & "!IssuerLabels" Note that ActiveSheet.Name is really a variable. Range("C3:C5").Name = ActiveSheet.Name & "!IssuerLabels" The above can be confusing where you are actually using the defined names in a range in lieu of the actual address. Named ranges are not VBA variables but belong to the worksheet and are saved with the worksheet and they are included between the quotes just the same as the cell address as per the following example. Range("C3:C5") becomes Range("IssuerLabels") -- Regards, OssieMac "MichaelDavid" wrote: Greetings! On page 128 of "Excel 2007 VBA Programmer's Reference" it says with respect to creating a name which refers to a range: "If you want the name to be local, you can include a worksheet name: Range("F1:F10").Name = "Sheet1!Staff" " In my workbook, BookIssuers.xlsm I have a number of worksheets. The Active Worksheet is "24Iteration" but it could be any of the worksheets in the workbook. I tried to make a range on the Active Worksheet local as follows: First I declared SheetName As String: Dim SheetName As String Then, I retrieved the Sheet Name and displayed it as follows: SheetName = ActiveSheet.Name MsgBox SheetName I also tried: MsgBox ActiveSheet.Name Both of these MsgBoxes indeed displayed "20Iteration" as expected. I then tried to name the local range IssuerLabels as follows: Range("C3:C5").Name = "SheetName!IssuerLabels" When I run the above instruction, I get: "Run-time error '1004': Application-defined or object-defined error" I then tried running: Range("C3:C5").Name = "ActiveSheet.Name!IssuerLabels" and got the same error message. I then substituted in the actual name of the sheet and ran the instruction: Range("C3:C5").Name = "24Iteration!IssuerLabels" which runs without error. Any help or suggestions will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to make the name Property of the Range Object Local
Greetings OssieMac:
Thanks immensely for your help. I will be putting your latest help to use shortly. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "OssieMac" wrote: Hi again Michael, I haven't been in all day so just got your message. If the sheet name has a space in the name then you need to concatenate single quotes around the name like the following 2 examples. The following code uses the ascii code of a single quote to concatenate the single quotes around the sheet name. Assume sheet name is something like My Sheet with a space between the words. sheetname = ActiveSheet.Name Range("C3:C5").Name = Chr(39) & sheetname & Chr(39) & "!IssuerLabels" The next line uses the single quote enclosed in double quotes to concatenate the single quotes around the sheet name. Range("C3:C5").Name = "'" & sheetname & "'" & "!IssuerLabels" Both methods are basically the same. It is just a matter of preference which method you use. -- Regards, OssieMac "MichaelDavid" wrote: Greetings! I mis-spoke. The Trim just elimianates leading and trailing blanks. I should be using the Replace Function to eliminate ALL blanks. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "OssieMac" wrote: Hi Michael, The reason is that where you use VBA variables in place of real strings. You do not include the variable between the quotes but concatenate it with the part between quotes using the ambersand. Range("C3:C5").Name = Sheetname & "!IssuerLabels" Note that ActiveSheet.Name is really a variable. Range("C3:C5").Name = ActiveSheet.Name & "!IssuerLabels" The above can be confusing where you are actually using the defined names in a range in lieu of the actual address. Named ranges are not VBA variables but belong to the worksheet and are saved with the worksheet and they are included between the quotes just the same as the cell address as per the following example. Range("C3:C5") becomes Range("IssuerLabels") -- Regards, OssieMac "MichaelDavid" wrote: Greetings! On page 128 of "Excel 2007 VBA Programmer's Reference" it says with respect to creating a name which refers to a range: "If you want the name to be local, you can include a worksheet name: Range("F1:F10").Name = "Sheet1!Staff" " In my workbook, BookIssuers.xlsm I have a number of worksheets. The Active Worksheet is "24Iteration" but it could be any of the worksheets in the workbook. I tried to make a range on the Active Worksheet local as follows: First I declared SheetName As String: Dim SheetName As String Then, I retrieved the Sheet Name and displayed it as follows: SheetName = ActiveSheet.Name MsgBox SheetName I also tried: MsgBox ActiveSheet.Name Both of these MsgBoxes indeed displayed "20Iteration" as expected. I then tried to name the local range IssuerLabels as follows: Range("C3:C5").Name = "SheetName!IssuerLabels" When I run the above instruction, I get: "Run-time error '1004': Application-defined or object-defined error" I then tried running: Range("C3:C5").Name = "ActiveSheet.Name!IssuerLabels" and got the same error message. I then substituted in the actual name of the sheet and ran the instruction: Range("C3:C5").Name = "24Iteration!IssuerLabels" which runs without error. Any help or suggestions will be greatly appreciated. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to set NumberFormat Property of Range class | Excel Programming | |||
Unable to set NumberFormat Property of Range class | Excel Programming | |||
unable to get the Interior property of the range class | Excel Programming | |||
unable to set the FormulaArray property of the Range class | Excel Programming | |||
PivotTable Object - Unable to get the name property | Excel Programming |