# Excel VBA Code for VLOOKUP

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.