Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to named ranges
Hi, What is best practise in referring to named ranges in VBA? I have dynamic named ranges in Excel which I wish to refer to in VBA. Sub Update() Dim FIData As Range Set rngFIData = Range("FIData") Range("rngFIData").Select End Sub I get runtime error on the last line. The range name "FIData" is valid in the sheet. Again, I am not sure I am following best practise, but my experience is that somtimes it appears to work in some workbooks and not others... Thanks -- Hardy ------------------------------------------------------------------------ Hardy's Profile: http://www.excelforum.com/member.php...fo&userid=3163 View this thread: http://www.excelforum.com/showthread...hreadid=398985 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to named ranges
Hi Hardy,
Try this: Sub Update() Dim FIData As Range Set rngFIData = Range("FIData") rngFIData.Select : : End Sub or this Sub Update() Range("FIData").Select : : End Sub Good luck, Wouter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to named ranges
You are setting a range object correctly when using a named range, but as it
is already a range object variable, you do not need to use the Range object in conjunction with the variable. rngFIData.Select -- HTH RP (remove nothere from the email address if mailing direct) "Hardy" wrote in message ... Hi, What is best practise in referring to named ranges in VBA? I have dynamic named ranges in Excel which I wish to refer to in VBA. Sub Update() Dim FIData As Range Set rngFIData = Range("FIData") Range("rngFIData").Select End Sub I get runtime error on the last line. The range name "FIData" is valid in the sheet. Again, I am not sure I am following best practise, but my experience is that somtimes it appears to work in some workbooks and not others... Thanks -- Hardy ------------------------------------------------------------------------ Hardy's Profile: http://www.excelforum.com/member.php...fo&userid=3163 View this thread: http://www.excelforum.com/showthread...hreadid=398985 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to named ranges
Hi Hardy,
Providing the name "FIData" refers to a range on the activesheet your code should run fine. But if that's not the case you first need to activate the parent sheet before selecting the range. Sub Update2() Dim FIData As Range Set rngFIData = Range("FIData") If Not rngFIData.Parent Is ActiveSheet Then rngFIData.Parent.Activate End If rngFIData.Select End Sub Typically you don't need to select the range to do things with it. So even if "FIData" does not refer to a range in the activesheet you could do simply Range("FIData").Interior.ColorIndex = 6 If "FIData" is a Worksheet level name and not in the active sheet, then you would need to refer to it like this - Range("'my Sheet'!F1Data") All the above assumes at least working with the active workbook Regards, Peter T "Hardy" wrote in message ... Hi, What is best practise in referring to named ranges in VBA? I have dynamic named ranges in Excel which I wish to refer to in VBA. Sub Update() Dim FIData As Range Set rngFIData = Range("FIData") Range("rngFIData").Select End Sub I get runtime error on the last line. The range name "FIData" is valid in the sheet. Again, I am not sure I am following best practise, but my experience is that somtimes it appears to work in some workbooks and not others... Thanks -- Hardy ------------------------------------------------------------------------ Hardy's Profile: http://www.excelforum.com/member.php...fo&userid=3163 View this thread: http://www.excelforum.com/showthread...hreadid=398985 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to named ranges
I didn't read your code correctly, fails for the reason Bob mentioned.
Peter T "Peter T" <peter_t@discussions wrote in message ... Hi Hardy, Providing the name "FIData" refers to a range on the activesheet your code should run fine. But if that's not the case you first need to activate the parent sheet before selecting the range. Sub Update2() Dim FIData As Range Set rngFIData = Range("FIData") If Not rngFIData.Parent Is ActiveSheet Then rngFIData.Parent.Activate End If rngFIData.Select End Sub Typically you don't need to select the range to do things with it. So even if "FIData" does not refer to a range in the activesheet you could do simply Range("FIData").Interior.ColorIndex = 6 If "FIData" is a Worksheet level name and not in the active sheet, then you would need to refer to it like this - Range("'my Sheet'!F1Data") All the above assumes at least working with the active workbook Regards, Peter T "Hardy" wrote in message ... Hi, What is best practise in referring to named ranges in VBA? I have dynamic named ranges in Excel which I wish to refer to in VBA. Sub Update() Dim FIData As Range Set rngFIData = Range("FIData") Range("rngFIData").Select End Sub I get runtime error on the last line. The range name "FIData" is valid in the sheet. Again, I am not sure I am following best practise, but my experience is that somtimes it appears to work in some workbooks and not others... Thanks -- Hardy ------------------------------------------------------------------------ Hardy's Profile: http://www.excelforum.com/member.php...fo&userid=3163 View this thread: http://www.excelforum.com/showthread...hreadid=398985 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to specific cell in named range in Excel formula | Excel Worksheet Functions | |||
How do I use indirect when referring to a named range in a closed | Excel Worksheet Functions | |||
referring to a named range on another worksheet | Excel Programming | |||
Totalling by referring to 2 date ranges | Excel Discussion (Misc queries) | |||
Totalling by referring to 2 date ranges | Excel Discussion (Misc queries) |