Link to home
Start Free TrialLog in
Avatar of mcrmg
mcrmg

asked on

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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
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
Good spot about the one column range - need more coffee!
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
Range("C4") is not necessarily the same as Activesheet.Range("C4") - it depends where the code is.
Avatar of mcrmg
mcrmg

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Just finished the first pot... :)
Avatar of mcrmg

ASKER

I am getting Subscript out of range error, any ideas?  thanks
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?
@rorya, thanks for not saying that there is never a good time for me to write VBA :)
Avatar of mcrmg

ASKER

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
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'?
Avatar of mcrmg

ASKER

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
And you get error 9 with that?
Can you post a copy of the workbook (you can remove all the data)?
Avatar of mcrmg

ASKER

yes, error 9
Really need to see the workbook then, I think. Don't need the data but the code should be in place.
Avatar of mcrmg

ASKER

is this okay?  thanks
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.
Avatar of mcrmg

ASKER

it is getting error 1004
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.
Avatar of mcrmg

ASKER

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mcrmg

ASKER

That's it..thank you very much.