Need help with named ranges in VBA

[JonR]

Hi,

I have a spreadsheet which lists all of our personnel and their vacation
days (manually entered). I've set up dynamic named ranges for each person in
order to query their vacation days and see if they are available for duty on
a particular day. The names are defined in the spreadsheet, not in VBA.

I'm trying to use a string variable to call the correct range and look for
the date in question to see if the individual will be on vacation the day his
name comes up on the rotation (in which case he will be substituted). I'm
getting Error 1004 Application or Object Defined Error on my With statement
in the code below.

How do I refer to a named range in VBA, using a find function?

'stName is a variable, determined by who is up for duty next.
'dtDate is the duty day. If this day shows up in the dynamic range that is
defined
'by the person's name, then blAvail is TRUE,
'meaning he is available for duty that day



With Worksheets("Vacation Days").Range(stName)
Set c = .Find(dtDate)
If c Is Nothing Then
blAvail = True
End If
End With

 

[Jim Cone]

Two examples that might help...

'JimsRng is a name defined in the spreadsheet.

'Assign the named range name to a String variable.
Sub nnn()
Dim stName As String
stName = Worksheets(2).Range("JimsRng").Name.Name
Worksheets(2).Range(stName).Select 'or Find...
End Sub

'Use the named range directly in the code.
Sub mmm()
Worksheets(2).Range("JimsRng").Select 'or Find...
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"JonR" <[email protected]>
wrote in message
Hi,
I have a spreadsheet which lists all of our personnel and their vacation
days (manually entered). I've set up dynamic named ranges for each person in
order to query their vacation days and see if they are available for duty on
a particular day. The names are defined in the spreadsheet, not in VBA.

I'm trying to use a string variable to call the correct range and look for
the date in question to see if the individual will be on vacation the day his
name comes up on the rotation (in which case he will be substituted). I'm
getting Error 1004 Application or Object Defined Error on my With statement
in the code below.

How do I refer to a named range in VBA, using a find function?

'stName is a variable, determined by who is up for duty next.
'dtDate is the duty day. If this day shows up in the dynamic range that is
defined
'by the person's name, then blAvail is TRUE,
'meaning he is available for duty that day

With Worksheets("Vacation Days").Range(stName)
Set c = .Find(dtDate)
If c Is Nothing Then
blAvail = True
End If
End With

 

[JMB]

You are sure the dynamic named range reference is correct? Your code snippet
works perfectly on my machine, but I will get the error you describe if I try
to reference a range name that does not exist.

Step throught the macro and, in the immediate window (after stName is
initialized), type
?Worksheets("Vacation Days").Range(stName).Address(True, True, xlA1, True)

and verify the dynamic named range address is correct.

 

[JonR]

Thanks for the code guys, it was a big help. Found out it was a really
stupid error -- I had defined a named range, but there was nothing in it. No
wonder I'm getting errors! Duh!!