Use VLOOKUP or similar function in VBA


Posted by Ron on July 27, 2001 7:39 AM

Here's the scenario I'm trying to create:
I'd like the user to choose a name from a predefined listbox on my form.
Then the user will fill in a series of textboxes.
I'd like to take that information and place it on a spreadsheet in a specific row based on the name chosen. I think I'm looking for the ability to either use VLOOKUP or something similar to accomplish this. Any ideas? Thanks!



Posted by Barrie Davidson on July 27, 2001 8:04 AM

Ron, if you're looking to paste the information in a row where the name is found try using:
Cells.Find(What:=namechosen,After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,SearchOrder:=xlByRows, SearchDirection:=xlNext,MatchCase:=False).Activate
ActiveCell.Offset(0, n).Select
ActiveSheet.Paste

Where namechosen is a variable carrying the value of the name chosen and n (in the offset parameter) is the number of columns away from the name found.

Hope this helps you out.

Barrie