VBA Alternative to VLOOKUP INDIRECT on closed workbooks?

[Dagnamit]

Hi All,

I know this is an issue that has been discussed many times, but i couldn't find any threads with the approach i'm looking at.

I've often used the indirect function within a vlookup as a dynamic means of referencing the location array i want to lookup on, and this works very well on open workbooks but unfortunately not on closed ones.

I am looking at using VBA to input a normal vlookup (i.e. no indirect function) within a defined range where the 'array' part of the vlookup sits in column A.

I am fairly clueless with VBA but have managed to adopt some code i found when googling that nearly does what i want...

Sub VBVlookup()

Dim MyPath As Variant

MyPath = Worksheets("Journal Consolidation").Range("A2").Text

Range(Cells(2, 5), Cells(25, 16)).Formula = "=iferror(VLOOKUP($C2," & MyPath & ",column()-2,false),0)"

End Sub

This code inputs a vlookup in cells E2:P25 on a workbook defined within MyPath cell A2. This does work for the very first row of the formula (E2:P2), but my problem is I don't know how to get the location in cell A2 to roll down with the vlookup so that E3:P3 uses the array in cell A3 etc..

I hope this makes sense, i can let you know the full structure of my workbooks if that would help but i didn't want to overload everyone with info straight away. Thanks in advance for any help you can provide

Cheers
Chris

 

[AlphaFrog]

Try something like this...

Sub VBVlookup()

    Dim MyPath As String
    
    MyPath = Worksheets("Journal Consolidation").Range("A2").Text
    
    Range("E2:P25").FormulaR1C1 = "=IFERROR(VLOOKUP(RC3," & MyPath & ",COLUMN()-2,FALSE),0)"

End Sub

 

[Dagnamit]

Thanks for the response Alphafrog, i've tried the code you mentioned but i get an "Application-defined or object-defined error", any ideas?

Thanks

 

[AlphaFrog]

what is in cell Worksheets("Journal Consolidation").Range("A2") ?

Maybe try this...

Sub VBVlookup()

    Dim MyPath As String
    
    MyPath = Worksheets("Journal Consolidation").Range("A2").Text

    Range("E2:P2").Formula = "=IFERROR(VLOOKUP($C2," & MyPath & ",COLUMN()-2,FALSE),0)"
    Range("E2:P2").AutoFill Destination:=Range("E2:P25")

End Sub

 

[Dagnamit]

Thanks Alpha, the code you posted does add a vlookup similarly to the code i posted originally, but as you mentioned, i don't think i explained what was in cell "Worksheets("Journal Consolidation").Range("A2")" very well.

This is the complete address of the array i wish to use in the vlookup... so cell A2 reads as "'C:\Users\Chris\Desktop\Excel Test File\Folder A\[Book A.xlsx]Journal'!$A$1:$Z$1000" so i want the vlookup in range E2:P2 to have this as the array and read "=IFERROR(vlookup(C2,'C:\Users\Chris\Desktop\Excel Test File\Folder A\[Book A.xlsx]Journal'!$A$1:$Z$1000,COLUMN()-2,FALSE),0)"

But cells A2:A25 all have different locations and workbook names and i want the vlookup on each row to use the location/array that is in column A on that given row. So the location in cell A3 reads as... "'C:\Users\Chris\Desktop\Excel Test File\Folder B\[Book B.xlsx]Journal'!$A$1:$Z$1000" (different folder and workbook to previous array) and thus i need the vlookup in cells E3:P3 to read... "=IFERROR(vlookup(C2,C:\Users\Chris\Desktop\Excel Test File\Folder B\[Book B.xlsx]Journal'!$A$1:$Z$1000,COLUMN()-2,FALSE),0)".

The code i have so far inserts the vlookup into range E2:P25 but it just uses the location and array that is in cell A2, and not the locations that are in A3,A4,A5 etc.

Sorry i didn't explain this very well previously and thanks for your help.

 

[shg]

A little messy:

Sub VBVlookup()
    Dim iRow As Long
 
    For iRow = 2 To 25
        With Rows(iRow)
            .Range("E1:P1").Formula = "=IFERROR(VLOOKUP(" & _
                                      .Range("C1").Address(False, True) & ", " & _
                                      .Range("A1").Text & _
                                      ", COLUMN()-2, FALSE), 0)"
        End With
    Next iRow
End Sub

 

[AlphaFrog]

You don't necessarily need a macro to do this but here it is...

Sub VBVlookup()

    Range("E2:P25").FormulaR1C1 = "=IFERROR(VLOOKUP(RC3, INDIRECT('Journal Consolidation'!RC1), COLUMN()-2,FALSE),0)"

End Sub

You can use the INDIRECT function in a formula to convert the text within a cell into a cell reference. If you put this formula in E2 and drag copy it to P25, it would (should) do the same as the macro.

=IFERROR(VLOOKUP($C2, INDIRECT('Journal Consolidation'!$A2), COLUMN()-2,FALSE),0)

 

[Dagnamit]

That's exactly what i'm after - thanks Shg4421, i've spent a fair few hours trying to figure this out and that code will save me a lot of time at work next week, so thanks again.

Thanks also Alpha, i did try the vlookup/indirect route but that only works if the workbooks are open, indirect doesn't work when the workbook within it is closed - which it would be in this case.

Cheers
Chris

 

[jasonb75]

Maybe of use for future reference

http://xcell05.free.fr/morefunc/english/indirect.ext.htm

 

[Dagnamit]

Maybe of use for future reference

http://xcell05.free.fr/morefunc/english/indirect.ext.htm

Thanks Jasonb, i've tried the indirect.ext formula also but i think that only works with single cells from a closed workbook doesn't it? Whereas i need to use an array from a closed workbook for this vlookup, apologies if i'm mistaken.

I did also look into the harlan grove pull function, but it seemed to either hang or take quite a while to calculate. I'm sure it was something i was doing wrong but it wouldn't work for me.