Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Help
You can use the Format Cells - Number - Special - Zip Code format
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Help
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. "AndrewArmstrong" wrote: You can use the Format Cells - Number - Special - Zip Code format |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Help
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)).Offse t(0, 1).Value End Sub "aintlifegrand79" wrote: 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. "AndrewArmstrong" wrote: You can use the Format Cells - Number - Special - Zip Code format |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Help
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. "aintlifegrand79" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find rows with a common item and find or highlight difference | Excel Programming | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |