Find Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aintlifegrand79 - 24 May 2008 03:13 GMT

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

Reply to this Message

AndrewArmstrong - 24 May 2008 03:25 GMT

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

Reply to this Message

aintlifegrand79 - 24 May 2008 03:49 GMT

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.

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

Reply to this Message

Joel - 24 May 2008 04:13 GMT

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

> 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.
>
> > You can use the Format Cells - Number - Special - Zip Code format

Reply to this Message

JLGWhiz - 24 May 2008 04:14 GMT

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.

> 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
[quoted text clipped - 11 lines]
> Sheet1. How can I format column 1 on Sheet1 to be a zip code and make my
> code work. Thanks for your help

Reply to this Message