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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.