Excel VBA Code for VLOOKUP

Last modified date

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.