I"m currently trying to perform a vooklookup/index match/sumproduct (either one) based on the input date the user chooses. Currently i have the code written as (see below). is there a better way to say, based on user input date, match date + unique code and bring the return overs (paste value)
Code
Sub Pull_MTD_In()
Dim start_date As Integer
Dim UserEntry As String
Dim Msg As String
Dim TheDate As String
Dim LR As Long
Dim Wb As Workbook
Set Wb = ActiveWorkbook
Application.ScreenUpdating = False
Application.EnableEvents = False
Msg = "Enter A Date as mm/dd/yyyy"
Do
UserEntry = InputBox(Msg)
If UserEntry = "" Then Exit Sub
Sheets("Bent").Range("E1").Value = UserEntry
If IsDate(UserEntry) Then
Workbooks.Open FileName:= _
"G:\Desktop\Daily In.xlsm"
Wb.Activate
Sheets("Benchmark").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((R1C5='[Daily In.xlsm]MTD Rets'!R2C3:R1048576C3)*(RC[-2]='[Daily In.xlsm]MTD Rets'!R2C1:R1048576C1)*('[Daily In.xlsm]MTD Rets'!R2C2:R1048576C2))"
ActiveCell.Copy
LR = Range("C" & Rows.Count).End(xlUp).Row
Range("C3:C" & LR).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C2:C" & LR).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Workbooks("Daily In.xlsm").Close savechanges:=False
Exit Sub
Else
Msg = "Invalid Date. Please enter date as mm/dd/yyyy"
End If
Loop
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Display More