Excel vba vLookup

Pit.Sweat

Pit.Sweat

Full Member
Anyone out there able and willing to provide some assistance with adding VLookup in VBA for an Excel spreadsheet?

What I'm doing should be fairly simple and I imagine is really simple but Googling for a solution has left me... well, sick of Googling.

What I want to do is make use of 3 columns and a hidden sheet. The principle is simply that when a valid entry is recorded in column A (using validation) I want to use vba to do vlookup against a hidden sheet and return a value into column B.
If column A is left blank then a manual entry can be made in column B and C - hence me not simply filling column B with vlookup formulas.

I have a spreadsheet that has the following setup:
- a hidden sheet named Buyers containing a list of buyers registration numbers in column A and the company/owner name in column B
- a front sheet that is set up as a form where a seller would complete details up to and including row 23. Row 24 onwards hold Sales lines, the first 3 cells of each row being where I want to invoke the vba code.

If anyone has any idea what I'm blathering on about then please drop me a pm or a reply. I'll probably blunder through it myself eventually but I'm starting to get hassled now to finish this monstrosity.


Cheers.

Pit.
 
C

Cuthrose

f00l Mebmer
if you using VBA you can bluid the Vlookup into the code, is should be stright forward, you are writing a macro are't you? if you want send me the sheet i will write the macro for you.
 
Pit.Sweat

Pit.Sweat

Full Member
Cheers Cuth. I'll dig out the spreadsheet and fire it over. I think part of my problem is that it's something I've inherited from an external supplier and a they've been less than helpful or tidy in what they've produced.
 
Althorn

Althorn

Full Member
(sorry late to the party)

Problem is, I don't think you can do it exactly like you described.
Closest I can think of, is have
A as "reference"
B as "custom details part 1"
C as "custom details part 2"
D as a vlookup using A, part 1 (hidden)
E as vlookup part 2 (also hidden)
F using isna(D) then use B, else D
G using isna(E) then use c, else E

on the vlookup, you need to set the option of "exact match" to force the #NA to appear when it doesn't match.

- A
 
Pit.Sweat

Pit.Sweat

Full Member
Cheers Althorn, I've passed the spreadsheet Cuth's way so I'm hopeful it will be easier to figure out with the actual thing rather than my rambling :)

I've an additional problem with Excel macros not working correctly on a mac that is also doing my head in... compounded by the act that I don't have a mac to play with :(
 
You must log in or register to reply here.
Top