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

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

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(

thanks

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

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

maybe like

Sheet1.Cells(nRow, 4) = WorksheetFunction.VLookup(

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

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

sheet1.cells(nRow,4) = WorksheetFunction.VLookup(

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

Dave

Sheet1.Cells(nRow, 4) = WorksheetFunction.VLookup(

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

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

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

I tried this too, and getting the same error

varMatch = Application.Match("12345",

thanks

varMatch = Application.WorksheetFunct

thanks

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.

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

All Courses

From novice to tech pro — start learning today.

Open in new window