Excel VB User Form Using Vlookup?

[Shaka215]

Hello Fellow Programmers! I am having a difficult time trying to get my
user form to search through a spreadsheet and retrieve the information
using the Application.Vlookup code...I have searched numerous other
posts and haven't found one code that worked the way I needed it to.

Private Sub TextBox10_Change()
x = Application.VLookup(TextBox10.Value,
Worksheets("SYSDATA").Range("A2:J30"), 2, False)
TextBox1.Value = x

End Sub

The objective is I have a Record Number listed in TextBox10... I have
information setup in the spreadsheet that I would liked filled in
TextBoxes 1 - 9 based on the value of TextBox10. I have tried to use
the code above to work but it keeps giving me a error message saying
"Could not set the Value property. Type Mismatch"...So my question is
how can I have my 9 other text boxes use the Vlookup code (using the
value from Textbox10 as the search value, to retrieve the information
based on its value?) TextBox10 will always be a number (for now)...I
might need the code to lookup the value of a non-numerical string at
some point but I'll be happy with just having the vlookup code work.
Any help is greatly appreciated!!!

 

[Chip Pearson]

If the VLookup doesn't find a match, it return a Variant containing an error
type variable. Your code will fail on
TextBox1.Value = x
if x contains an error value. You can test x with IsError. E.g.,

If IsError(X) = True Then
MsgBox "Data Not Found"
Else
TextBox1.Value = X
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Shaka215]

Chip,

I appreciate you taking the time to inform me of this...however, your
new addition to my code is displaying a new error message...

"Could not set the Value property. Type mismatch."

If you think you know how to do this then please provide me the code...
the variable of the code are below...

Lookup Value From UserForm1 = Textbox10
-- Textbox10 is a number derived from the record number
Area in spreadsheet where data is = SYSDATA!A2:J30
Column of where the data is = 2

I don't care what the function is called I just want it so when a
end-user changes the value of textbox 10 it will update every other
textbox in the user form. Your help as well as anyone elses is much
appreciated!!!

Thanks alot!

-Todd