Rocutana Rim
asked on
Automate double vlookup using VBA
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.
http://stackoverflow.com/q
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.
I have attached an example sheet so that it will be easier to suggest the solutions.
Double Vlookup Method - https://fastexcel.wordpres
Experts, please write a response for this and don't again make this as a neglected question.
Thanks in advance
VBA_Vlookup_Example.xlsm
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.
http://stackoverflow.com/q
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.
Sub VlookupMacro()
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]
End SubI have attached an example sheet so that it will be easier to suggest the solutions.
Double Vlookup Method - https://fastexcel.wordpres
Experts, please write a response for this and don't again make this as a neglected question.
Thanks in advance
VBA_Vlookup_Example.xlsm
ASKER
@Subodh Tiwari (Neeraj) - Thanks for your response.
I use MS Excel 2007 and use the Vlookup function often as my work is related to data. I will not always work with large data, but as I said earlier I use Vlookup function often. So If I get a generic VBA code to automate this, my work will become more easy.
Kindly suggest some solution for this.
Thanks
I use MS Excel 2007 and use the Vlookup function often as my work is related to data. I will not always work with large data, but as I said earlier I use Vlookup function often. So If I get a generic VBA code to automate this, my work will become more easy.
Kindly suggest some solution for this.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Neeraj & ProfessorJimJam for your replies. Both of the solutions are great and I will select both as solution.
I got one small doubt regarding the above process. I re-read the Charles page, but couldn't get the correct answer for that.
My doubt is that for the above process, should both the columns be sorted or just the column with the lookup value. Kindly clarify.
Thanks
I got one small doubt regarding the above process. I re-read the Charles page, but couldn't get the correct answer for that.
My doubt is that for the above process, should both the columns be sorted or just the column with the lookup value. Kindly clarify.
Thanks
You're welcome.
Only sheet with the lookup table should be sorted.
Only sheet with the lookup table should be sorted.
you are welcome.
ASKER
Thank you both of you for clearing my doubt.
@Neeraj - I request one more update to your code. When I used your code with around 160K, after the sort order input box, my excel hangs or it becomes very slow.
@ProfessorJimJam - Sir, your code also works fine but can you update a Original order as requested in previous comment.
Can't we just put consecutive numbers for both the input and lookup columns in the rightmost blank column and then sort?
Also, Can the code be extended for multiple workbooks i.e selecting input workbook and mapping workbbok. The inputbox works only for the active workbook.
I know that I am asking for more effort and time of yours, but please don't feel bad. I am a newbie in VBA and so exploring various methods for doing things.
I had another question related to this - automate vlookup using dictionary but following the same above mentioned process.
I had asked both questions previously, but got no responses, so I reposted one now and got your responses and if possible kindly look into this and provide a method for doing this.
Thanks Once Again
@Neeraj - I request one more update to your code. When I used your code with around 160K, after the sort order input box, my excel hangs or it becomes very slow.
@ProfessorJimJam - Sir, your code also works fine but can you update a Original order as requested in previous comment.
Can't we just put consecutive numbers for both the input and lookup columns in the rightmost blank column and then sort?
Also, Can the code be extended for multiple workbooks i.e selecting input workbook and mapping workbbok. The inputbox works only for the active workbook.
I know that I am asking for more effort and time of yours, but please don't feel bad. I am a newbie in VBA and so exploring various methods for doing things.
I had another question related to this - automate vlookup using dictionary but following the same above mentioned process.
I had asked both questions previously, but got no responses, so I reposted one now and got your responses and if possible kindly look into this and provide a method for doing this.
Thanks Once Again
The code will take time for that much of data.
Try to add DoEvents line before each line of code which place the formula on the sheet.
Try to add DoEvents line before each line of code which place the formula on the sheet.
@Vijayendra S. Murthy
it is simple. use a helper column next to the data and put 1 and then fill series 1,2,3,4,5,6,6,7,8,9,10 and so on.
then whenever you have run your macro and you want to return to original sort, then simply sort the 1,2,3,4 column back to ascending order and it will move back original state.
it is simple. use a helper column next to the data and put 1 and then fill series 1,2,3,4,5,6,6,7,8,9,10 and so on.
then whenever you have run your macro and you want to return to original sort, then simply sort the 1,2,3,4 column back to ascending order and it will move back original state.
ASKER
Hi Experts - Thanks for your response.
Can you please look into my other questions about extending this to multiple workbooks and using Dictionary to do vlookup.
Thanks once again for all your help.
Can you please look into my other questions about extending this to multiple workbooks and using Dictionary to do vlookup.
Thanks once again for all your help.
If you are dealing with a large data set, I would suggest you go for Power Query as an alternative method for VlookUp.