Having a formula reference a a range

[Guest]

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

 

[Doug Glancy]

Paula,

Try this:

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

hth,

Doug Glancy

 

[JulieD]

Hi

my problem with this is that sheet names can't contain / so if the person
enters
1/1/05 in response to this question:
shtdate = InputBox("Enter date of sheet to reference")
you'll have a problem.

maybe putting an example of how you want the data entered (or a default
value) might be useful?
e.g.
shtdate = Inputbox("Enter date of sheet to reference, e.g. 010105")
or
shtdate = Inputbox("Enter date of sheet to reference","Sheet Date","010105")

Cheeers
JulieD

 

[JE McGimpsey]

A template is good, but it's much more friendly to let the user input
the date any way s/he wants, and have VBA do the work:

Public Sub Enter_Formula_In_B7()
Const sDATE_FORMAT = "dd MMM yyyy"
Const sTWO_LINES As String = vbNewLine & vbNewLine
Const sMSG1 As String = "Enter date of sheet to reference:"
Const sMSG2 As String = "Reference must be a date!" & sTWO_LINES
Const sMSG3 As String = "Sheet '%%' does not exist!" & sTWO_LINES
Const sFORMULA As String = _
"=VLOOKUP($A7,'%%'!$A$3:$C$97,2,FALSE)"
Const sTITLE As String = "Enter Formula"
Const nSTRING_TYPE As Long = 2
Dim vInput As Variant
Dim wsRef As Worksheet
Dim sDefault As String
Dim sMsg As String
Dim sDate As String

sMsg = sMSG1
sDefault = Format(Date, sDATE_FORMAT)
Do
vInput = Application.InputBox( _
Prompt:=sMsg, _
Title:=sTITLE, _
Default:=sDefault, _
Type:=nSTRING_TYPE)
If vInput = False Then Exit Sub 'User cancelled
If IsDate(vInput) Then
sDate = Format(vInput, sDATE_FORMAT)
On Error Resume Next
Set wsRef = Sheets(sDate)
On Error GoTo 0
If wsRef Is Nothing Then sMsg = _
Application.Substitute(sMSG3, "%%", sDate) & sMSG1
Else
sMsg = sMSG2 & sMSG1
End If
Loop Until Not wsRef Is Nothing
Range("B7").Formula = _
Application.Substitute(sFORMULA, "%%", sDate)
End Sub

Change the format (sDATE_FORMAT) as desired...