Vlookup Using VBA

If you want to apply vlookup in vba Excel. Snpshot below-

Here is the code
'------------------------------------------------------------
Sub vlookup_method1()
Range("h2").Formula = "=VLOOKUP(G2,A:B,2,0)"
Range("h2:h" & Range("g65356").End(xlUp).Row).FillDown
' use paste specialas value
Range("h2:h" & Range("g65356").End(xlUp).Row).Copy
Range("h2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

'-------------------------------------------------------------------------------------

Sub vlookup_method2()
Range("i2").Formula = "=VLOOKUP(G2,A:B,2,0)"
Range("i2").AutoFill Destination:=Range("i2:i" & Range("g65356").End(xlUp).Row)
' use paste specialas value
Range("i2:i" & Range("g65356").End(xlUp).Row).Copy
Range("i2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

'---------------------------------------------------------------------------------
Sub vlookup_method3()
Dim i As Long
For i = 2 To Range("g65356").End(xlUp).Row
Range("j" & i).Formula = "=VLOOKUP(g" & i & ",A:B,2,0)"
Next i
' use paste specialas value
Range("j2:j" & Range("g65356").End(xlUp).Row).Copy
Range("j2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

'---------------------------------------------------------------------------------------

Sub vlookup_method4()
' if you want to get value using vlookup only for blank cells

Dim i As Long
For i = 2 To Range("g65356").End(xlUp).Row

If Range("k" & i).Value = vbNullString Then
Range("k" & i).Formula = "=VLOOKUP(g" & i & ",A:B,2,0)"

Range("k" & i).Value = Range("k" & i).Value


End If

Next i
End Sub


Download Excel Macro File