Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA vlookup
You might get more attention if you post under the programming group.
" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Link cells in Access to Excel using VLOOKUP | Excel Discussion (Misc queries) | |||
russian excel vlookup | Excel Worksheet Functions | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
VLOOKUP function in Excel 97 | Excel Worksheet Functions |