VLOOKUP run-time error

[isabella]

Hello guys!

I've tried to use VLOOKUP function in order to add Department for each Item that I have in my workbook. But, the message box keep prompting "Run-time error '1004': Unable to get Vlookup property in the Worksheet function class" when I run this code:

Please Login or Register  to view this content.

I just can't detect where the mistake that I have done with this vlookup formula. Can anyone give some help?

Thanks.

[protonLeah]

1. You are parsing through cells in column A

MyCell.Offset(0, -1).Value is trying to put a value in the column to the left of column A which does not exist

2. Column A is empty (at least in the sheet you provided), therefore,

Val(Mid(MyCell, 2, 1)) fails

3. Notice that when your IF condition is TRUE, i.e., the cell is blank, then you try to use the above function to get the value of the middle of an empty cell.

4. Sheets(2) should be written Sheets("department") to avoid confusion. You notice in VBA project explorer that you have Sheet1(SVC) & Sheet5(Department) so that it's not clear which is referred to by Sheets(2)

[isabella]

Thanks protonLeah!

I have change my code to this:

Please Login or Register  to view this content.

It works for my first vlookup condition, not for the second one.
How can I modify that code to achieve the second vlookup condition? (For 4 item from bottom.)

[protonLeah]

You have two statements in your IF condition:

MyCell.Offset(0, -2).Value = WorksheetFunction.VLookup(Val(Mid(MyCell, 2, 1)), Sheets("Department").Range("ONERNG"), 2, False)
MyCell.Offset(0, -2).Value = WorksheetFunction.VLookup(Val(Mid(MyCell, 2, 2)), Sheets("Department").Range("TWORNG"), 2, False)

Both statements are writing to the same cell so that only the results of the last statement are preserved since it overwrites whatever is written by the first statement.
What are you trying to do with those two statements?

[isabella]

I see.

Actually, I want to add department name on Department Name column. What I want to do is like this:

Please Login or Register  to view this content.

For better understanding, you can refer the attachment on "Department" sheet.

Your help are most welcome.
Thanks!

[snb]

Why don't you use formulae in the first place ?

A6: =Vlookup(Mid(C6,2,1),onerng,2,false)

or in VBA

Please Login or Register  to view this content.

[isabella]

Thanks snb.

I already editted your code to this:

Please Login or Register  to view this content.

But, the message box keep prompting "Object variable or With block variable not set".
Am I missing something?

[snb]

do not edit...

Please Login or Register  to view this content.

[isabella]

Thanks, but it still did not work.
Can you attach the workbook that you have tested?

Thank you very much!

[protonLeah]

You need to look at the second character of the invoice and test for your conditions:
Condition 1: the character is 1 or 5 or 6 therefore lookup a name from the first table
Condition 2: the second character is a 3 therefore lookup the name from the second table using the second and third characters of the invoice

Please Login or Register  to view this content.

[isabella]

Thank you very much protonLeah.You have reveal the logic to me.

Thanks again!

[snb]

or

Please Login or Register  to view this content.