Vlookup in VBA errors

[AllMB]

I am trying to run a Vlookup inside a VBA. I am trying to run a dynamic Vlookup with two changing variables that will pull the needed data with the other workbooks closed. The two changing variables are Month and Year. If there is a better way to do this I am open to suggestions.

I am new at VBA and learning as I go. Any help you can give with the code below would be great. The changing variables will help this spreadsheet be used for a long time and by many others besides myself. There are 6 others who will be using this spreadsheet.

The network drive path when the workbook is closed looks like this: O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control\January 2019\VTR Position Control January 2019




Dim ThisMonth As String
Dim TheYear As String
Dim ThisDivision As String



ThisMonth = Range("E2").Value
TheYear = Range("F2").Value
ThisDivision = Range("A121").Value

With Range("C126")
Application.WorksheetFunction.VLookup(A126,"'O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control" & "TheYear ThisMonth" & "\[" & "ThisDivision Position Control ThisMonth TheYear" & ".xlsx]Position Control'!$A$2:$B$2", 2, False)


End With
End Sub

 

[GR00007]

Give this a try:
This is assuming E2="January", F2="2019", A126="VTR" and your file name is:
O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control\January 2019\VTR Position Control January 2019.xlsx
And the sheet being used is:
Position Control

    ThisMonth = Range("E2").Value
    ThisYear = Range("F2").Value
    ThisDivision = Range("A126").Value
    ThisFile = "O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY " & ThisYear & " Budget Resources\FY " & ThisYear & _
               " Position Control\" & ThisMonth & " " & ThisYear & "\[" & ThisDivision & " Position Control " _
               & ThisMonth & " " & ThisYear & ".xlsx]"
    Range("C126").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'" & ThisFile & "Position Control'!$A$2:$B$2,2,FALSE)"

 

[AllMB]

Give this a try:
This is assuming E2="January", F2="2019", A126="VTR" and your file name is:
O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control\January 2019\VTR Position Control January 2019.xlsx
And the sheet being used is:
Position Control

    ThisMonth = Range("E2").Value
    ThisYear = Range("F2").Value
    ThisDivision = Range("A126").Value
    ThisFile = "O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY " & ThisYear & " Budget Resources\FY " & ThisYear & _
               " Position Control\" & ThisMonth & " " & ThisYear & "\[" & ThisDivision & " Position Control " _
               & ThisMonth & " " & ThisYear & ".xlsx]"
    Range("C126").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'" & ThisFile & "Position Control'!$A$2:$B$2,2,FALSE)"

The assumptions are just off by a little bit. A121 is "VTR". The workbook that is being referenced is "Position Control". The whole point of doing this in VBA is to keep the Position Control file closed. Trying to keep the number of workbooks open using indirect(conat( to a minimum.

I will try to replace a few items in the code and see if it will work.

Replaced "A126" with "A121" for the division range and ended up with an error.

Run-time error '1004':
Application-defined or object-defined error

 

[GR00007]

The network drive path when the workbook is closed looks like this: O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control\January 2019\VTR Position Control January 2019
ThisMonth = Range("E2").Value
TheYear = Range("F2").Value
ThisDivision = Range("A121").Value
With Range("C126")
Application.WorksheetFunction.VLookup(A126,"'O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control" & "TheYear ThisMonth" & "\[" & "ThisDivision Position Control ThisMonth TheYear" & ".xlsx]Position Control'!$A$2:$B$2", 2, False)
End With

If what you are looking up is in A126, use C126 for the range to put the VLOOKUP, it's using relative position back to A for the value to lookup. Please change $A$2:$B$2 to $A$2:$B$xxxx where xxx is the last row or even A:B for the lookup range

 

[AllMB]

If what you are looking up is in A126, use C126 for the range to put the VLOOKUP, it's using relative position back to A for the value to lookup. Please change $A$2:$B$2 to $A$2:$B$xxxx where xxx is the last row or even A:B for the lookup range

I am using this right now:

im ThisMonth As String
Dim TheYear As String
Dim ThisDivision As String



ThisMonth = Range("F2").Value
TheYear = Range("E2").Value
ThisDivision = Range("A121").Value

With Range("C126")
Application.WorksheetFunction.VLookup(A126,"'O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control" & "TheYear ThisMonth" & "\[" & "ThisDivision Position Control ThisMonth TheYear" & ".xlsx]Position Control'!$A$2:$B$2", 2, False)

End With

End Sub


I get a "compile error: Expected ="

 

[GR00007]

You can't put variables within quotes and have them work.
Please provide the EXACT name of the closed file you are trying to reference.
The example given in #2 above was (with a different file name) obtaining information from a closed workbook.

 

[AllMB]

'O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control\Jan 2019\[VTR Position Control January 2019.xlsx]Position Control'!$A$2:$B$2

This is the exact file I am referencing.

 

[GR00007]

'O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control\Jan 2019\[VTR Position Control January 2019.xlsx]Position Control'!$A$2:$B$2

Based on what you gave the actual file name is "VTR Position Control January 2019.xlsx"
The tab to access within the workbook is "Position Control"
The directory is "O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control\Jan 2019"

Variable ThisYear contains "2019"
Variable ThisMonth contains "January"
Variable ThisShortMonth contains "Jan"
Variable ThisDivision contains "VTR"

ThisFile = "O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY " & ThisYear & " Budget Resources\FY " & ThisYear & _
           " Position Control\" & ThisShortMonth & " " & ThisYear & "\[" & ThisDivision & " Position Control " _
           & ThisMonth & " " & ThisYear & ".xlsx]"

The folder and file name are built by the above code with the addition of another variable for ThisShortMonth
Variable ThisFile contains "O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY 2019 Budget Resources\FY 2019 Position Control\Jan 2019\[VTR Position Control January 2019.xlsx]"
From your prior post you are trying to lookup a value that is in A126, so these two lines should return the result of the lookup even if the file is closed.

Range("C126").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'" & ThisFile & "Position Control'!$A$2:$B$999,2,FALSE)"

I hope you don't have more than 998 rows of data, and that this resolves your question, and the one posted here: https://www.mrexcel.com/forum/excel-questions/1088248-concat-inside-vlookup-formula.html

 

[AllMB]

I am still getting an error "Application-defined or object defined error". The error happens on the Vlookup

Dim ThisMonth As String
Dim TheYear As String
Dim ThisDivision As String
Dim ThisShortMonth As String




ThisMonth = Range("F2").Value 'January
TheYear = Range("E2").Value '2019
ThisDivision = Range("A121").Value 'VTR
ThisShortMonth = Range("G2").Value 'Jan



ThisFile = "O:\MVD\USERS\COOCFO-SHARED\Finance\Budget\FY " & ThisYear & " Budget Resources\FY " & ThisYear & " Position Control" & ThisShortMonth & " " & ThisYear & "\[" & ThisDivision & " Position Control " & ThisMonth & " " & ThisYear & ".xlsx]"

Range("C126").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'" & ThisFile & "Position Control'!$A$2:$B$2,2,FALSE)"

 

[GR00007]

I apologize, the last line should have FormulaR1C1 format throughout:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'" & ThisFile & "Position Control'!C[-2]:C[-1],2,FALSE)"