| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
clear user form after entry
I have a user form with a command button that users enter data to a table.
How can clear the user form with the same button click that enters the data so someone can continue to the next record? i know this must be simple but it is eluding me. -- Bucky F |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
To answer your question, you will need to supply the following information:
1. What controls are used on the UserForm i.e. listboxes, comboboxes, textboxes? 2. Post the code that is behind the form. i.e. initialize and click event codes. "willfeld" wrote: I have a user form with a command button that users enter data to a table. How can clear the user form with the same button click that enters the data so someone can continue to the next record? i know this must be simple but it is eluding me. -- Bucky F |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
One way is to put the UserForm.Show in a loop like:
Sub UFloopDemo() 'Declarations, etc. 'Some code Do UserForm1.Show again = MsgBox("Show UserForm again?", vbYesNo, "More?") Loop while again = vbYes 'some more code End Sub You could possibly do it without having to unload the UserForm but it is difficult to tell without knowing what is on the form and what the code for the controls is. "willfeld" wrote: I have a user form with a command button that users enter data to a table. How can clear the user form with the same button click that enters the data so someone can continue to the next record? i know this must be simple but it is eluding me. -- Bucky F |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
I have several combo boxes and about 10 text boxes. the code is:
Private Sub fconfirm_Click() Dim NewRow As Integer NewRow = Worksheets("main").Range("B3").Value + 1 If Len(MyForm.fregion.Value) = 0 Then MsgBox "The Region field can not be left Blank!", vbOKOnly, "IPA Training - Input Form" MyForm.fregion.SetFocus Exit Sub End If Worksheets("Table").Cells(NewRow, 1).Value = MyForm.fregion.Value Worksheets("Table").Cells(NewRow, 2).Value = MyForm.ftrainer.Value Worksheets("Table").Cells(NewRow, 3).Value = MyForm.ftrainee.Value Worksheets("Table").Cells(NewRow, 4).Value = MyForm.fstriation.Value Worksheets("Table").Cells(NewRow, 5).Value = MyForm.ftrainingdate.Value Worksheets("Table").Cells(NewRow, 6).Value = MyForm.fappts.Value Worksheets("Table").Cells(NewRow, 7).Value = MyForm.fconfirmedleads.Value Worksheets("Table").Cells(NewRow, 8).Value = MyForm.fcontacts.Value Worksheets("Table").Cells(NewRow, 9).Value = MyForm.fpresentations.Value Worksheets("Table").Cells(NewRow, 10).Value = MyForm.ftalkbc.Value Worksheets("Table").Cells(NewRow, 11).Value = MyForm.ftalkzone.Value Worksheets("Table").Cells(NewRow, 12).Value = MyForm.ffieldsales.Value Worksheets("Table").Cells(NewRow, 13).Value = MyForm.fhybrids.Value Worksheets("Table").Cells(NewRow, 15).Value = MyForm.fapptafter.Value Worksheets("Table").Cells(NewRow, 16).Value = MyForm.fconfirmedafter.Value Worksheets("Table").Cells(NewRow, 17).Value = MyForm.fcontactsafter.Value Worksheets("Table").Cells(NewRow, 18).Value = MyForm.fpresentationsafter.Value Worksheets("Table").Cells(NewRow, 19).Value = MyForm.ftalkbcafter.Value Worksheets("Table").Cells(NewRow, 20).Value = MyForm.ftalkzoneafter.Value Worksheets("Table").Cells(NewRow, 21).Value = MyForm.ffieldsalesafter.Value Worksheets("Table").Cells(NewRow, 22).Value = MyForm.fhybridsafter.Value MyForm.Hide End Sub And the button has the following Sub Button1_Click() Range("B3").Value = Worksheets("Table").Range("a20000").End(xlUp).Row Range("C1").Value = "" Range("d1").Value = "" Range("e1").Value = "" Range("f1").Value = "" Range("G1").Value = "" Range("H1").Value = "" Range("I1").Value = "" Range("J1").Value = "" Range("K1").Value = "" Range("L1").Value = "" Range("M1").Value = "" Range("O1").Value = "" Range("P1").Value = "" Range("Q1").Value = "" Range("R1").Value = "" Range("S1").Value = "" Range("T1").Value = "" MyForm.Show MyForm.fregion.SetFocus End Sub -- Bucky F "JLGWhiz" wrote: To answer your question, you will need to supply the following information: 1. What controls are used on the UserForm i.e. listboxes, comboboxes, textboxes? 2. Post the code that is behind the form. i.e. initialize and click event codes. "willfeld" wrote: I have a user form with a command button that users enter data to a table. How can clear the user form with the same button click that enters the data so someone can continue to the next record? i know this must be simple but it is eluding me. -- Bucky F |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
I have several combo boxes and about 10 text boxes. the code is:
Private Sub fconfirm_Click() Dim NewRow As Integer NewRow = Worksheets("main").Range("B3").Value + 1 If Len(MyForm.fregion.Value) = 0 Then MsgBox "The Region field can not be left Blank!", vbOKOnly, "IPA Training - Input Form" MyForm.fregion.SetFocus Exit Sub End If Worksheets("Table").Cells(NewRow, 1).Value = MyForm.fregion.Value Worksheets("Table").Cells(NewRow, 2).Value = MyForm.ftrainer.Value Worksheets("Table").Cells(NewRow, 3).Value = MyForm.ftrainee.Value Worksheets("Table").Cells(NewRow, 4).Value = MyForm.fstriation.Value Worksheets("Table").Cells(NewRow, 5).Value = MyForm.ftrainingdate.Value Worksheets("Table").Cells(NewRow, 6).Value = MyForm.fappts.Value Worksheets("Table").Cells(NewRow, 7).Value = MyForm.fconfirmedleads.Value Worksheets("Table").Cells(NewRow, 8).Value = MyForm.fcontacts.Value Worksheets("Table").Cells(NewRow, 9).Value = MyForm.fpresentations.Value Worksheets("Table").Cells(NewRow, 10).Value = MyForm.ftalkbc.Value Worksheets("Table").Cells(NewRow, 11).Value = MyForm.ftalkzone.Value Worksheets("Table").Cells(NewRow, 12).Value = MyForm.ffieldsales.Value Worksheets("Table").Cells(NewRow, 13).Value = MyForm.fhybrids.Value Worksheets("Table").Cells(NewRow, 15).Value = MyForm.fapptafter.Value Worksheets("Table").Cells(NewRow, 16).Value = MyForm.fconfirmedafter.Value Worksheets("Table").Cells(NewRow, 17).Value = MyForm.fcontactsafter.Value Worksheets("Table").Cells(NewRow, 18).Value = MyForm.fpresentationsafter.Value Worksheets("Table").Cells(NewRow, 19).Value = MyForm.ftalkbcafter.Value Worksheets("Table").Cells(NewRow, 20).Value = MyForm.ftalkzoneafter.Value Worksheets("Table").Cells(NewRow, 21).Value = MyForm.ffieldsalesafter.Value Worksheets("Table").Cells(NewRow, 22).Value = MyForm.fhybridsafter.Value MyForm.Hide End Sub And the button has the following Sub Button1_Click() Range("B3").Value = Worksheets("Table").Range("a20000").End(xlUp).Row Range("C1").Value = "" Range("d1").Value = "" Range("e1").Value = "" Range("f1").Value = "" Range("G1").Value = "" Range("H1").Value = "" Range("I1").Value = "" Range("J1").Value = "" Range("K1").Value = "" Range("L1").Value = "" Range("M1").Value = "" Range("O1").Value = "" Range("P1").Value = "" Range("Q1").Value = "" Range("R1").Value = "" Range("S1").Value = "" Range("T1").Value = "" MyForm.Show MyForm.fregion.SetFocus End Sub I don't know if you saw this from my last post so i sent it again. -- Bucky F "JLGWhiz" wrote: One way is to put the UserForm.Show in a loop like: Sub UFloopDemo() 'Declarations, etc. 'Some code Do UserForm1.Show again = MsgBox("Show UserForm again?", vbYesNo, "More?") Loop while again = vbYes 'some more code End Sub You could possibly do it without having to unload the UserForm but it is difficult to tell without knowing what is on the form and what the code for the controls is. "willfeld" wrote: I have a user form with a command button that users enter data to a table. How can clear the user form with the same button click that enters the data so someone can continue to the next record? i know this must be simple but it is eluding me. -- Bucky F |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
I have a user form with a command button that users enter data to
a table. How can clear the user form with the same button click that enters the data so someone can continue to the next record? I have several combo boxes and about 10 text boxes. You can use this code to clear the ComboBoxes and TextBoxes... Dim C As Control ...... ...... For Each C In MyForm.Controls If TypeOf C Is MSForms.ComboBox Then C.ListIndex = -1 ElseIf TypeOf C Is MSForms.TextBox Then C.Text = "" End If Next Just put the Dim statement with the other Dim statements and insert this code wherever you need it at with your other code. By the way, you can simplify the code you posted for Button1 as follows... Private Sub Button1_Click() Range("B3").Value = Worksheets("Table").Range("A20000").End(xlUp).Row Range("C1:T1").ClearContents End Sub Although I would put the worksheet references in for these ranges. Rick |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
See inline comments...
I can use this constantly - if I understand it Before you go and use this everywhere, remember that it was designed for your specific case... in effect, find the first digit that is not a zero (notice that the array and listing of digits do not have a zero in them), which is also why I said this formula only applies IF the first character after the leading zeroes is a digit (it will fail to work correctly if a non-digit ever follows the leading zeroes). and Gosh I hate to ask but could you explain how it works - I'll try. I think the Find({1,2,3,4,5,6,7,8,9} is an array function - I believe it uses the &"123456789" constant but I am really confused as to the MIN function and the 99 value and just basically how the formula flows. I guess the best place to start is by giving an overview of what the formula does. It finds the location of the first non-zero digit within the text and uses that to find the starting point for pulling out the text you wanted. It uses the MID function to get pull out a sub-string from the main piece of text. The format of the MID function is... MID(YourText, StartingPoint, NumberOfCharactersAfterStartingPoint) We will describe how to get the StartingPoint (the location of the first non-zero) in a moment; but, once you have it, you wanted that digit along with the remainder of the text after it. We don't know how many characters that will be as the number of leading zeroes can vary; however, there is no problem in asking for more characters than exist, so I took a guess that your text will never be longer than 99 characters (which is why the last value is 99... it is the 3rd argument of the MID function). Okay, so how do we get the StartingPoint. We use ths code... MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")) And how does that work. First off, we note that the FIND function cannot process an array (values contained within curly braces); however, the MIN can and so, embedding an array or a function call with an array (even if that function can't normally handle arrays) within it forces the array to get processed. This happens be feeding each array element into its encasing function (the FIND function in this case) one at a time so that the MIN function can determine which evaluated value is the smallest. So, the MIN function is forcing these evaluations to take place... FIND(1,A1&"123456789") FIND(2,A1&"123456789") FIND(3,A1&"123456789") FIND(4,A1&"123456789") FIND(5,A1&"123456789") FIND(6,A1&"123456789") FIND(7,A1&"123456789") FIND(8,A1&"123456789") FIND(9,A1&"123456789") The returned values from the above are the positions of the digit within the text, so when the MIN function looks at all of these values in order to find the smallest value, that value is the location in the text... that is, the location of the first non-zero digit within the text (which is our starting point for the MID function). Okay, now the concatenated "123456789" is placed there to ensure that FIND will in fact find the digit it is looking for at the moment. Well, that's it. I'm not so sure my explanation is as clear in words as the concepts are in my head, but hopefully there is enough for your to fight your way to an understanding of what is happening. Rick Is there **always** digit after the last leading zero (that is, never something like 000ABC)? If so... =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789 ")),99) I have an item number field which I use a formula to pull the last segment of the item number field ( such as: WBN-2-FCCG-043-00000094 or WBN-2-IVSG-043-0001A-A ) the results would be a text field such as 00000094 or 0001A-A or 0230-B I need to find a way to remove the leading 0's does anyone have any ideas? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
*** Sorry about the incomplete previous posting ***
*** I accidentally sent it before I had finished. *** See inline comments... I can use this constantly - if I understand it Before you go and use this everywhere, remember that it was designed for your specific case... in effect, find the first digit that is not a zero (notice that the array and listing of digits do not have a zero in them), which is also why I said this formula only applies IF the first character after the leading zeroes is a digit (it will fail to work correctly if a non-digit ever follows the leading zeroes). and Gosh I hate to ask but could you explain how it works - I'll try. I think the Find({1,2,3,4,5,6,7,8,9} is an array function - I believe it uses the &"123456789" constant but I am really confused as to the MIN function and the 99 value and just basically how the formula flows. I guess the best place to start is by giving an overview of what the formula does. It finds the location of the first non-zero digit within the text and uses that to find the starting point for pulling out the text you wanted. It uses the MID function to get pull out a sub-string from the main piece of text. The format of the MID function is... MID(YourText, StartingPoint, NumberOfCharactersAfterStartingPoint) We will describe how to get the StartingPoint (the location of the first non-zero) in a moment; but, once you have it, you wanted that digit along with the remainder of the text after it. We don't know how many characters that will be as the number of leading zeroes can vary; however, there is no problem in asking for more characters than exist, so I took a guess that your text will never be longer than 99 characters (which is why the last value is 99... it is the 3rd argument of the MID function). Okay, so how do we get the StartingPoint. We use ths code... MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")) And how does that work. First off, we note that the FIND function cannot process an array (values contained within curly braces); however, the MIN can and so, embedding an array or a function call with an array (even if that function can't normally handle arrays) within it forces the array to get processed. This happens be feeding each array element into its encasing function (the FIND function in this case) one at a time so that the MIN function can determine which evaluated value is the smallest. So, the MIN function is forcing these evaluations to take place... FIND(1,A1&"123456789") FIND(2,A1&"123456789") FIND(3,A1&"123456789") FIND(4,A1&"123456789") FIND(5,A1&"123456789") FIND(6,A1&"123456789") FIND(7,A1&"123456789") FIND(8,A1&"123456789") FIND(9,A1&"123456789") The returned values from the above are the positions of the digit within the text, so when the MIN function looks at all of these values in order to find the smallest value, that value is the location in the text... that is, the location of the first non-zero digit within the text (which is our starting point for the MID function). Okay, now the concatenated "123456789" is placed there to ensure that FIND will in fact find the digit it is looking for at the moment. Well, that's it. I'm not so sure my explanation is as clear in words as the concepts are in my head, but hopefully there is enough for your to fight your way to an understanding of what is happening. Rick Is there **always** digit after the last leading zero (that is, never something like 000ABC)? If so... =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789 ")),99) I have an item number field which I use a formula to pull the last segment of the item number field ( such as: WBN-2-FCCG-043-00000094 or WBN-2-IVSG-043-0001A-A ) the results would be a text field such as 00000094 or 0001A-A or 0230-B I need to find a way to remove the leading 0's does anyone have any ideas? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
No, we don't need this information, Bucky does.
Bucky - You simply need to write code that changes each control to its default value, most likely false for checkboxes and "" for textboxes. Do this right after you place the current values of the controls into the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "JLGWhiz" wrote in message ... To answer your question, you will need to supply the following information: 1. What controls are used on the UserForm i.e. listboxes, comboboxes, textboxes? 2. Post the code that is behind the form. i.e. initialize and click event codes. "willfeld" wrote: I have a user form with a command button that users enter data to a table. How can clear the user form with the same button click that enters the data so someone can continue to the next record? i know this must be simple but it is eluding me. -- Bucky F |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
I don't know if you picked up on Rick's reply or not. You can insert his
code near the end of your code for the fconfirm button like so: For Each C In MyForm.Controls If TypeOf C Is MSForms.ComboBox Then C.ListIndex = -1 ElseIf TypeOf C Is MSForms.TextBox Then C.Text = "" End If Next MyForm.Hide End Sub This should reset all of your combo boxes and text boxes to blank. Then when you show the form again, all controls will require a new entry. "willfeld" wrote: I have several combo boxes and about 10 text boxes. the code is: Private Sub fconfirm_Click() Dim NewRow As Integer NewRow = Worksheets("main").Range("B3").Value + 1 If Len(MyForm.fregion.Value) = 0 Then MsgBox "The Region field can not be left Blank!", vbOKOnly, "IPA Training - Input Form" MyForm.fregion.SetFocus Exit Sub End If Worksheets("Table").Cells(NewRow, 1).Value = MyForm.fregion.Value Worksheets("Table").Cells(NewRow, 2).Value = MyForm.ftrainer.Value Worksheets("Table").Cells(NewRow, 3).Value = MyForm.ftrainee.Value Worksheets("Table").Cells(NewRow, 4).Value = MyForm.fstriation.Value Worksheets("Table").Cells(NewRow, 5).Value = MyForm.ftrainingdate.Value Worksheets("Table").Cells(NewRow, 6).Value = MyForm.fappts.Value Worksheets("Table").Cells(NewRow, 7).Value = MyForm.fconfirmedleads.Value Worksheets("Table").Cells(NewRow, 8).Value = MyForm.fcontacts.Value Worksheets("Table").Cells(NewRow, 9).Value = MyForm.fpresentations.Value Worksheets("Table").Cells(NewRow, 10).Value = MyForm.ftalkbc.Value Worksheets("Table").Cells(NewRow, 11).Value = MyForm.ftalkzone.Value Worksheets("Table").Cells(NewRow, 12).Value = MyForm.ffieldsales.Value Worksheets("Table").Cells(NewRow, 13).Value = MyForm.fhybrids.Value Worksheets("Table").Cells(NewRow, 15).Value = MyForm.fapptafter.Value Worksheets("Table").Cells(NewRow, 16).Value = MyForm.fconfirmedafter.Value Worksheets("Table").Cells(NewRow, 17).Value = MyForm.fcontactsafter.Value Worksheets("Table").Cells(NewRow, 18).Value = MyForm.fpresentationsafter.Value Worksheets("Table").Cells(NewRow, 19).Value = MyForm.ftalkbcafter.Value Worksheets("Table").Cells(NewRow, 20).Value = MyForm.ftalkzoneafter.Value Worksheets("Table").Cells(NewRow, 21).Value = MyForm.ffieldsalesafter.Value Worksheets("Table").Cells(NewRow, 22).Value = MyForm.fhybridsafter.Value MyForm.Hide End Sub And the button has the following Sub Button1_Click() Range("B3").Value = Worksheets("Table").Range("a20000").End(xlUp).Row Range("C1").Value = "" Range("d1").Value = "" Range("e1").Value = "" Range("f1").Value = "" Range("G1").Value = "" Range("H1").Value = "" Range("I1").Value = "" Range("J1").Value = "" Range("K1").Value = "" Range("L1").Value = "" Range("M1").Value = "" Range("O1").Value = "" Range("P1").Value = "" Range("Q1").Value = "" Range("R1").Value = "" Range("S1").Value = "" Range("T1").Value = "" MyForm.Show MyForm.fregion.SetFocus End Sub I don't know if you saw this from my last post so i sent it again. -- Bucky F "JLGWhiz" wrote: One way is to put the UserForm.Show in a loop like: Sub UFloopDemo() 'Declarations, etc. 'Some code Do UserForm1.Show again = MsgBox("Show UserForm again?", vbYesNo, "More?") Loop while again = vbYes 'some more code End Sub You could possibly do it without having to unload the UserForm but it is difficult to tell without knowing what is on the form and what the code for the controls is. "willfeld" wrote: I have a user form with a command button that users enter data to a table. How can clear the user form with the same button click that enters the data so someone can continue to the next record? i know this must be simple but it is eluding me. -- Bucky F |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
Hmm! I have no idea why this message (and the previous incomplete one) are
here in this thread. Obviously, I post them here by mistake and now I'll have to go search for where they were supposed to have been posted to. Sorry about any confusion these two posts may have caused... I am not sure what I did to misdirect them to this thread. Rick "Rick Rothstein (MVP - VB)" wrote in message ... *** Sorry about the incomplete previous posting *** *** I accidentally sent it before I had finished. *** See inline comments... I can use this constantly - if I understand it Before you go and use this everywhere, remember that it was designed for your specific case... in effect, find the first digit that is not a zero (notice that the array and listing of digits do not have a zero in them), which is also why I said this formula only applies IF the first character after the leading zeroes is a digit (it will fail to work correctly if a non-digit ever follows the leading zeroes). and Gosh I hate to ask but could you explain how it works - I'll try. I think the Find({1,2,3,4,5,6,7,8,9} is an array function - I believe it uses the &"123456789" constant but I am really confused as to the MIN function and the 99 value and just basically how the formula flows. I guess the best place to start is by giving an overview of what the formula does. It finds the location of the first non-zero digit within the text and uses that to find the starting point for pulling out the text you wanted. It uses the MID function to get pull out a sub-string from the main piece of text. The format of the MID function is... MID(YourText, StartingPoint, NumberOfCharactersAfterStartingPoint) We will describe how to get the StartingPoint (the location of the first non-zero) in a moment; but, once you have it, you wanted that digit along with the remainder of the text after it. We don't know how many characters that will be as the number of leading zeroes can vary; however, there is no problem in asking for more characters than exist, so I took a guess that your text will never be longer than 99 characters (which is why the last value is 99... it is the 3rd argument of the MID function). Okay, so how do we get the StartingPoint. We use ths code... MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")) And how does that work. First off, we note that the FIND function cannot process an array (values contained within curly braces); however, the MIN can and so, embedding an array or a function call with an array (even if that function can't normally handle arrays) within it forces the array to get processed. This happens be feeding each array element into its encasing function (the FIND function in this case) one at a time so that the MIN function can determine which evaluated value is the smallest. So, the MIN function is forcing these evaluations to take place... FIND(1,A1&"123456789") FIND(2,A1&"123456789") FIND(3,A1&"123456789") FIND(4,A1&"123456789") FIND(5,A1&"123456789") FIND(6,A1&"123456789") FIND(7,A1&"123456789") FIND(8,A1&"123456789") FIND(9,A1&"123456789") The returned values from the above are the positions of the digit within the text, so when the MIN function looks at all of these values in order to find the smallest value, that value is the location in the text... that is, the location of the first non-zero digit within the text (which is our starting point for the MID function). Okay, now the concatenated "123456789" is placed there to ensure that FIND will in fact find the digit it is looking for at the moment. Well, that's it. I'm not so sure my explanation is as clear in words as the concepts are in my head, but hopefully there is enough for your to fight your way to an understanding of what is happening. Rick Is there **always** digit after the last leading zero (that is, never something like 000ABC)? If so... =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789 ")),99) I have an item number field which I use a formula to pull the last segment of the item number field ( such as: WBN-2-FCCG-043-00000094 or WBN-2-IVSG-043-0001A-A ) the results would be a text field such as 00000094 or 0001A-A or 0230-B I need to find a way to remove the leading 0's does anyone have any ideas? |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear user form after entry
More amusement than confusion <g
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Rick Rothstein (MVP - VB)" wrote in message ... Hmm! I have no idea why this message (and the previous incomplete one) are here in this thread. Obviously, I post them here by mistake and now I'll have to go search for where they were supposed to have been posted to. Sorry about any confusion these two posts may have caused... I am not sure what I did to misdirect them to this thread. Rick "Rick Rothstein (MVP - VB)" wrote in message ... *** Sorry about the incomplete previous posting *** *** I accidentally sent it before I had finished. *** See inline comments... I can use this constantly - if I understand it Before you go and use this everywhere, remember that it was designed for your specific case... in effect, find the first digit that is not a zero (notice that the array and listing of digits do not have a zero in them), which is also why I said this formula only applies IF the first character after the leading zeroes is a digit (it will fail to work correctly if a non-digit ever follows the leading zeroes). and Gosh I hate to ask but could you explain how it works - I'll try. I think the Find({1,2,3,4,5,6,7,8,9} is an array function - I believe it uses the &"123456789" constant but I am really confused as to the MIN function and the 99 value and just basically how the formula flows. I guess the best place to start is by giving an overview of what the formula does. It finds the location of the first non-zero digit within the text and uses that to find the starting point for pulling out the text you wanted. It uses the MID function to get pull out a sub-string from the main piece of text. The format of the MID function is... MID(YourText, StartingPoint, NumberOfCharactersAfterStartingPoint) We will describe how to get the StartingPoint (the location of the first non-zero) in a moment; but, once you have it, you wanted that digit along with the remainder of the text after it. We don't know how many characters that will be as the number of leading zeroes can vary; however, there is no problem in asking for more characters than exist, so I took a guess that your text will never be longer than 99 characters (which is why the last value is 99... it is the 3rd argument of the MID function). Okay, so how do we get the StartingPoint. We use ths code... MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")) And how does that work. First off, we note that the FIND function cannot process an array (values contained within curly braces); however, the MIN can and so, embedding an array or a function call with an array (even if that function can't normally handle arrays) within it forces the array to get processed. This happens be feeding each array element into its encasing function (the FIND function in this case) one at a time so that the MIN function can determine which evaluated value is the smallest. So, the MIN function is forcing these evaluations to take place... FIND(1,A1&"123456789") FIND(2,A1&"123456789") FIND(3,A1&"123456789") FIND(4,A1&"123456789") FIND(5,A1&"123456789") FIND(6,A1&"123456789") FIND(7,A1&"123456789") FIND(8,A1&"123456789") FIND(9,A1&"123456789") The returned values from the above are the positions of the digit within the text, so when the MIN function looks at all of these values in order to find the smallest value, that value is the location in the text... that is, the location of the first non-zero digit within the text (which is our starting point for the MID function). Okay, now the concatenated "123456789" is placed there to ensure that FIND will in fact find the digit it is looking for at the moment. Well, that's it. I'm not so sure my explanation is as clear in words as the concepts are in my head, but hopefully there is enough for your to fight your way to an understanding of what is happening. Rick Is there **always** digit after the last leading zero (that is, never something like 000ABC)? If so... =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789 ")),99) I have an item number field which I use a formula to pull the last segment of the item number field ( such as: WBN-2-FCCG-043-00000094 or WBN-2-IVSG-043-0001A-A ) the results would be a text field such as 00000094 or 0001A-A or 0230-B I need to find a way to remove the leading 0's does anyone have any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CLear Data Entry Form Fields | Excel Discussion (Misc queries) | |||
| Clear a Checkbox in a a user form | Excel Discussion (Misc queries) | |||
| How can I protect cell from user entry but able to alter/clear in | Excel Programming | |||
| Auto email - With every new entry on my user form | Excel Discussion (Misc queries) | |||
| Clear Contents Of A Text Box In User Form | Excel Programming | |||
Linear Mode
