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"
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
--
HTH
JonR