Solved

vlookup in vba

Posted on 2011-02-15
26
655 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
ID: 34895149
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:Ingeborg Hawighorst
ID: 34895156
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
ID: 34895172
Good spot about the one column range - need more coffee!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 34895247
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
ID: 34895380
Range("C4") is not necessarily the same as Activesheet.Range("C4") - it depends where the code is.
0
 

Author Comment

by:mcrmg
ID: 34895386
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
ID: 34895434
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:Ingeborg Hawighorst
ID: 34895441
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
ID: 34895464
Just finished the first pot... :)
0
 

Author Comment

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

Expert Comment

by:Rory Archibald
ID: 34895494
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:Ingeborg Hawighorst
ID: 34895510
@rorya, thanks for not saying that there is never a good time for me to write VBA :)
0
 

Author Comment

by:mcrmg
ID: 34895531
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34895720
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
ID: 34895742
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
ID: 34895749
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
ID: 34895768
yes, error 9
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34895793
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
ID: 34895847
is this okay?  thanks
0
 

Author Comment

by:mcrmg
ID: 34895850
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34895870
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
ID: 34895942
it is getting error 1004
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34895957
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
ID: 34896057
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
ID: 34896075
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
ID: 34896090
That's it..thank you very much.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

756 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