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
Subscribe to:
Post Comments (Atom)
Import data from SQL
Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...
-
If you want to add a new menu on mouse right click "Workbook Navigation showing you the list of all open workbooks and worksheets in ea...
-
Macro to Export Range in Json Format Option Explicit Sub export_in_json_format() Dim fs As Object Dim jsonfile Dim rangetoex...
I am very happy
ReplyDeleteBUT WHY??
Delete