Solved

vlookup in vba

Posted on 2011-02-15
26
621 Views
Last Modified: 2012-05-11
Hi,

I am try to do the vlookup from sheet1 to sheet "lookup", this is what I do, does not work, please hlpe.

Sheet1.Cells(nRow, 4) = WorksheetFunction.VLookup(ActiveSheet.Range("$c4"), Worksheets("lookup").Range("$e", 2, 0)

thanks
0
Comment
Question by:mcrmg
  • 12
  • 10
  • 3
  • +1
26 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
That should be:
Sheet1.Cells(nRow, 4) = WorksheetFunction.VLookup(ActiveSheet.Range("c4"), Worksheets("lookup").Range("e:e"), 2, 0)

Open in new window


Note that you will get a run time error if the lookup value is not found.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello,

maybe like

Sheet1.Cells(nRow, 4) = WorksheetFunction.VLookup(ActiveSheet.Range("c4"), Worksheets("lookup").Range("E:F"), 2, 0)

If your VLookup returns the value from the second column, (the 2 in your formula), then the lookup range must at least be two columns wide. The code is also short of one closing bracket for the last Range statement. I'm just guessing at the range E:F. If you can tell us in words what you want to put in, or tell us the Vlookup formula you would use in a cell, then this would be easier.

cheers, teylyn
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Good spot about the one column range - need more coffee!
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
NOT for points....

or simplified a bit.  since Range("C4") is the same as ActiveSheet.Range("C4")?

sheet1.cells(nRow,4) = WorksheetFunction.VLookup(Range("C4"), Range("Lookup!E:F"), 2, 0)

Why are there 49 different ways to say the same thing?  And I thought English was confusing!

Dave
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Range("C4") is not necessarily the same as Activesheet.Range("C4") - it depends where the code is.
0
 

Author Comment

by:mcrmg
Comment Utility
this is what I use
Sheet1.Cells(nRow, 4) = WorksheetFunction.VLookup(Range("C4"), Range("Lookup!E:E"), 1, 0)

I think I need to clarify what I need to do first, (sorry)
After inserting a number into Sheet1!C, I want to use this value to do the lookup in lookup!E, once is spotted, it will grab the value from lookup!D into sheet1!D

thank you
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 400 total points
Comment Utility
You can't use Vlookup to return a value to the left of the lookup value. Assuming you want to lookup the value from the same row in col C:
Dim varMatch
varMatch = Application.Match(sheet1.cells(nRow, "C").Value, Worksheets("Lookup").Range("E:E"),0)
if not iserror(varmatch) then
   Sheet1.Cells(nRow, 4).value = Worksheets("Lookup").Cells(varMatch, "D").Value
End If

Open in new window

0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello,

Vlookup will only return values to the right of the first column. A Vlookup with only one column in the lookup table is pretty much useless, since it will always be the same as the lookup value.

If I understand you correctly, the lookup value is in column E and you want to return the corresponding value in column D for the same row.

In a worksheet cell, you'd use Index/Match instead of Vlookup in such a case.

Rory, have you had your coffee yet? Near midnight is not a good time for me to write VBA.

cheers, teylyn
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Just finished the first pot... :)
0
 

Author Comment

by:mcrmg
Comment Utility
I am getting Subscript out of range error, any ideas?  thanks
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Either your sheet is not called Lookup, or nRow has an invalid value I guess. Which line causes the error and what is the value of nRow at the time?
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
@rorya, thanks for not saying that there is never a good time for me to write VBA :)
0
 

Author Comment

by:mcrmg
Comment Utility
The error shows on both lines, I verified that nrow has value, in this case, it is 4.

and I have a sheet called Lookup in the same workbook.  

I tried this too, and getting the same error

varMatch = Application.Match("12345", Worksheets("Lookup").Range("E:E"), 0)

thanks
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
If you get the same error on that line, then your sheet is not called 'Lookup' - are you sure there is no leading or trailing space in the name - i.e. it's not 'Lookup ' or ' Lookup'?
0
 

Author Comment

by:mcrmg
Comment Utility
yes, I have made sure the name is correct.  What is strange is I use it on the same sheet, it is the same error

varMatch = Application.WorksheetFunction.Match("12345", Worksheets("Sheet1").Range("E1:E10"), 0)

thanks
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
And you get error 9 with that?
Can you post a copy of the workbook (you can remove all the data)?
0
 

Author Comment

by:mcrmg
Comment Utility
yes, error 9
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Really need to see the workbook then, I think. Don't need the data but the code should be in place.
0
 

Author Comment

by:mcrmg
Comment Utility
is this okay?  thanks
0
 

Author Comment

by:mcrmg
Comment Utility
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I don't get an error 9 with that. The only message that comes up is there because you have a Msgbox statement to display it.
0
 

Author Comment

by:mcrmg
Comment Utility
it is getting error 1004
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Yes, because you told it to display the error message generated by the Worksheet.Function.Match (which makes it pointless to put the On Error Resume Next in front of it).
The Application.Match that I suggested does not cause a runtime error if the value is not found (it returns an error value).
In the example you posted the reason there is no match is that you are searching for "123" as text, you should search for 123 instead.
0
 

Author Comment

by:mcrmg
Comment Utility
how stupid I am...I have been looping through the files in a folder, there is no "lookup" tab, it is fixed.  

I have encountered another issue, please let me know if I need to open another question.

The lookup value has little "green" triangle to the cell, (I need to keep it there, so I can later on dump it into db)

with that triangle, the search has no match, how can I solve the problem?  Once it has a match, I need to assign chr(39) to it and make sure the new value has triangle as well..thanks
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 400 total points
Comment Utility
If it's a number stored as text, then try using CDbl:
varMatch = Application.Match(CDbl(sheet1.cells(nRow, "C").Value), Worksheets("Lookup").Range("E:E"),0)

Open in new window


or whatever your sheet names are.
0
 

Author Comment

by:mcrmg
Comment Utility
That's it..thank you very much.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now