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

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

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

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

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