Find Help

[aintlifegrand79]

I have a userform (ufRepInfo) in which there is 28 text/combo/check boxes and
1 command button (cbFind). What I have done is made it so that when I put a
value in the textbox (tbZipCode) and hit the Find button (cbFind) it will
return the rep information stored on Sheet1 into the other 27
text/combo/check boxes. This is my code for 1 of the textboxes in which a
value is returned:

Private Sub cbFind_Click()
tbRepNumber.Value = Sheet1.Columns(1).Find(tbZipCode.Value).Offset(0,
1).Value
End Sub

My code works as is as long as I don't use any formating for the cells on
Sheet1. How can I format column 1 on Sheet1 to be a zip code and make my
code work. Thanks for your help

 

[AndrewArmstrong]

You can use the Format Cells - Number - Special - Zip Code format

 

[aintlifegrand79]

Thank you but I understand how to format a cell as a zip code but the problem
is when I do that my code doesn't work any more. What am i missing in my
code or in the formatting of the textbox (tbZipCode) that won't allow it to
return the values when the cell is formated as a Zip Code.

 

[Joel]

Textboxes are Strings while your spreadsheet has numbers.. The solution is to
convert the strings to numbers with the VAL() function.

Private Sub cbFind_Click()
tbRepNumber.Value =
Sheet1.Columns(1).Find(val(tbZipCode.Value)).Offset(0,
1).Value
End Sub

 

[JLGWhiz]

It looks like you might have a number vs string problem. You could try:

Private Sub cbFind_Click()
tbRepNumber.Value = Sheet1.Columns(1).Find(CStr(tbZipCode.Value)) _
.Offset(0, 1).Value
End Sub

Or, it is the other way around try:

Private Sub cbFind_Click()
tbRepNumber.Value = Sheet1.Columns(1).Find(CLng(tbZipCode.Value)) _
.Offset(0, 1).Value
End Sub

If it is the string vs number thing, one of them should work.