vlookup with input box to select a new month when run

[preseb]

I need to create a macro that will do a vlookup from one workbook to another where the range will change every month.

How can I setup this macro so when there is a new month, the range changes?

This is what I have so far, but it does not handle the change for a new month.

Sub IPOPBudgetData()

Dim strFile As String
Dim Tempfile As Workbook
Dim LR As Long

strFile = Application.GetOpenFilename
Workbooks.Open strFile
Set Tempfile = ActiveWorkbook

Sheets("IP").Select

Windows("Allowance Macro.xls").Activate
Sheets("By_Trans_Code").Select
Range("E2").Select
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("E2:E" & LR)
.Formula = "=VLOOKUP(RC[-4],'[2011 Allowance Budget by Trans Code.xls]OP'!C3:C17,15,0)"
.Value = .Value
End With



Application.CutCopyMode = False
Tempfile.Close
End Sub

I did find this code, but instead of mm.dd.yy, i would like to enter Jan OP, Feb OP, Mar OP, etc

Sub test()
Dim varDate
varDate = Application.InputBox("Enter the date of the target workbook's name", "Enter Date", Format(Date, "mm.dd.yy"), , , , , Type:=2)
If IsDate(Replace(varDate, ".", "/")) Then
varDate = Format(CDate(Replace(varDate, ".", "/")), "mm.dd.yy")
Else
MsgBox "Invalid date entered - exiting sub!": Exit Sub
End If
Range("B1").FormulaR1C1 = "=VLOOKUP(RC[-1],'[PN " & varDate & ".xls]Sheet1'!C1:C2,2,0)"

End Sub


Thanks

[preseb]

I am going to try and rephrase my question.
How can I take a formula like this: =INDEX('[Budget by Trans Code.xls]OP'!$Q:$Q,MATCH(A:A,'[Budget by Trans Code.xls]OP'!$C:$C,0))

turn it into VBA, where $Q:$Q is changed based on an input box and what needs to go into that input box is the column title e.g. Jan OP, Feb OP, Mar OP, etc.

[CatDaddy]

cell.Formula = "=INDEX('[Budget by Trans Code.xls]OP'!" & _
InputBox("Enter the date of the target workbook's name", _
"Enter Date", _
Format(Date, "mm.dd.yy"), , , , ,_
Type:=2) & _
",MATCH(A:A,'[Budget by Trans Code.xls]OP'!$C:$C,0))"

[preseb]

CatDaddy, thanks for your reply. I noticed that the Format(Date, "mm.dd.yy"), , , , ,_
Type:=2)
is in your reply. I need my input to be Jan OP, Feb OP, Mar OP, etc. These are all column titles. I want to find that column title to then select the column and pull the needed data.

thanks

[CatDaddy]

cell.Formula = "=INDEX('[Budget by Trans Code.xls]OP'!" & _
InputBox("Enter the date of the target workbook's name", "Enter Date") & _
",MATCH(A:A,'[Budget by Trans Code.xls]OP'!$C:$C,0))"

[preseb]

I keep getting Run-time error 424, I am not sure why

[CatDaddy]

Dim ipBOX
ipBOX = InputBox("Enter the date of the target workbook's name", "Enter Date")
SOMECELL.value = "=INDEX('[Budget by Trans Code.xls]OP'!" & ipBox & _
",MATCH(A:A,'[Budget by Trans Code.xls]OP'!$C:$C,0))"

[preseb]

I am still get the same error with Object Required. the debug is highlighting the Somecell.value section

[preseb]

Is my ipBOX looking at what the column should be? When I am trying to have it find a column title in order to select that column?