PDA

View Full Version : Return Value into userform.



Rlb53
02-12-2012, 10:47 PM
Missing something simple here, I know...

Trying to return the value of a cell which is 2 columns right of the cell in Worksheet "Data 2" that contains the same value that is in TextboxQuoteNumber.

TextBoxSalesman.Value = Sheets("Data 2").Find(TextboxQuoteNumber.Value).Offset(0, 2).Value

mikerickson
02-13-2012, 12:31 AM
Try
TextBoxSalesman.Value = CStr(Sheets("Data 2").Cells.Find(TextboxQuoteNumber.Value).Offset(0, 2).Value)You may have to convert the TextBoxQuoteNumber.Value if the cells contain other than text.

Rlb53
02-13-2012, 06:16 AM
Thank you Mike.
I'm still getting an error message. "Object variable or With block variable not set". I set it up With (and without)... but.. continuing to beat myself up.

With Sheets("Data 2")
TextboxQuoteNumber.Value = QuoteNo.TextBox_QuoteNo.Value
TextBoxSalesman.Value = CStr(Sheets("Data 2").cells.Find(TextboxQuoteNumber.Value).Offset(0, 2).Value)
End With Thanks for any additional recommendations !

Jan Karel Pieterse
02-13-2012, 06:48 AM
The error means the search string wasn't found. I advise you to also use the other agruments of the Find method to prevent it using the wrong settings.

Kenneth Hobs
02-13-2012, 06:58 AM
That means that the activeworkbook has no sheet named "Data 2".

You will also get an error if there is no cell found.
Dim f As Range
With Sheets("Data 2")
TextboxQuoteNumber.Value = QuoteNo.TextBox_QuoteNo.Value
Set f = Sheets("Data 2").Cells.Find(TextboxQuoteNumber.Value)
If f Is Nothing Then Exit Sub
TextBoxSalesman.Value = CStr(f.Offset(0, 2).Value)
End With