Prior Workday Dynamic Reference Within VLookUp Formula - VBA

0
votes

I am somewhat of a noob when it comes to VBA programming. I am running a macro script daily on each day's distinct tab title MM-DD-YY. I want to do a vlookup on today's date using the lookup range from the prior business day. For instance, if today is 07/31/20 I want the vlookup to be VLOOKUP(Q7, '07-30-20'!$P$1:$Q$29, 2, FALSE). How do you code this to automatically populate the vlookup using the prior business day? Below is the noob code I have at this point.

Range("Q2").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(Q7, '07-29-20'!$P$1:$Q$29, 2, FALSE)"
    Selection.AutoFill Destination:=Range("Q2:Q" & Range("E" & Rows.Count).End(xlUp).Row)
2
excelvbavlookup
look at the Excel WORKDAY function. Or write one in VBA and use it's results in your VLOOKUP formula. – Ron Rosenfeld

2 Answers

0
votes

Try:

ActiveCell.FormulaR1C1 = "=VLOOKUP(Q7,'" & Right("0" & Month(Date), 2) & "-" & Right("0" & Day(Date), 2) - 1 & "-" & Right(Year(Date), 2) & "'!$P$1:$Q$29, 2, FALSE)"

0
votes

You could do something like:

Dim dprevDate As Date, sprevDate As String
  dprevDate = WorksheetFunction.WorkDay(Date, -1)
  sprevDate = Format(dprevDate, "mm-dd-yy")

ActiveCell.Formula = "=VLOOKUP(Q7, '" & sprevDate & "'!$P$1:$Q$29, 2, FALSE)"