Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apostrophe's apearing in range address
In the following code, RangeNamePrefix = "Jul071" and NewSheet.Name = "July,
2007 - 1" ================================================== = Dim NewRangeName As String Dim RefersTo As String RangeAddress = Replace(Range("DateValues").Address, "$", "") RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress NewRangeName = RangeNamePrefix & "DateValues" ActiveWorkbook.Names.Add Name:=NewRangeName, RefersToR1C1:=RefersTo ================================================== = The variables resolve to the following: RangeAddress: "A20:A450" RefersTo: "='July, 2007 - 1'!A20:A450" NewRangeName: "Jul071DateValues" You would think that after this code runs, the range A20:A450 would be named properly, but it's not. When I go to Insert/Name/Define, the range name is listed corrctly, but the address is wrong. It has apostrophes around the individual cell addresses, like this: ='July, 2007 - 1'!'A20':'A450' I'm stumped. Anyone know what's going on here? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apostrophe's apearing in range address
Do you have a particular need to name a relative address, in usage it will
be relative to the active cell (eg select A1, name A2, select B3 and the name will refer to B4). If that's what you want, use RefersTo instead of RefersToR1C1. Regards, Peter T "ppsa" wrote in message ... In the following code, RangeNamePrefix = "Jul071" and NewSheet.Name = "July, 2007 - 1" ================================================== = Dim NewRangeName As String Dim RefersTo As String RangeAddress = Replace(Range("DateValues").Address, "$", "") RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress NewRangeName = RangeNamePrefix & "DateValues" ActiveWorkbook.Names.Add Name:=NewRangeName, RefersToR1C1:=RefersTo ================================================== = The variables resolve to the following: RangeAddress: "A20:A450" RefersTo: "='July, 2007 - 1'!A20:A450" NewRangeName: "Jul071DateValues" You would think that after this code runs, the range A20:A450 would be named properly, but it's not. When I go to Insert/Name/Define, the range name is listed corrctly, but the address is wrong. It has apostrophes around the individual cell addresses, like this: ='July, 2007 - 1'!'A20':'A450' I'm stumped. Anyone know what's going on here? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apostrophe's apearing in range address
I'm not sure I understand what you're asking. After I name the address, if
the user inserts a row above the range, yes, I need the named range to move down. Is that what you mean? "Peter T" wrote: Do you have a particular need to name a relative address, in usage it will be relative to the active cell (eg select A1, name A2, select B3 and the name will refer to B4). If that's what you want, use RefersTo instead of RefersToR1C1. Regards, Peter T "ppsa" wrote in message ... In the following code, RangeNamePrefix = "Jul071" and NewSheet.Name = "July, 2007 - 1" ================================================== = Dim NewRangeName As String Dim RefersTo As String RangeAddress = Replace(Range("DateValues").Address, "$", "") RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress NewRangeName = RangeNamePrefix & "DateValues" ActiveWorkbook.Names.Add Name:=NewRangeName, RefersToR1C1:=RefersTo ================================================== = The variables resolve to the following: RangeAddress: "A20:A450" RefersTo: "='July, 2007 - 1'!A20:A450" NewRangeName: "Jul071DateValues" You would think that after this code runs, the range A20:A450 would be named properly, but it's not. When I go to Insert/Name/Define, the range name is listed corrctly, but the address is wrong. It has apostrophes around the individual cell addresses, like this: ='July, 2007 - 1'!'A20':'A450' I'm stumped. Anyone know what's going on here? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apostrophe's apearing in range address
From what you say I suspect you don't want a 'relative' name, which is what
you were attempting to create. Try this - Sub Test() [a4].Select ActiveWorkbook.Names.Add "RelName", "=A2" ActiveWorkbook.Names.Add "AbsName", "=$A$2" Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' A5 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2 [b6].Select Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2 Rows(1).Delete Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$1 End Sub Regards, Peter T "ppsa" wrote in message ... I'm not sure I understand what you're asking. After I name the address, if the user inserts a row above the range, yes, I need the named range to move down. Is that what you mean? "Peter T" wrote: Do you have a particular need to name a relative address, in usage it will be relative to the active cell (eg select A1, name A2, select B3 and the name will refer to B4). If that's what you want, use RefersTo instead of RefersToR1C1. Regards, Peter T "ppsa" wrote in message ... In the following code, RangeNamePrefix = "Jul071" and NewSheet.Name = "July, 2007 - 1" ================================================== = Dim NewRangeName As String Dim RefersTo As String RangeAddress = Replace(Range("DateValues").Address, "$", "") RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress NewRangeName = RangeNamePrefix & "DateValues" ActiveWorkbook.Names.Add Name:=NewRangeName, RefersToR1C1:=RefersTo ================================================== = The variables resolve to the following: RangeAddress: "A20:A450" RefersTo: "='July, 2007 - 1'!A20:A450" NewRangeName: "Jul071DateValues" You would think that after this code runs, the range A20:A450 would be named properly, but it's not. When I go to Insert/Name/Define, the range name is listed corrctly, but the address is wrong. It has apostrophes around the individual cell addresses, like this: ='July, 2007 - 1'!'A20':'A450' I'm stumped. Anyone know what's going on here? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apostrophe's apearing in range address
OK, thanks, I'll give it a try. By the way, the curious thing to me about
what you're saying is that I got the RefersToR1C1 by recording a macro of me naming a range. Which worked fine. "Peter T" wrote: From what you say I suspect you don't want a 'relative' name, which is what you were attempting to create. Try this - Sub Test() [a4].Select ActiveWorkbook.Names.Add "RelName", "=A2" ActiveWorkbook.Names.Add "AbsName", "=$A$2" Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' A5 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2 [b6].Select Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2 Rows(1).Delete Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$1 End Sub Regards, Peter T "ppsa" wrote in message ... I'm not sure I understand what you're asking. After I name the address, if the user inserts a row above the range, yes, I need the named range to move down. Is that what you mean? "Peter T" wrote: Do you have a particular need to name a relative address, in usage it will be relative to the active cell (eg select A1, name A2, select B3 and the name will refer to B4). If that's what you want, use RefersTo instead of RefersToR1C1. Regards, Peter T "ppsa" wrote in message ... In the following code, RangeNamePrefix = "Jul071" and NewSheet.Name = "July, 2007 - 1" ================================================== = Dim NewRangeName As String Dim RefersTo As String RangeAddress = Replace(Range("DateValues").Address, "$", "") RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress NewRangeName = RangeNamePrefix & "DateValues" ActiveWorkbook.Names.Add Name:=NewRangeName, RefersToR1C1:=RefersTo ================================================== = The variables resolve to the following: RangeAddress: "A20:A450" RefersTo: "='July, 2007 - 1'!A20:A450" NewRangeName: "Jul071DateValues" You would think that after this code runs, the range A20:A450 would be named properly, but it's not. When I go to Insert/Name/Define, the range name is listed corrctly, but the address is wrong. It has apostrophes around the individual cell addresses, like this: ='July, 2007 - 1'!'A20':'A450' I'm stumped. Anyone know what's going on here? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apostrophe's apearing in range address
Mmmm... not really. Sorry. The only change I made was in the way I assembled
what the macro was doing. No significant changes there. The logic for creating the range is exactly what the macro is doing. The dollar sign thing I added as a precaution, there were no dollar signs in the range I recorded, either. Having said this, I will try what you are suggesting. I'm still stumped, though, by why the apostrophes are added when they are not in the variables my code is generating. Why is Excel doing that, I am left to wonder... Anyway, thank you for your help so far. I'll give it a try tonight. " :) "ppsa" wrote: In the following code, RangeNamePrefix = "Jul071" and NewSheet.Name = "July, 2007 - 1" ================================================== = Dim NewRangeName As String Dim RefersTo As String RangeAddress = Replace(Range("DateValues").Address, "$", "") RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress NewRangeName = RangeNamePrefix & "DateValues" ActiveWorkbook.Names.Add Name:=NewRangeName, RefersToR1C1:=RefersTo ================================================== = The variables resolve to the following: RangeAddress: "A20:A450" RefersTo: "='July, 2007 - 1'!A20:A450" NewRangeName: "Jul071DateValues" You would think that after this code runs, the range A20:A450 would be named properly, but it's not. When I go to Insert/Name/Define, the range name is listed corrctly, but the address is wrong. It has apostrophes around the individual cell addresses, like this: ='July, 2007 - 1'!'A20':'A450' I'm stumped. Anyone know what's going on here? Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apostrophe's apearing in range address
OK, I got it to work, but I'm confused about what I'm seeing. To make it
work, I did two things you suggested: I changed the RefersToR1C1 to RefersTo and got rid of the replace that removes the dollar signs. Here's what's confusing me: if I insert a row above the range, the range addresse changes to accommodate that, just as I want it to. But I would have thought that with the $ it wouldn't. What am I missing? thanks, again. "Peter T" wrote: By the way, the curious thing to me about what you're saying is that I got the RefersToR1C1 by recording a macro of me naming a range. Which worked fine. You may have recorded a macro but then you changed it significantly Referring to your OP, using the recorded macro as a basis and RefersToR1C1, all would have worked if you had done RangeAddress = Range("A20:A450").Address(, , xlR1C1) It would also have worked had you not removed the $'s in an xlA1 style address, which the macro did not do. Regards Peter T "ppsa" wrote in message ... OK, thanks, I'll give it a try. By the way, the curious thing to me about what you're saying is that I got the RefersToR1C1 by recording a macro of me naming a range. Which worked fine. "Peter T" wrote: From what you say I suspect you don't want a 'relative' name, which is what you were attempting to create. Try this - Sub Test() [a4].Select ActiveWorkbook.Names.Add "RelName", "=A2" ActiveWorkbook.Names.Add "AbsName", "=$A$2" Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' A5 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2 [b6].Select Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2 Rows(1).Delete Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$1 End Sub Regards, Peter T "ppsa" wrote in message ... I'm not sure I understand what you're asking. After I name the address, if the user inserts a row above the range, yes, I need the named range to move down. Is that what you mean? "Peter T" wrote: Do you have a particular need to name a relative address, in usage it will be relative to the active cell (eg select A1, name A2, select B3 and the name will refer to B4). If that's what you want, use RefersTo instead of RefersToR1C1. Regards, Peter T "ppsa" wrote in message ... In the following code, RangeNamePrefix = "Jul071" and NewSheet.Name = "July, 2007 - 1" ================================================== = Dim NewRangeName As String Dim RefersTo As String RangeAddress = Replace(Range("DateValues").Address, "$", "") RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress NewRangeName = RangeNamePrefix & "DateValues" ActiveWorkbook.Names.Add Name:=NewRangeName, RefersToR1C1:=RefersTo ================================================== = The variables resolve to the following: RangeAddress: "A20:A450" RefersTo: "='July, 2007 - 1'!A20:A450" NewRangeName: "Jul071DateValues" You would think that after this code runs, the range A20:A450 would be named properly, but it's not. When I go to Insert/Name/Define, the range name is listed corrctly, but the address is wrong. It has apostrophes around the individual cell addresses, like this: ='July, 2007 - 1'!'A20':'A450' I'm stumped. Anyone know what's going on here? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apostrophe's apearing in range address
Here's what's
confusing me: if I insert a row above the range, the range address changes to accommodate that, just as I want it to. But I would have thought that with the $ it wouldn't. What am I missing? Although the $'s makes the address 'absolute the reference updates if you insert/delete rows/columns. It works just the same in a normal cell formula. Try say =$D$4 in some cell. Now insert/delete a row/column above or to the left of D4. The cell formula updates with a new address reference, right? Normally that's helpful but need to be aware if you delete rows or columns that fully include the referenced range you will the get a #REF! error. Again same applies with a ref' in a cell formula ref or using named range that's been fully deleted. Anyway glad you got your Names working. Regards, Peter T PS forgot yesterday - I'm sure you know but the apostrophe's surrounding the sheet name are not only normal but required if the sheet-name includes certain characters, such as punctuation and spaces. "ppsa" wrote in message ... OK, I got it to work, but I'm confused about what I'm seeing. To make it work, I did two things you suggested: I changed the RefersToR1C1 to RefersTo and got rid of the replace that removes the dollar signs. Here's what's confusing me: if I insert a row above the range, the range addresse changes to accommodate that, just as I want it to. But I would have thought that with the $ it wouldn't. What am I missing? thanks, again. "Peter T" wrote: By the way, the curious thing to me about what you're saying is that I got the RefersToR1C1 by recording a macro of me naming a range. Which worked fine. You may have recorded a macro but then you changed it significantly Referring to your OP, using the recorded macro as a basis and RefersToR1C1, all would have worked if you had done RangeAddress = Range("A20:A450").Address(, , xlR1C1) It would also have worked had you not removed the $'s in an xlA1 style address, which the macro did not do. Regards Peter T "ppsa" wrote in message ... OK, thanks, I'll give it a try. By the way, the curious thing to me about what you're saying is that I got the RefersToR1C1 by recording a macro of me naming a range. Which worked fine. "Peter T" wrote: From what you say I suspect you don't want a 'relative' name, which is what you were attempting to create. Try this - Sub Test() [a4].Select ActiveWorkbook.Names.Add "RelName", "=A2" ActiveWorkbook.Names.Add "AbsName", "=$A$2" Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' A5 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2 [b6].Select Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$2 Rows(1).Delete Debug.Print ActiveWorkbook.Names("RelName").RefersTo ' B7 Debug.Print ActiveWorkbook.Names("AbsName").RefersTo ' $A$1 End Sub Regards, Peter T "ppsa" wrote in message ... I'm not sure I understand what you're asking. After I name the address, if the user inserts a row above the range, yes, I need the named range to move down. Is that what you mean? "Peter T" wrote: Do you have a particular need to name a relative address, in usage it will be relative to the active cell (eg select A1, name A2, select B3 and the name will refer to B4). If that's what you want, use RefersTo instead of RefersToR1C1. Regards, Peter T "ppsa" wrote in message ... In the following code, RangeNamePrefix = "Jul071" and NewSheet.Name = "July, 2007 - 1" ================================================== = Dim NewRangeName As String Dim RefersTo As String RangeAddress = Replace(Range("DateValues").Address, "$", "") RefersTo = "='" & NewSheet.Name & "'!" & RangeAddress NewRangeName = RangeNamePrefix & "DateValues" ActiveWorkbook.Names.Add Name:=NewRangeName, RefersToR1C1:=RefersTo ================================================== = The variables resolve to the following: RangeAddress: "A20:A450" RefersTo: "='July, 2007 - 1'!A20:A450" NewRangeName: "Jul071DateValues" You would think that after this code runs, the range A20:A450 would be named properly, but it's not. When I go to Insert/Name/Define, the range name is listed corrctly, but the address is wrong. It has apostrophes around the individual cell addresses, like this: ='July, 2007 - 1'!'A20':'A450' I'm stumped. Anyone know what's going on here? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Apostrophe's becoming upside down question marks on the web | Excel Discussion (Misc queries) | |||
How to create a range address with ADDRESS function? | Excel Worksheet Functions | |||
getting the absolute range address from a dynamic named range | Excel Programming | |||
How to convert numbers with apostrophe's (ex. 219'2) to decimals? | Excel Worksheet Functions | |||
To data apearing in other sheets I can use =SUM(. How I can have . | Excel Worksheet Functions |