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.