| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify defined name range by another cell value
I have a defined range "MyCity"
Cell C4 on sheet "INFO" has the text MyCity (this changes by code) The range "MyCity" is on sheet "Index" how do I in VBA refer to the range "MyCity" or whatever range's name is shown in cell C4. My goal is to change the formatting of whatever range is listed in cell C4. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify defined name range by another cell value
Have you looked at the excel function INDIRECT?
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Spencer Hutton" wrote in message ... I have a defined range "MyCity" Cell C4 on sheet "INFO" has the text MyCity (this changes by code) The range "MyCity" is on sheet "Index" how do I in VBA refer to the range "MyCity" or whatever range's name is shown in cell C4. My goal is to change the formatting of whatever range is listed in cell C4. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify defined name range by another cell value
range(worksheets("INFO").range("c4").value) will point you at the range you
want. "Spencer Hutton" wrote: I have a defined range "MyCity" Cell C4 on sheet "INFO" has the text MyCity (this changes by code) The range "MyCity" is on sheet "Index" how do I in VBA refer to the range "MyCity" or whatever range's name is shown in cell C4. My goal is to change the formatting of whatever range is listed in cell C4. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify defined name range by another cell value
I have this code and it does not work, i get an error. The range whose name
is in C4 is on another sheet, i am not sure if that matters. Dim s As String s = Sheets("INFO").Range("C4").Value Range(s).Locked = True "Spencer Hutton" wrote: I have a defined range "MyCity" Cell C4 on sheet "INFO" has the text MyCity (this changes by code) The range "MyCity" is on sheet "Index" how do I in VBA refer to the range "MyCity" or whatever range's name is shown in cell C4. My goal is to change the formatting of whatever range is listed in cell C4. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify defined name range by another cell value
What error do you get? I've just tried your code and it works on mine.
Do you have any spaces in the cell containing MyCity, ie is the actual value "MyCity " rather than "MyCity" "Spencer Hutton" wrote: I have this code and it does not work, i get an error. The range whose name is in C4 is on another sheet, i am not sure if that matters. Dim s As String s = Sheets("INFO").Range("C4").Value Range(s).Locked = True "Spencer Hutton" wrote: I have a defined range "MyCity" Cell C4 on sheet "INFO" has the text MyCity (this changes by code) The range "MyCity" is on sheet "Index" how do I in VBA refer to the range "MyCity" or whatever range's name is shown in cell C4. My goal is to change the formatting of whatever range is listed in cell C4. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify defined name range by another cell value
i changed it to:
Dim s As String s = ActiveWorkbook.Worksheets("INFO").Range("C4").Valu e ActiveWorkbook.Worksheets("Index").Range(s).Locked = True and it works. "Sam Wilson" wrote: What error do you get? I've just tried your code and it works on mine. Do you have any spaces in the cell containing MyCity, ie is the actual value "MyCity " rather than "MyCity" "Spencer Hutton" wrote: I have this code and it does not work, i get an error. The range whose name is in C4 is on another sheet, i am not sure if that matters. Dim s As String s = Sheets("INFO").Range("C4").Value Range(s).Locked = True "Spencer Hutton" wrote: I have a defined range "MyCity" Cell C4 on sheet "INFO" has the text MyCity (this changes by code) The range "MyCity" is on sheet "Index" how do I in VBA refer to the range "MyCity" or whatever range's name is shown in cell C4. My goal is to change the formatting of whatever range is listed in cell C4. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify defined name range by another cell value
I was just about to post this combined line in which the Range is embedded
with another Range for that... Sheets("Index").Range(Sheets("Info").Range("C4").V alue).Locked = True As you can see, there is no need for the intermediate "s" variable in order to carry the defined name forward. Rick "Spencer Hutton" wrote in message ... i changed it to: Dim s As String s = ActiveWorkbook.Worksheets("INFO").Range("C4").Valu e ActiveWorkbook.Worksheets("Index").Range(s).Locked = True and it works. "Sam Wilson" wrote: What error do you get? I've just tried your code and it works on mine. Do you have any spaces in the cell containing MyCity, ie is the actual value "MyCity " rather than "MyCity" "Spencer Hutton" wrote: I have this code and it does not work, i get an error. The range whose name is in C4 is on another sheet, i am not sure if that matters. Dim s As String s = Sheets("INFO").Range("C4").Value Range(s).Locked = True "Spencer Hutton" wrote: I have a defined range "MyCity" Cell C4 on sheet "INFO" has the text MyCity (this changes by code) The range "MyCity" is on sheet "Index" how do I in VBA refer to the range "MyCity" or whatever range's name is shown in cell C4. My goal is to change the formatting of whatever range is listed in cell C4. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range Defined by Value of Cell | Excel Worksheet Functions | |||
| Selecting a range defined in a cell | Excel Programming | |||
| User Defined Function - Can we identify the cell it's called from | Excel Programming | |||
| Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined | Excel Programming | |||
| How do I identify a range based on cell value? | Excel Programming | |||
Hybrid Mode
