Inserting Input Box in VBA for Excel Macros

[Sav09]

Hi,
I have a file which needs to be looked up for data in certain fields on a daily basis. I've created a macro in Excel using INDEX & MATCH.
The lookup file name is the same barring the date which changes on a daily basis.
I need to run the macro daily to lookup a new value to the master file, however need to go to the VBA code to change the date & then run the Macro.

I'm a newbie & need help with the code to have an date input box so that i do not need to go to the VBA code to change the date daily. If I could input the date in the input box & then run the macro without going into the VBA code to change the date, it would be of great help. Please assist.

Details of the file code as below (Date highlighted, needs to be changed on a daily basis, post which lookup is done)

Sub Lookup()
'
' Lookup Macro
'

'
ActiveCell.FormulaR1C1 = _
"=INDEX('[RBL Retail Portfolio Dump as on 8-Dec-18.xlsx]Sheet1'!R2C1:R101C6,MATCH(RC2,'[RBL Retail Portfolio Dump as on 8-Dec-18.xlsx]Sheet1'!R2C2:R101C2,0),MATCH(R1C,'[RBL Retail Portfolio Dump as on 8-Dec-18.xlsx]Sheet1'!R1C1:R1C6,0))"
Range("D2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("F2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E12").Select
Application.CutCopyMode = False
End Sub

 

[dmt32]

Hi
welcome to forum

Try

Sub Lookup()
'
' Lookup Macro
'


Dim GetInput As Variant


Do
GetInput = InputBox("Enter Date", "Enter Date")
'cancel pressed
If StrPtr(GetInput) = 0 Then Exit Sub


Loop Until IsDate(GetInput)


GetInput = Format(GetInput, "d-mmm-yy")


ActiveCell.FormulaR1C1 = "=INDEX('[RBL Retail Portfolio Dump as on " & GetInput & _
".xlsx]Sheet1'!R2C1:R101C6,MATCH(RC2,'[RBL Retail Portfolio Dump as on " & GetInput & _
".xlsx]Sheet1'!R2C2:R101C2,0),MATCH(R1C,'[RBL Retail Portfolio Dump as on " & GetInput & _
".xlsx]Sheet1'!R1C1:R1C6,0))"


' rest of code



End Sub

Dave

 

[Sav09]

This worked for me.
Thanks Dave, for taking out the time & effort to provide a solution. Appreciate it.

 

[Sav09]

This worked for me.
Thanks Dave, for taking out the time & effort to provide a solution. Appreciate it.

Hi Dave,

One more query.
If the input date format of the file changes to ddmmyyyy instead of the earlier one(DD-MM-YY), how would the code change.

Regards
Savio

 

[dmt32]

just change to the required format

GetInput = Format(GetInput, "ddmmyyyy")

Dave