Paula,

Try this:

ActiveCell.Formula = "=VLOOKUP($A7," & shtdate & "!$A$3:$C$97,2,FALSE)"

hth,

Doug Glancy

"Paula" wrote in message
...
I am trying to get a vlookup formula to reference a range name. The code
is
below - I know it's gotta be possible, but I can't get it to work!
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/12/2004 by
'
Dim shtdate As String
Range("c1").Select
Range("c1").Activate
shtdate = InputBox("Enter date of sheet to reference")
ActiveCell = shtdate


Range("B7").Select
ActiveCell = "=VLOOKUP($A7,shtdate!$A$3:$C$97,2,FALSE)"


End Sub


Any help would be greatly appreciated.
Thanks - Paula