Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

vlookup in vba

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
mcrmg
Asked:
mcrmg
  • 12
  • 10
  • 3
  • +1
2 Solutions
 
Rory ArchibaldCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Rory ArchibaldCommented:
Good spot about the one column range - need more coffee!
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
dlmilleCommented:
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
 
Rory ArchibaldCommented:
Range("C4") is not necessarily the same as Activesheet.Range("C4") - it depends where the code is.
0
 
mcrmgAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Rory ArchibaldCommented:
Just finished the first pot... :)
0
 
mcrmgAuthor Commented:
I am getting Subscript out of range error, any ideas?  thanks
0
 
Rory ArchibaldCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@rorya, thanks for not saying that there is never a good time for me to write VBA :)
0
 
mcrmgAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
mcrmgAuthor Commented:
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
 
Rory ArchibaldCommented:
And you get error 9 with that?
Can you post a copy of the workbook (you can remove all the data)?
0
 
mcrmgAuthor Commented:
yes, error 9
0
 
Rory ArchibaldCommented:
Really need to see the workbook then, I think. Don't need the data but the code should be in place.
0
 
mcrmgAuthor Commented:
is this okay?  thanks
0
 
mcrmgAuthor Commented:
0
 
Rory ArchibaldCommented:
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
 
mcrmgAuthor Commented:
it is getting error 1004
0
 
Rory ArchibaldCommented:
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
 
mcrmgAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
mcrmgAuthor Commented:
That's it..thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 12
  • 10
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now