vLookup in VBA (2003 SP1)

Topic

New Reply

The help says I can use some functions from excel in vba. So I’m trying vLookup as I want to grab a value from another sheet. This fails with a “run-time error ‘1004’ Unable to get the vlookup property of the worksheetfunction class.” I noticed in help that I might need to use the range object, thus the second example (which I added the appropriate code around it) that fails.

ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(“A” & iRownum, Sheets(“Emp_No”), 2)
————————————————-
iRowCount = iRownum – 2 ‘Set the number of rows to work with before resetting the counter
iRownum = 2
Do While iRownum <= iRowCount
Range("Q" & iRownum).Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range("A" & iRownum), Sheets("Emp_No"), 2)
Loop

Any ideas?

Reply | Quote

Replies

VLookup has 4 arguments (of which the last is optional):

1. The lookup value – this can be a literal value, or the value of a cell, or an expression.
2. The lookup range – this must be a range on a worksheet, not a worksheet.
3. The column to return the result from.
4. The lookup type – TRUE (the default) for an approximate match, FALSE for an exact match.

So you will have to specify a range as second argument instead of a worksheet.

Reply | Quote

Hmm. I remember that now. It’s been a while since i’ve worked on this and as you already know, my VBA skill level stinks. That’s a bummer. The ’emp_no’ sheet holds a lookup that gets manually updated every time this is run. The data is exported from another system to a text file, manually loaded into this sheet before the vba is run. It would help if I just had the macro do the file loading and range naming, but I don’t konw how to do that in VBA yet! I guess I can manually create a named range (incl a few extra rows just in case) and test and see if a range stays named as data is replaced. Thanks again!

Reply | Quote

If you always import into Emp_No, starting at A1, you can use Worksheets(“Emp_No”).Range(“A1”).CurrentRegion as range (2nd argument).

You can try recording a macro of importing the text file, it’ll give you an idea of the code needed.

Reply | Quote

Thanks, so that worked perfectly. Now I have another lookup that’s giving me problems. I’m trying this and I get that 1004 error again.;I THINK it’s because vLookup wants the lookup table key to be in column 1 and in this case it’s not. The field I’m using to grab the data is, as you can see. The lookup table has a few columns before the one that’s matching the source. It’s in column C. The value to return to the main worksheet is 4 columns past that in column G. Do I need to use something other than vLookup?

Worksheets(“SALARY”).Activate
Range(“R” & iRownum).Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range(“A” & iRownum), Worksheets(“SUPV”).Range(“C1”).CurrentRegion, 4)
iRownum = iRownum + 1

Reply | Quote

There are several possibilities. For example, assuming that column C has no gaps:

… Application.WorksheetFunction.VLookup(Range(“A” & iRownum), Worksheets(“SUPV”).Range(Worksheets(“SUPV”).Range(“C1”), Worksheets(“SUPV”).Range(“C1”).End(xlDown).Offset(0, 4)), 4)

Reply | Quote

Nice. Thanks again and again. Heck, I even understand what this does! However I would not have been able to come up with it without you.

Reply | Quote

Hans. I’m back to this program because something stopped? working (probably never did ). The purpose of the code is to grab a supervisor number from the SUPV sheet and add it to the correct Employee on the SALARY sheet. The result is missing some supervisor numbers and I can’t figure out why. There ARE some supervisor numbers on the SUPV sheet that are zero. It’s missing some right after those, but no complete pattern yet. I’m working to find a pattern but nothing yet. Here’s my code in case you or anyone can see a blatent problem.
—————
‘—————————————-ADD Supv_No to source sheet———————————
vRawNum = “OK”
iRowCount = iRownum – 1 ‘ Set the number of rows to work with before resetting the counter
iRownum = 2 ‘ Use iRowCount from above for loop counter
Do While iRownum <= iRowCount
Worksheets("SUPV").Activate
' Format column C/"Code" to number w/o P
Range("C" & iRownum).Select
vRawNum = Right(Range("C" & iRownum).Value, 8)
Selection.NumberFormat = "0"
ActiveCell.FormulaR1C1 = vRawNum

' Format column G/"Personnel number of superior (Org. Manag" to number
Range("G" & iRownum).Select
Selection.NumberFormat = "0"
ActiveCell.Value = ActiveCell.Value

' Do a lookup using Personnel number on the Emp_no sheet and bring back 'G' to last column
Worksheets("SALARY").Activate
Range("R" & iRownum).Select
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range("A" & iRownum), Worksheets("SUPV").Range(Worksheets("SUPV").Range("C1"), Worksheets("SUPV").Range("C1").End(xlDown).Offset(0, 5)), 5)

iRownum = iRownum + 1

Loop

End Sub

Reply | Quote

Without seeing the workbook it’s hard to say. Does it work better if you add False as fourth argument to VLookup?

ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range(“A” & iRownum), Worksheets(“SUPV”).Range(Worksheets(“SUPV”).Range(“C1”), Worksheets(“SUPV”).Range(“C1”).End(xlDown).Offset(0, 5)), 5, False)

Reply | Quote

I found a few things wrong with the latest data they ran through it. For one, not all the sheets were sorted correctly. If I understand your response and my research, False should take care of that. They guaranteed they would always be sorted by emp_no. I should have known . So firstly, I was thinking of adding a sort. Do I not need to with False? I just read the help and I DO only want an exact match.

So if that’s not true… I sorted manually the way I wanted, recording a macro. Got this below. I’d rather just say sort the whole thing by A2 for each of the sheets. Can I tell the first range to do that rather than A1:P501?
Range(“A1:P501”).Sort Key1:=Range(“A2”), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

