Excel VBA vlookup
Hello
This is my first Excel VBA to publish in the forum and happy to join
the Excel VBA family.
Excel vlookup function is very useful in many applications.
I develop a VBA to make Vlookup run faster if you many rows to lookup
and more user friendly , please try the code.
I appreciate your comment.
Option Explicit
Dim finalrow As Integer
Dim Mysheet As String
Dim mycolumn As Long
Dim Myrange As String
Dim mycount As Long
Dim i As Integer
Dim mylookup As Variant
Dim mystring As String
Dim myvalue As Long
Sub Vlookup()
mycolumn = Application.InputBox("Please enter the lookup column
number", Type:=1)
myvalue = Application.InputBox("Please enter the column number you want
to place your lookup up result ", Type:=1)
Mysheet = InputBox("Please enter lookup worksheet name",
"WorksheetName", Default)
Myrange = InputBox("Please enter lookup range", "Range", Default)
mycount = Range(Myrange).Columns.Count
On Error Resume Next
Columns(myvalue).Insert Shift:=xlToRight
finalrow = Cells(65536, mycolumn).End(xlUp).Row
For i = 1 To finalrow
mystring = (Cells(i, mycolumn))
mylookup = Application.Vlookup((mystring),
Worksheets(Mysheet).Range(Myrange), mycount, False)
Cells(i, myvalue).Value = mylookup
Next i
End Sub
|