Vlookup in VBA?

[privxls]

Hi everyone,

First, I'd like to thank everyone who previously helped me and now I'm back to ask for more of the expert's suggestions and help.

I am currently working on a mini project where I aim to use Buttons, Textboxes in VBA to do a 'vlookup', that if... it's possible.

So here's how it looks like:

Here's where the reference would be from:

Is there a way for me to do it?

Thank you everyone!
-Privxls

 

[PhilS2520]

Hi Privxls,

I do not understand your question fully, but I will answer you based on what I understand.

A VLookup works as follows: Look at the sorted list in location, and then find within the first column the Look-Up Value and from that same row where that Look-Up Value is located give me the value in a certain Column.

Since you have the buttons named, you can get the name of the button, or the caption of the button, pressed, and then look for that value in your Reference area. and return Column 2.

I guess that might be what you need to do, if you need a code example, I am not at my computer right this minute, but I will be happy to send you a sample later.

 

[privxls]

I guess I did not make it clear. I just re-read the thread and it did not make much sense, sorry.

But yes, that's how I'd want it to go Phils. I tried doing "Application.WorksheetFunction.Vlookup" -- yet I encountered compile error.

I'd really appreciate it if you can share it with me.

Thank you PhilS2520

Kind regards,
Privxls.

 

[Norie]

Privxls

What exactly should the buttons on the userform do?

 

[privxls]

Hi Norie,

The buttons should show the corresponding text in the reference area. For an example, the user clicks on button 'Smith 1' it will then show in Text Box 1,2 and 3 the corresponding values in it, same goes for the rest of the buttons. It's like doing a vlookup.
Could there be a better way of doing it than what I currently think of?

Kind regards,
Privxls.

(edit - punctuation)

 

[Norie]

Privxls

VLOOKUP would only return the first instance where there was a match, e.g. for 'Smith 2' it would return 'On the docks'.

If you wanted to return all the instances that matched you would need to take another approach.

 

[privxls]

Ohh... do you have any suggestions Norie?

Kind regards,
Privxls.

 

[offthelip]

You can do that quite easily using variant arrays instead of vlookup, I never use vlookup in VBa because simple coding using variant arrays runs faster and is easier to debug and much more flexible as in this case where you want to return every match. ( to do this just leave out the EXIT for)

Have a look at this thread for a VBA altenative to Vlookup:
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html