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

 

[AllMB]

It Worked!! Awesome. Thank you.

One more question if you have a minute.

I need to do this formula about 18 times in 18 different location on the spreadsheet with the only thing changing is the division name. I know how to put it in the formula in correct cell.
The question is: Do I redo the division range (ThisDivision = Range("A121").Value) before each vlookup or do I just name 18 variables in the beginning? I.e DivisionVTR, DivisionIAD, DivisionCID?


Thank you for your time on help me with this.

 

[GR00007]

If you are going to change the value for C126 to a new location for 18 times, couldn't you also reset the value for ThisDivision 18 times?