Excel VBA Code for VLOOKUP
Click here to download the workbook which contains the below code
Copy below code and edit below VBA code as per your requirement
Sub Excel_VLOOKUP_code()
'www.comexcelhub.com
'This code is used to write VLOOKUP formula in a fixed range
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets("Sheet1")
S_LR = sht.Cells(Rows.Count, "D").End(xlUp).Row
D_LR = sht.Cells(Rows.Count, "H").End(xlUp).Row
sht.Range("I7:I" & D_LR) = "=VLOOKUP(RC[-1],R6C2:R" & S_LR & "C4,3,FALSE)"
MsgBox "VLOOKUP Formula updated Successfully", , "Confirmation"
End Sub
Explanation of code lines
Code Line1 Dim sht As Worksheet
In Line1: “Sht” Variable declaration as worksheet, this is to shorten the code and also can be used in different places code will be lengthy if we use {thisworkbook.sheets(“Sheet1”)}.
Code Line2 Set sht = Thisworkbook.Sheets("Sheet1")
In Line2 we are assigning value “Thisworkbook.sheets(“Sheet1″)” to variable “sht” created in Line1.
Code Line3 S_LR = sht.Cells(Rows.Count, "D").End(xlUp).Row
In Line3 we are calculating the Source column Last Row to update the VLOOKUP Formula.
Code Line4 D_LR = sht.Cells(Rows.Count, "H").End(xlUp).Row
In Line4 we are calculating the destination Last Row to update the VLOOKUP Formula.
Code Line5 sht.Range("I7:I" & D_LR) = "=VLOOKUP(RC[-1],R6C2:R" & S_LR & "C4,3,FALSE)"
In Line5 we are updating the VLOOKUP formula in the destination range.
Code Line6 MsgBox "VLOOKUP Formula updated Successfully", , "Confirmation"
In Line6 this is a simple message box to confirm that formula is updated.
Note: Line6 is optional code line we can remove if not required.
