PDA

View Full Version : Solved: Vlookup From Userform



Emoncada
07-01-2009, 07:16 AM
I would like to see if I can do a vlookup or something similiar from a UF.

I will enter in a textbox (TxtSiteID) and would like to vlookup (Column C)on the Spreadsheet Sheet1 and have it return the data from that row like the following

(Column N) = TxtShipping1
(Column O) = TxtCity
(Column P) = TxtState
(Column Q) = TxtZip

something like that.

any help would be great.

mdmackillop
07-01-2009, 07:23 AM
any help would be great
any workbook would be great

Bob Phillips
07-01-2009, 07:31 AM
Use VLOOKUP in VBA



Range("N2").value = Application.VLookup(TxtSiteId.Text,Range("A:E"),2,False)


and so on

Emoncada
07-01-2009, 08:05 AM
I would need to use the UF Textbox "TxtSiteID" and then find that value in Range ("C:C") then grab the other columns data. I don't think I can use
Range("N2").value = Application.VLookup(TxtSiteId.Text,Range("A:E"),2,False)
wouldn't that just look at the "N2" Cell value?

Bob Phillips
07-01-2009, 08:11 AM
No, N2 is where it drops the value. I had to take a punt due to lack of detail.

Emoncada
07-01-2009, 08:19 AM
I want it to drop the value back on the UserForm is that possible?

Emoncada
07-01-2009, 08:24 AM
Sorry i should of explained a little better I have 2 spreadsheets spreadsheet1 has the userform and blank spreadsheet that will get filled little by little when the user inputs data and updates the userform.

Spreadsheet2 has data with the shipping information.

I need spreadsheet1's userform to lookup the shipping address based on the value the user inputs in "TxtSiteID" on the userform.

Bob Phillips
07-01-2009, 09:46 AM
Sorry, I thought that N was where you wanted it, not where that data was. See if this is better



TextBox1.Text = Application.VLookup(TxtSiteId.Text,Range("M:Q"),2,False)


This assumes that the site id is in column M, and this example gets the Shipping value.

Emoncada
07-01-2009, 09:51 AM
That worked XLD.
Perfect!!!!

Thanks Again.

Emoncada
07-01-2009, 09:52 AM
Ohh one problem. I just noticed the sheet where its looks for the data is not going to be the activeworksheet. How can I make that work? It will be hidden.

Paul_Hossler
07-01-2009, 09:55 AM
Not tested


TextBox1.Text = Application.VLookup(TxtSiteId.Text,Worksheets("HiddenSheetName").Range("M:Q"),2,False)


paul

Emoncada
07-01-2009, 09:58 AM
Is there a way to have a prompt if no match found?

Emoncada
07-01-2009, 10:02 AM
Paul that worked thanks.

All i need now is some type of Error Msg to notify the user no match was found for the vlookup. Where can that be added?

Bob Phillips
07-01-2009, 10:15 AM
UNtested!



On Error Resume Next
myVar = Application.VLookup(TxtSiteId.Text,Worksheets("HiddenSheetName").Range("M:Q"),2,False)
On Error Goto 0

If Not Iserror(MyVar) Then

TextBox1.Text = MyVar
Else

MsgBox "Not Found"
End If

Emoncada
07-01-2009, 10:30 AM
myVar error. Would that be a String?

Emoncada
07-01-2009, 10:32 AM
Ok String fixed it but it doesn't give the message when not found it just stays blank.

Bob Phillips
07-01-2009, 10:46 AM
My errror.

You need a different test


If Not IsEmpty(MyVar) Then


if MyVar is a Variant variable


If Not MyVar = "" Then


if it is a string

Emoncada
07-01-2009, 10:57 AM
That Worked Perfectly.

Thanks.