Set WB Name & using VLOOKUP

[Legacy 313594]

Any particular reason why my vlookup code isnt working? I am using 2 workbooks & want to set the open workbook as "PriorBDay" & use that Name as the VlOokup source.

Please help

Dim dtTestDate As Date
    Dim sStartWB As String
    
    Const sPath As String = "G:\ManagedAccounts\Operations\ReconTool\Unknown and Halted Accounts\Unknown Account Archive\"
    Const dtEarliest = #3/9/2015# '--to stop loop if file not found by earliest valid date.
    dtTestDate = Date
    sStartWB = ActiveWorkbook.Name
    
    While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
        On Error Resume Next
        Workbooks.Open sPath & "Unknown Accounts " & Format(dtTestDate - 1, "MM-DD-YY") & ".xlsx"
        dtTestDate = dtTestDate - 1
        On Error GoTo 0
    Wend
    
    If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."
    
    Set PriorBDay = ThisWorkbook
    
'Activate Unknown List Workbook
    Windows("Unknown List.xlsx").Activate

'vLOOKUP EACH WORKSHEET
    Worksheets("EV Accounts").Activate
    
'Value Vlookup
    Range("D2").Formula = _
        "=IF(VLOOKUP(C[-1],'[PriorBDay]EV Accounts'!C3:C4,2,FALSE)=0,"""",(VLOOKUP(C[-1],'[PriorBDay]EV Accounts'!C3:C4,2,FALSE)))"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D99")

 

[jasonb75]

Instead of

[PriorBDay]

Try

" & PriorBDay.Name & "

 

[Legacy 313594]

JASONB75... Are you referring to changing the Vlookup formula to

" & PriorBDay.Name & "

? Could it also be that my code has

 sStartWB = ActiveWorkbook.Name
    
    While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
        On Error Resume Next
        Workbooks.Open sPath & "Unknown Accounts " & Format(dtTestDate - 1, "MM-DD-YY") & ".xlsx"
        dtTestDate = dtTestDate - 1

Set as the active name?

 

[jasonb75]

I was referring to the formula, but previously I missed another problem with it, (also a typo on my part probably didn't help)

Because PriorBDay is referring to an object in vba a formula in excel will not recognise it, you have to convert it to something that excel can use in a formula first.

The other problem that I just noticed is that your formula uses R1C1 references, but you were trying to write it as an A1 formula which will cause the code to fail because the formula is invalid.

Try replacing

    Range("D2").Formula = _
        "=IF(VLOOKUP(C[-1],'[PriorBDay]EV Accounts'!C3:C4,2,FALSE)=0,"""",(VLOOKUP(C[-1],'[PriorBDay]EV Accounts'!C3:C4,2,FALSE)))"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D99")

With

    Range("D2:D99").FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-1],'[" & PriorBDay.Name & "]EV Accounts'!C3:C4,2,FALSE)=0,"""",(VLOOKUP(RC[-1],'[" & PriorBDay.Name & "]EV Accounts'!C3:C4,2,FALSE)))"

This method eliminates the need for the subsequent autofill. Hopefully it should work this time, if it's slow to run then there are other methods that could speed things up a bit.