Using a VLookup in VBA to retrieve company name?

0
votes

I'm currently designing a macro that auto-populates a table in Excel from a drop-down list. Basically, in one sheet, there is a table of 200 companies. This has a field named Manager (for people looking after the company) which contains a drop-down list with all 12 possible managers' names. When I select a manager, it populates the company name to their section of the table.

Only problem is that I'm trying to use a VLookup in VBA to retrieve the company name. So for example, if I select a manager from row 123, I want to assign the company name in column 'C' to the companyName variable (which then gets inserted into the auto-populating table.

Here is my VLookup code so far:

companyName = Application.WorksheetFunction.VLookup(ActiveCell.Address, Sheets("Companies").Range("C8:K208"), 1, False)

I've seen a number of different ways to do VLookups and generally I find them quite confusing. Whenever it tries to do the lookup it just says "Run-time error 1004: Unable to get the VLookup property of the WorksheetFunction class".

Any help on what's going wrong here would be much appreciated. It's been about 6 years since I last looked at Visual Basic in any sort, and I haven't been working with Excel macros that long - so sorry if I'm missing something blindly obvious!

2
excelvbavlookup
have you tried putting ActiveCell.value instead of ActiveCell.Address ? – Wouter
Yep, sadly that doesn't work either :( – Sam Anderson
ActiveCell approach is never recommended.. it fraught with danger. Can you share either the full code or relevant code? I suspect that ActiveCell.Address might either have an invalid value or empty – Zac
what column holds the managers' names and what column holds the company names? – user10735198

2 Answers

0
votes

From VBA: Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Referring to the reference part which you are obviously trying to use: in VBA a reference is ActiveCell, .range("A5") or .cells(1, 2) NOT the Address like in Excel A1 or B7 etc.

companyName = Application.WorksheetFunction.VLookup(ActiveCell, _
Sheets("Companies").Range("C8:K208"), 1, False)

or (at least in my 2003 version)

companyName = Application.VLookup(ActiveCell, _
Sheets("Companies").Range("C8:K208"), 1, False)

But I still wouldn't play with ActiveCell.

-1
votes

My advice is to not use VLookup in VBA. It is slow, though if your table is only ~200 entries large it should be manageable. What I would do is to read the table into a dictionary keyed by the manager name with values of the company name.

There is a VBA-less alternative: change the format of your table from |Company|Manager| |:-|:-| |Company1|Manager1| Etc. To |Company|Manager|Company| |:-|:-|:-| |Company1|Manager1|Company1| Etc. Now you can just do a cell formula for the VLookup