There is another data problem in that all the source data sheets MUST have the same emloyee list (and sorted the same way as I mentioned). The main sheet doesn’t have one person that was in the other three. Yuck. I don’t have a clue on how to check for matching rows of people… and I think I need to since I clearly can’t trust the source!

Reply | Quote

You can use

Range(“A1”).CurrentRegion.Sort Key1:=Range(“A2”), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

This will sort the table with upper left corner A1 on column A, whatever the size of the table.

See Duplicate And Unique Items In Lists for some ideas on how to compare lists – see the entries near the end.

Reply | Quote

Thanks again! I’d still like to know if adding False as the fourth parameter of vLookup will make this unnecessary. Also, without it, am I in danger of bringing back an incorrect supervisor number?

Reply | Quote

If you add False as 4th parameter to VLookup, it will look for an exact match, and the lookup list won’t need to be sorted. If no match can be found, an error will occur.

If you omit the 4th argument, the list MUST be sorted, and VLookup will stop at the largest value from the list that is smaller than or equal to the value searched for, then returm the value from the appropriate column in that row.

Reply | Quote

Got it. Thanks. So I added False (and am not going to sort the sheets) and the line looks like this.
ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range(“A” & iRownum), Worksheets(“Emp_No”).Range(“A1”).CurrentRegion, 2, False)
The error message I get is “Run-time error ‘1004’: Unable to get the VLookup property of the WorksheetFunction class”
Any ideas?

Reply | Quote

As I said in my previous reply[indent]

---

If no match can be found, an error will occur.

---

[/indent]so you will have to add error handling, or change the line slighty:

ActiveCell.Value = Application.VLookup(Range(“A” & iRownum), Worksheets(“Emp_No”).Range(“A1”).CurrentRegion, 2, False)

Application.VLookup will set the cell to an error value if there is no match, instead of causing an error in the code. (Thanks to Jan Karel Pieterse for this tip)

Reply | Quote

Ah. Missed that it would completely break. Sorry about that. Thanks for the repeat. I like the setting the row to error better anyway and will modify the code for both vLookup’s.

Thanks!

Reply | Quote

Oops. Posted too quickly. What’s the difference between my use of ActiveCell.FormulaR1C1 vs the change you made to ActiveCell.Value?

Reply | Quote

It’s not really important. Assigning a constant value to the Formula or FormulaR1C1 property does work, but since you’re setting the value of the cell, not its formula, I decided to make that explicit.

Reply | Quote

OK. Losing my mind. I’m guessing that as I improve parts with your help I’m breaking other areas. Now some code that has never had a problem breaks with a ‘Type Mismatch’

Public Sub CommitCurr()
‘ Create a backup file of the output in xls format.
Dim sDelete As String
Dim n As Integer
n = 2
Do While Range(“A” & n).Value “”
If Range(“A” & n + 1).Value = Range(“A” & n).Value Then <<< BREAKS HERE
Range("A" & n, "IV" & n).Delete
Else
n = n + 1
End If
Loop
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:tempSAP_Extract_" & Format(Now(), "YYMMDD")
Application.DisplayAlerts = True
End Sub

Reply | Quote

Try using

Range(“A” & (n + 1))

instead of

Range(“A” & n + 1)

Reply | Quote

Same result. Yikes. I’m going nuts. This has to be done by Monday morning. I hate to keep bugging you about this. Heck, I’d even pay you out of my own pocket to help me get it figured out and working correctly. Attached is my entire Module, in case it’d help.

Reply | Quote

Can you post a stripped down copy of the workbook? I think we need to see the workbook to know what causes the error.

Reply | Quote

Temp.zip holds the master workbook with code, the source data book1.xls & emplid.txt which are normally stored in C:temp. I’m in a meeting for the rest of the afternoon/evening so I won’t be able to check back to see if I’ve missed getting you something past 2pm PDT (Pacific US)

Reply | Quote

When I tried it, the worksheets weren’t sorted correctly; when I fixed that, I got the error you reported. It’s caused by #N/A error values that result from VLookup not finding a match. I added a test for this, and now column A remains empty if no match is found. The attached code runs to completion with the sample files you provided.

The code could be made more efficient by using range objects to avoid all that selecting of cells and activating of worksheets.

Reply | Quote

Interesting that you say they weren’t sorted correctly as I thought my code ended up doing that. I know the sort is wrong in book1.xls, which is why I do it in the code.
Everything we were talking about now works. Thank you so much for all your help. I’m continuing to struggle with some formatting issues though. Columns that look like numbers (phone) won’t format as text. They stay numbers whether I try to format manually or with code;
Range(“D” & iRownum).Select
Selection.NumberFormat = “@”
ActiveCell.Value = ActiveCell.Value
Any idea what might cause this? I remember I had a problem with dates and had a friend code something to handle it, but I can’t find the sample. I got the above by creating a macro and doing it manually. But the funny thing is that manually the formatting shows it’s text, but still acts like a number (if I shrink the column size the ‘text’ value changes to scientific notation.)

Reply | Quote

Try this:

With Range("D" & iRownum)
.NumberFormat = "@"
.Value = "'" & .Value
End With

The apostrophe in front of the value tells Excel that it should really be treated as text.

Reply | Quote

Adding False as the last parameter does make it unnessary to sort the lookup table. If I understand what you are doing, leaving False off (or using True) could cause you to retrieve an incorrect Supervisor. If you leave False off or use True, the VLOOKUP will do an approximate match. If you lookup a supervisor number that is not in the table, it will return the largest value in the lookup column that is less than the value you are looking up. If I understand what you are doing correctly, that is NOT what you want.

Reply | Quote