Hi Experts -
I am reposting this question as this question did not get any responses from the experts. The moderator requested to delete the question and repost it.
The question is simple - I want to automate the vlookup function using VBA.
But the code should have option to specify the lookup value, range and relative column reference by the user through inputbox.
The code can be similar to the one given below taken from this link.
But, if data is very large normal vlookup will be very slow and so I want to use the double vlookup method as given in the below link. For this I need the following 2 adjustments to the below macro -
1 - Need to add Original order column and fill it with continuous numbers equal to number of items excluding header. This is required if we need to go back to the original order in which the data was given.
2 - Ask user if data is sorted and if the answer is no ask whether to sort it on the lookupvalue column. This is required as the double vlookup will only work on sorted columns.
3 - I also need vlookup error trapping like #N/A etc. in the code.
Dim subject As Range
Dim sheet As Range
Dim clm As Long
Set subject = Application.InputBox(prompt:="select Subject to be looked up", Type:=8)
Set sheet = Application.InputBox(prompt:="select tab/range", Type:=8)
Set myResults = Application.InputBox("Please select on the spreadsheet the first cell where you want your lookup results to start:", Type:=8)
clm = Application.InputBox(prompt:="Relative Column Reference", Type:=1)
'Change the range to the cell into which you want the formula placed.
'Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = "=VLOOKUP(" & subject.Address(0, 0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",False)"
[b]Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = "=IF" & "(VLOOKUP(" & subject.Address(0, 0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",True)" & "=" & Range(subject.Address(0, 0)).Value & ", " & "VLOOKUP(" & subject.Address(0, 0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",True)"[/b]
I have attached an example sheet so that it will be easier to suggest the solutions.
Double Vlookup Method - https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/
Experts, please write a response for this and don't again make this as a neglected question.
Thanks in advance