Vlookup in VBA errors

AllMB

New Member
Joined
Mar 16, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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
Code:
    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

New Member
Joined
Mar 16, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Code:
    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
 
Last edited:

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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

New Member
Joined
Mar 16, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Apr 22, 2015
Messages
184
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

New Member
Joined
Mar 16, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

'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

Board Regular
Joined
Apr 22, 2015
Messages
184
'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"
Code:
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.
Code:
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
 
Last edited:

AllMB

New Member
Joined
Mar 16, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Apr 22, 2015
Messages
184
I apologize, the last line should have FormulaR1C1 format throughout:
Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'" & ThisFile & "Position Control'!C[-2]:C[-1],2,FALSE)"
 

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,477
Members
409,702
Latest member
thmoriarty

This Week's Hot Topics

Top