Populating a text box in a form based on VLOOKUP function

[bsalonzo]

I want to populate TextBox2 in a form with the value in TextBox1 that uses a VLOOKUP function. I have scoured the net and have come up with this.

Please Login or Register  to view this content.

However, this does not appear to do anything. I type a string that matches a value in the lookup range, and nothing appears to happen. I have just recently acquainted myself with VB and am very green. Please tell me it is something simple I am missing.

Thanks. B

[rylo]

Hi

Worked for me.

One thing I would do would be to add the line
TextBox2.Text = ""

before the on error statement to clear out any existing result.

Make sure that sheet1 actually has the data in the range (as distinct from sheet "Sheet1"). If you have multiple workbooks open, you may be pointing to the wrong workbook depending on your situation, how the form was raised etc.

rylo

[bsalonzo]

Thanks for your reply rylo. I have added that useful bit of code you suggested. However, nothing is happening with my lookup. I have verified that I only have one workbook open and it is potining to the correct sheet and range. The event is supposed to show me the lookup value when there is a match in text box 1 right? It will automatically appear in text box 2?

The only thing I can think of is it is not picking up the range correctly. The sheet name is called Sheet1(Roster). I have the Range A7:J86 defined as Name "Roster". I am stumped.

Tried...

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Nothing happens. Am I missing something?

[Dean England]

bsalonzo,

A month or so ago, I was trying to get a vlookup to work and having many problems. After a considerable search, this was what I found at another site and my subsequent post there.


I really liked the code that Bill posted; as I have been looking for something like this for a while. However, Like Peter, I was getting a

"run-time error '1004' unable to get the VLookup Property of the WorksheetFunction.

when entering data that was not in the data list or by trying to delete data.
Subsequent to that it produced a "fatal" error in excel, so the only way to get it to run again, is to not only close the proceedure; but also close excel and power up again. That was the problem that Peter ran into. I tested this on both 97 and 2003 and it worked well for me.

Looking above this posting for other postings in this vain, I found this in a posting from Will Riley in 2004 regarding WorksheetFunction.VLookup vs. Application.VLookup.

Either will work. If calling an excel Worksheet Function, the advised method is to use WorksheetFunction in your code. The only exception to this is that sometimes using the WorksheetFunction match will give an error; so it's best to omit the WorksheetFunction Bit when using match.

Henceforth; my change to the coding and it seems to be working.

Please Login or Register  to view this content.

This resolved my problem. Maybe it will be of some help to you. Since I'm new to VBA, it drove me up a wall for a while.

See ya,

Dean

[rylo]

Hi

What sort of data are you trying to lookup? If it is numeric, remember that the data from a textbox will be text, and you will have to convert it to value before the VLOOKUP would work.

If that is not it, can you build a small sample file with representative data and put it up for review.


rylo