VLOOKUP/MACRO referencing data in a report dated 1 day previous.

[Rhinogaz]

Hi All I am trying to write a VLOOKUP formula in a macro run from a template report to Return data from yesterdays report (which started life as the template then saved as that days report). The normal VLOOKUP formula works fine normally when both reports are open. I wanted to automate the process as much as possible which is why I wanted to incorporate the formula in a macro. The date in the file is displayed as "Report 19.12.19.xls". When I run the macro it stops with a compile error: Sub or Function not defined and highlights TODAY in the formula. Can anyone help please?

' ActiveCell.FormulaR1C1 = _
    '   "=VLOOKUP(C12,INDIRECT("[Offline Report_"&TEXT(TODAY()-1,"DD.MM.YY")&".XLS]Offline Tuesday to Friday Repor'!C12:C17,6,FALSE))

 

[BobUmlas]

TODAY() is an unknown function in VBA. Use DATE().

 

[Rhinogaz]

Thanks Bob, I tried it but in the original formula it still comes back with an error. I can get the formula to work as per attached which points directly to the file. -

ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(C12,'[Offline report 16.12.19.xls]Offline Tuesday to Friday Repor'!C12:C17,5,FALSE)"

Because the file changes name by day this means I would have to change the date in the macro before running it, I am trying to get the macro to always look at the file from the day before and possibly 3 days before on a Monday so I don't have to edit the macro every day. Any ideas?

 

[BobUmlas]

TheDate=Date
If application.Weekday(TheDate)=1 then TheDate=TheDate-3 'Monday
TheDate=Format(TheDate,"dd.mm.yy")

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C12,INDIRECT("[Offline Report_"& TheDate &".XLS]Offline Tuesday to Friday Repor'!C12:C17,6,FALSE))

 

[Rhinogaz]

TheDate=Date
If application.Weekday(TheDate)=1 then TheDate=TheDate-3 'Monday
TheDate=Format(TheDate,"dd.mm.yy")

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C12,INDIRECT("[Offline Report_"& TheDate &".XLS]Offline Tuesday to Friday Repor'!C12:C17,6,FALSE))

Hi Bob sorry for the delay (Christmas and all that) and thanks for the attached but unfortunately as soon as I put it in my VB code
the following line turns red straight away any shows a compile error -

ActiveCell.FormulaR1C1 = _
         "=VLOOKUP(C12,INDIRECT("[Offline Report_"& TheDate &".xls]Offline Tuesday to Friday Repor'!C12:C17,6,FALSE))

it highlights

[Offline Report_"& TheDate &".xls]

and shows the error message 'Expected end of statement'.

Is there a way around this?

Thanks again

 

[BobUmlas]

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C12,INDIRECT(""[Offline Report_ & TheDate & .xls]Offline Tuesday to Friday Repor'!C12:C17""),6,FALSE)"

(Sorry)

 

[Rhinogaz]

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C12,INDIRECT(""[Offline Report_ & TheDate & .xls]Offline Tuesday to Friday Repor'!C12:C17""),6,FALSE)"

(Sorry)

Hi Bob thanks for all your efforts.

This works in that the macro runs till the end but the formula returns #Ref where it should return comments from the previous days report (Tues-Fri & Fridays report on a Monday). So we're getting there and probably just needs tweeking. In the formula C12 relates to column L this being the 'Lookup value' and C12:C17 is column L:Q is the 'Table array' with the comments to return being in Columns P and Q (I have two formulas for each column P and Q).

Also because the reports change daily the number of rows containing data also change daily so I would like the code to repeat the formulas down the column to the last data entry.

Sorry to be a pain but I hope you can still help.

Thanks

 

[BobUmlas]

Change C12:C17 to L:Q and change FormulaR1C1 to Formula.
If you were truly using R1C1 notation then your reference to C12 would mean all of column L, which you can't use for a lookup value. If this still doesn't work, please supply a sample of what the formula SHOULD look like once the macro runs, like =VLOOKUP(G6,INDIRECT("[Offline Report_3-4-19.xls]Tuesday to Friday Repor'!L:Q"),6,FALSE), for example.
I don't understand your comment about columns P & Q

 

[Rhinogaz]

Change C12:C17 to L:Q and change FormulaR1C1 to Formula.
If you were truly using R1C1 notation then your reference to C12 would mean all of column L, which you can't use for a lookup value. If this still doesn't work, please supply a sample of what the formula SHOULD look like once the macro runs, like =VLOOKUP(G6,INDIRECT("[Offline Report_3-4-19.xls]Tuesday to Friday Repor'!L:Q"),6,FALSE), for example.
I don't understand your comment about columns P & Q

Hi Bob

No it didn't work and still returned #Ref!

The P and Q columns relate to two sets of comments and two separate vlookup formulas in P and Q so in P the number reference would be 5 and Q would be 6, apologies for the confusion.

This is the formula I would use directly in the cell P2-

=VLOOKUP(L:L,'[Offline report 27.12.19.xls]Offline Tuesday to Friday Repor'!L:Q,5,FALSE)

This is the formula I would use directly in the cell Q2-

=VLOOKUP(L:L,'[Offline report 27.12.19.xls]Offline Tuesday to Friday Repor'!L:Q,6,FALSE)

I hope this helps.
Thanks

 

[BobUmlas]

You can't VLOOKUP(L:L... - that's what's giving you the #REF error. You look up one value, not en entire column. Something like =VLOOKUP(L1,...