| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving range string from named range
I have an input box that defines a range, which is then named, i.e. myRange.
Later in the program, I have need of the String version of the range that the user entered. To be specific, the following line of code DOES work: Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value However, the same line, using the SAME range, just in a named format, does NOT work: Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value Does this make sense? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Retrieving range string from named range
If myrange is truly a range found in the listing in Insert=Names=Define
then Dim rng as Range set rng = Sheets(1).Range("MyRange") Sheets(i).Range(rng.Address).Value = rng.Value or With Sheets(1).Range("MyRange") Sheets(i).Range(.Address).Value = .Value End With -- Regards, Tom Ogilvy "clapper" wrote in message ... I have an input box that defines a range, which is then named, i.e. myRange. Later in the program, I have need of the String version of the range that the user entered. To be specific, the following line of code DOES work: Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value However, the same line, using the SAME range, just in a named format, does NOT work: Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value Does this make sense? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Retrieving range string from named range
Tom,
No, the range is not found in 'Names'-'Define'. The sheet is protected, so all of that is greyed out. Maybe I'm calling it the wrong thing. What I'm calling a 'named range' is being created in the macro with the following code: Dim ItemRange As Range Set ItemRange = Application.InputBox(prompt:="Select Range of Items...", Type:=8) Does that shed any light on the issue? "Tom Ogilvy" wrote: If myrange is truly a range found in the listing in Insert=Names=Define then Dim rng as Range set rng = Sheets(1).Range("MyRange") Sheets(i).Range(rng.Address).Value = rng.Value or With Sheets(1).Range("MyRange") Sheets(i).Range(.Address).Value = .Value End With -- Regards, Tom Ogilvy "clapper" wrote in message ... I have an input box that defines a range, which is then named, i.e. myRange. Later in the program, I have need of the String version of the range that the user entered. To be specific, the following line of code DOES work: Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value However, the same line, using the SAME range, just in a named format, does NOT work: Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value Does this make sense? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Retrieving range string from named range
Yes, you have a variable of type Range which is referencing a specific set
of cells in a specific worksheet. So to use that: Sheets(i).Range(ItemRange.Address).Value = ItemRange.Value -- Regards, Tom Ogilvy "clapper" wrote in message ... Tom, No, the range is not found in 'Names'-'Define'. The sheet is protected, so all of that is greyed out. Maybe I'm calling it the wrong thing. What I'm calling a 'named range' is being created in the macro with the following code: Dim ItemRange As Range Set ItemRange = Application.InputBox(prompt:="Select Range of Items...", Type:=8) Does that shed any light on the issue? "Tom Ogilvy" wrote: If myrange is truly a range found in the listing in Insert=Names=Define then Dim rng as Range set rng = Sheets(1).Range("MyRange") Sheets(i).Range(rng.Address).Value = rng.Value or With Sheets(1).Range("MyRange") Sheets(i).Range(.Address).Value = .Value End With -- Regards, Tom Ogilvy "clapper" wrote in message ... I have an input box that defines a range, which is then named, i.e. myRange. Later in the program, I have need of the String version of the range that the user entered. To be specific, the following line of code DOES work: Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value However, the same line, using the SAME range, just in a named format, does NOT work: Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value Does this make sense? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Retrieving range string from named range
That did it. Thanks!
"Tom Ogilvy" wrote: Yes, you have a variable of type Range which is referencing a specific set of cells in a specific worksheet. So to use that: Sheets(i).Range(ItemRange.Address).Value = ItemRange.Value -- Regards, Tom Ogilvy "clapper" wrote in message ... Tom, No, the range is not found in 'Names'-'Define'. The sheet is protected, so all of that is greyed out. Maybe I'm calling it the wrong thing. What I'm calling a 'named range' is being created in the macro with the following code: Dim ItemRange As Range Set ItemRange = Application.InputBox(prompt:="Select Range of Items...", Type:=8) Does that shed any light on the issue? "Tom Ogilvy" wrote: If myrange is truly a range found in the listing in Insert=Names=Define then Dim rng as Range set rng = Sheets(1).Range("MyRange") Sheets(i).Range(rng.Address).Value = rng.Value or With Sheets(1).Range("MyRange") Sheets(i).Range(.Address).Value = .Value End With -- Regards, Tom Ogilvy "clapper" wrote in message ... I have an input box that defines a range, which is then named, i.e. myRange. Later in the program, I have need of the String version of the range that the user entered. To be specific, the following line of code DOES work: Sheets(i).Range("k26:k48").Value = Sheets(1).Range("k26:k48").Value However, the same line, using the SAME range, just in a named format, does NOT work: Sheets(i).Range("myRange").Value = Sheets(1).Range("myRange").Value Does this make sense? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Join non-contiguous ranges into one range via named range? | Excel Discussion (Misc queries) | |||
| Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
| automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
| If any cell in named range = 8 then shade named range | Excel Programming | |||
| Setting named range in VBA- how to set as formula/reference instead of text string? | Excel Programming | |||
Linear Mode
