Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
I don't know enough about VBA yet to do this without help.
I have a column of variable tolerances which have one of the following forms: 5 ST 5.3 (aka number, space, "ST", space, desired number) 5 +-6 (aka number, space, +- symbol, another numberm) 5 +4/-2 (aka number, space, positive number, /, negative number) I need to be able to extract the tolerance numbers in order to use them for conditional formatting. I would want the following from the above: 5.3 (there is a set tolerance off this number, so all I need is this number, I don't need 5 at all). 5 and 6 to turn into -1 to 11 (the max/min of the tolerance). 5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance). This surely involves finding spaces, /, etc., and determining what is what, but I'm having a lot of trouble getting things to work in VBA. Suggestions and existing code to improve much appreciated. Thanks, NP |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
Alright, I was able to make a little bit of progress.
I created a function which gets the cell value as a string, reverses the string, and finds the items before the first space (which since the string is reversed refer to the last items of the original string). Thus I can get 5.3, +-6, and +4/-2. Next up - How to convert a string into a number (this is probably easily searched on the archives). How to make a function which identifies if the first portion of the string is the +- symbol, and then remove it and give me the number 6. How to make a function which if the above isn't satisfied, then assumes the form of the tolerance is +X/-Y (where Y can equal 0 and there will be no - sign), and finds X and Y. The tricky part here is sometimes there will be a Y, sometimes not. The code below is what I'm using so far. Ways to make it cleaner of course appreciated: Sub ReConfirmVariableVariances() Dim TeishutsuItemRow As Long Dim TeishutsuItemColumn As Long Dim PrevXTIR As Long Dim SokuteiPointNumber As Long Dim SolutionTolerance As Long Dim ToleranceValue As String Dim XYLCounter As Long Dim TempLoop As Long ' SET THE VARIABLES FOR TEISHUTSUROW AND COLUMN. TeishutsuItemRow = 3 TeishutsuItemColumn = 8 SokuteiPointNumber = 1 PrevXTIR = 0 Do Until SokuteiPointNumber = 51 For TempLoop = 1 To 1 If Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 1).Value = SokuteiPointNumber Then If Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 2).Value = "" Then TempLoop = 2 TeishutsuItemRow = PrevXTIR Else TempLoop = 2 End If Else TeishutsuItemRow = TeishutsuItemRow + 1 End If TempLoop = TempLoop - 1 Next TempLoop For XYLCounter = 0 To 2 ToleranceString = Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + XYLCounter + 2).Value ToleranceValue = LastNumber(ToleranceString) MsgBox "ToleranceValue = " & (ToleranceValue) Next XYLCounter PrevXTIR = TeishutsuItemRow TeishutsuItemRow = TeishutsuItemRow + 1 SokuteiPointNumber = SokuteiPointNumber + 1 Loop End Sub Function LastNumber(ToleranceString) As String Dim Newstring As String Dim SpacePlace As Integer Dim RevOut As String Newstring = StrReverse(ToleranceString) SpacePlace = InStr(1, Newstring, " ") RevOut = Left(Newstring, SpacePlace - 1) LastNumber = StrReverse(RevOut) End Function On 7月15日, 午前10:03, pallaver wrote: I don't know enough about VBA yet to do this without help. I have a column of variable tolerances which have one of the following forms: 5 ST 5.3 (aka number, space, "ST", space, desired number) 5 +-6 (aka number, space, +- symbol, another numberm) 5 +4/-2 (aka number, space, positive number, /, negative number) I need to be able to extract the tolerance numbers in order to use them for conditional formatting. I would want the following from the above: 5.3 (there is a set tolerance off this number, so all I need is this number, I don't need 5 at all). 5 and 6 to turn into -1 to 11 (the max/min of the tolerance). 5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance). This surely involves finding spaces, /, etc., and determining what is what, but I'm having a lot of trouble getting things to work in VBA. Suggestions and existing code to improve much appreciated. Thanks, NP |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
The following code will work for the examples you've provided, given that all
entries are exactly like the ones you've provided, it should work for a list of similar type entries. There is a good chance of it failing or locking up in places if the entries in your list don't follow the same format of your examples. I leave it for the student to investigate Help on any unfamilair VB commands used. Sub ParseEntries() Dim listOfEntries As Range Dim anyEntry As Range Const numericCharacters = "+-0123456789" Const specialText = " ST " Const separator1 = " " ' single space Const separator2 = "/" Const separator3 = "+-" Dim initialValue As String Dim result1 As String Dim result2 As String Dim position As Integer 'get address of cells 'with entries in them 'assumes it starts at 'A1 and continues as an 'unbroken list down that column ' change "A1:" to have address of first entry in the list ' change the A column references as required. Set listOfEntries = Range("A1:" & _ Range("A" & Rows.Count).End(xlUp).Address) 'now work through each cell in the group For Each anyEntry In listOfEntries 'pick up the number at the beginning 'of the entry initialValue = Left(anyEntry, InStr(anyEntry, separator1) - 1) result1 = "" ' clear previous results result2 = "" ' clear previous results If InStr(anyEntry, specialText) Then 'contains " ST ", all we want is the 'number at the far right result1 = Right(anyEntry, Len(anyEntry) - _ InStrRev(anyEntry, separator1)) result2 = result1 ' so we have something later initialValue = 0 ' so the math works later! Else 'do we have an entry with "+-" 'presumably followed by single numeric value? If InStr(anyEntry, separator3) Then 'yes it has "+-" in it result1 = Right(anyEntry, Len(anyEntry) - _ InStrRev(anyEntry, separator3) - 1) result2 = "-" & result1 ' make the negative side Else 'we must assume it is an entry 'with the / separator in it 'get the value on the right side of the "/" For position = InStr(anyEntry, separator2) To Len(anyEntry) If InStr(numericCharacters, Mid(anyEntry, position, 1)) Then result1 = result1 & Mid(anyEntry, position, 1) End If Next ' position loop end 'get the value on the left side of the "/" 'work left to right from the "/" For position = (InStr(anyEntry, separator2) - 1) To 1 Step -1 If InStr(numericCharacters, Mid(anyEntry, position, 1)) Then result2 = result2 & Mid(anyEntry, position, 1) End If Next End If End If ' end of 1st/outer If..Then..Else 'display the results, If (Val(initialValue) + Val(result1) <= _ Val(initialValue) + Val(result2)) Then 'one limit in cell 1 column to the right of the entry anyEntry.Offset(0, 1) = Val(initialValue) + Val(result1) 'other limit in cell 2 columns to the right of the entry anyEntry.Offset(0, 2) = Val(initialValue) + Val(result2) Else 'one limit in cell 1 column to the right of the entry anyEntry.Offset(0, 1) = Val(initialValue) + Val(result2) 'other limit in cell 2 columns to the right of the entry anyEntry.Offset(0, 2) = Val(initialValue) + Val(result1) End If Next End Sub "pallaver" wrote: I don't know enough about VBA yet to do this without help. I have a column of variable tolerances which have one of the following forms: 5 ST 5.3 (aka number, space, "ST", space, desired number) 5 +-6 (aka number, space, +- symbol, another numberm) 5 +4/-2 (aka number, space, positive number, /, negative number) I need to be able to extract the tolerance numbers in order to use them for conditional formatting. I would want the following from the above: 5.3 (there is a set tolerance off this number, so all I need is this number, I don't need 5 at all). 5 and 6 to turn into -1 to 11 (the max/min of the tolerance). 5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance). This surely involves finding spaces, /, etc., and determining what is what, but I'm having a lot of trouble getting things to work in VBA. Suggestions and existing code to improve much appreciated. Thanks, NP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
Below are two functions you can use to find the maximum tolerance (MaxTol)
and the minimum tolerance (MinTol). These functions can be called from within your own VB code or, if placed in a Module, directly from the worksheet (as placing them in a Module makes them into a UDF). I also created an error condition return value (#BADFORMAT!) that will be returned if the variable tolerance (VarTol) passed into the function cannot be resolved to one of the three formats or if a non-numeric value is contained in one of the number parts. Rick Function MaxTol(VarTol As String) As Variant Dim Parts() As String On Error GoTo BadFormat If VarTol Like "* [Ss][Tt] *" Then MaxTol = CDbl(Split(VarTol, "ST", , vbTextCompare)(1)) Exit Function End If If VarTol Like "* +-*" Then Parts = Split(VarTol, "+-") MaxTol = CDbl(Parts(0)) + CDbl(Parts(1)) Exit Function End If If VarTol Like "* */*" Then MaxTol = CDbl(Split(VarTol)(0)) + CDbl(Split(Split(VarTol)(1), "/")(0)) Exit Function End If BadFormat: MaxTol = "#BADFORMAT!" End Function Function MinTol(VarTol As String) As Variant Dim Parts() As String On Error GoTo BadFormat If VarTol Like "* [Ss][Tt] *" Then MinTol = CDbl(Split(VarTol, "ST", , vbTextCompare)(1)) Exit Function End If If VarTol Like "* +-*" Then Parts = Split(VarTol, "+-") MinTol = CDbl(Parts(0)) - CDbl(Parts(1)) Exit Function End If If VarTol Like "* */*" Then MinTol = CDbl(Split(VarTol)(0)) + CDbl(Split(Split(VarTol)(1), "/")(1)) Exit Function End If BadFormat: MinTol = "#BADFORMAT!" End Function "pallaver" wrote in message ... I don't know enough about VBA yet to do this without help. I have a column of variable tolerances which have one of the following forms: 5 ST 5.3 (aka number, space, "ST", space, desired number) 5 +-6 (aka number, space, +- symbol, another numberm) 5 +4/-2 (aka number, space, positive number, /, negative number) I need to be able to extract the tolerance numbers in order to use them for conditional formatting. I would want the following from the above: 5.3 (there is a set tolerance off this number, so all I need is this number, I don't need 5 at all). 5 and 6 to turn into -1 to 11 (the max/min of the tolerance). 5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance). This surely involves finding spaces, /, etc., and determining what is what, but I'm having a lot of trouble getting things to work in VBA. Suggestions and existing code to improve much appreciated. Thanks, NP |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
Good to see you're still in there plugging away - hopefully the code I
provided in another post in this thread will help you some more. But I see some situations in this post of yours that I possibly didn't allow for. If you try out my code and it seems to be taking forever to give results and seems to have stopped putting results on the sheet, use [Ctrl]+[Break] to go into debug mode to see if it may be locked in a loop. Might even suffer a dramatic failure somewhere along the line. But those types of things often show you situations you haven't thought of just yet. "pallaver" wrote: Alright, I was able to make a little bit of progress. I created a function which gets the cell value as a string, reverses the string, and finds the items before the first space (which since the string is reversed refer to the last items of the original string). Thus I can get 5.3, +-6, and +4/-2. Next up - How to convert a string into a number (this is probably easily searched on the archives). How to make a function which identifies if the first portion of the string is the +- symbol, and then remove it and give me the number 6. How to make a function which if the above isn't satisfied, then assumes the form of the tolerance is +X/-Y (where Y can equal 0 and there will be no - sign), and finds X and Y. The tricky part here is sometimes there will be a Y, sometimes not. The code below is what I'm using so far. Ways to make it cleaner of course appreciated: Sub ReConfirmVariableVariances() Dim TeishutsuItemRow As Long Dim TeishutsuItemColumn As Long Dim PrevXTIR As Long Dim SokuteiPointNumber As Long Dim SolutionTolerance As Long Dim ToleranceValue As String Dim XYLCounter As Long Dim TempLoop As Long ' SET THE VARIABLES FOR TEISHUTSUROW AND COLUMN. TeishutsuItemRow = 3 TeishutsuItemColumn = 8 SokuteiPointNumber = 1 PrevXTIR = 0 Do Until SokuteiPointNumber = 51 For TempLoop = 1 To 1 If Sheets("箇").Cells(TeishutsuItemRow, TeishutsuItemColumn + 1).Value = SokuteiPointNumber Then If Sheets("箇").Cells(TeishutsuItemRow, TeishutsuItemColumn + 2).Value = "" Then TempLoop = 2 TeishutsuItemRow = PrevXTIR Else TempLoop = 2 End If Else TeishutsuItemRow = TeishutsuItemRow + 1 End If TempLoop = TempLoop - 1 Next TempLoop For XYLCounter = 0 To 2 ToleranceString = Sheets("箇").Cells(TeishutsuItemRow, TeishutsuItemColumn + XYLCounter + 2).Value ToleranceValue = LastNumber(ToleranceString) MsgBox "ToleranceValue = " & (ToleranceValue) Next XYLCounter PrevXTIR = TeishutsuItemRow TeishutsuItemRow = TeishutsuItemRow + 1 SokuteiPointNumber = SokuteiPointNumber + 1 Loop End Sub Function LastNumber(ToleranceString) As String Dim Newstring As String Dim SpacePlace As Integer Dim RevOut As String Newstring = StrReverse(ToleranceString) SpacePlace = InStr(1, Newstring, " ") RevOut = Left(Newstring, SpacePlace - 1) LastNumber = StrReverse(RevOut) End Function On 715, 10:03, pallaver wrote: I don't know enough about VBA yet to do this without help. I have a column of variable tolerances which have one of the following forms: 5 ST 5.3 (aka number, space, "ST", space, desired number) 5 +-6 (aka number, space, +- symbol, another numberm) 5 +4/-2 (aka number, space, positive number, /, negative number) I need to be able to extract the tolerance numbers in order to use them for conditional formatting. I would want the following from the above: 5.3 (there is a set tolerance off this number, so all I need is this number, I don't need 5 at all). 5 and 6 to turn into -1 to 11 (the max/min of the tolerance). 5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance). This surely involves finding spaces, /, etc., and determining what is what, but I'm having a lot of trouble getting things to work in VBA. Suggestions and existing code to improve much appreciated. Thanks, NP |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
I'm getting closer.
Now the problem that I have though which I didn't foresee, is that all the numbers are having the decimal replaced removed as well. Therefore what should read as "4.5" is now "45"!! Ack... It has something to do with my code to make the string into a number.... On 7$B7n(B15$BF|(B, $B8a8e(B12:06, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Good to see you're still in there plugging away - hopefully the code I provided in another post in this thread will help you some more. But I see some situations in this post of yours that I possibly didn't allow for. If you try out my code and it seems to be taking forever to give results and seems to have stopped putting results on the sheet, use [Ctrl]+[Break] to go into debug mode to see if it may be locked in a loop. Might even suffer a dramatic failure somewhere along the line. But those types of things often show you situations you haven't thought of just yet. "pallaver" wrote: Alright, I was able to make a little bit of progress. I created a function which gets the cell value as a string, reverses the string, and finds the items before the first space (which since the string is reversed refer to the last items of the original string). Thus I can get 5.3, +-6, and +4/-2. Next up - How to convert a string into a number (this is probably easily searched on the archives). How to make a function which identifies if the first portion of the string is the +- symbol, and then remove it and give me the number 6. How to make a function which if the above isn't satisfied, then assumes the form of the tolerance is +X/-Y (where Y can equal 0 and there will be no - sign), and finds X and Y. The tricky part here is sometimes there will be a Y, sometimes not. The code below is what I'm using so far. Ways to make it cleaner of course appreciated: Sub ReConfirmVariableVariances() Dim TeishutsuItemRow As Long Dim TeishutsuItemColumn As Long Dim PrevXTIR As Long Dim SokuteiPointNumber As Long Dim SolutionTolerance As Long Dim ToleranceValue As String Dim XYLCounter As Long Dim TempLoop As Long ' SET THE VARIABLES FOR TEISHUTSUROW AND COLUMN. TeishutsuItemRow = 3 TeishutsuItemColumn = 8 SokuteiPointNumber = 1 PrevXTIR = 0 Do Until SokuteiPointNumber = 51 For TempLoop = 1 To 1 If Sheets("$BDs=PMQ(B").Cells(TeishutsuItemRow, TeishutsuItemColumn + 1).Value = SokuteiPointNumber Then If Sheets("$BDs=PMQ(B").Cells(TeishutsuItemRow, TeishutsuItemColumn + 2).Value = "" Then TempLoop = 2 TeishutsuItemRow = PrevXTIR Else TempLoop = 2 End If Else TeishutsuItemRow = TeishutsuItemRow + 1 End If TempLoop = TempLoop - 1 Next TempLoop For XYLCounter = 0 To 2 ToleranceString = Sheets("$BDs=PMQ(B").Cells(TeishutsuItemRow, TeishutsuItemColumn + XYLCounter + 2).Value ToleranceValue = LastNumber(ToleranceString) MsgBox "ToleranceValue = " & (ToleranceValue) Next XYLCounter PrevXTIR = TeishutsuItemRow TeishutsuItemRow = TeishutsuItemRow + 1 SokuteiPointNumber = SokuteiPointNumber + 1 Loop End Sub Function LastNumber(ToleranceString) As String Dim Newstring As String Dim SpacePlace As Integer Dim RevOut As String Newstring = StrReverse(ToleranceString) SpacePlace = InStr(1, Newstring, " ") RevOut = Left(Newstring, SpacePlace - 1) LastNumber = StrReverse(RevOut) End Function On 7$B7n(B15$BF|(B, $B8aA0(B10:03, pallaver wrote: I don't know enough about VBA yet to do this without help. I have a column of variable tolerances which have one of the following forms: 5 ST 5.3 (aka number, space, "ST", space, desired number) 5 +-6 (aka number, space, +- symbol, another numberm) 5 +4/-2 (aka number, space, positive number, /, negative number) I need to be able to extract the tolerance numbers in order to use them for conditional formatting. I would want the following from the above: 5.3 (there is a set tolerance off this number, so all I need is this number, I don't need 5 at all). 5 and 6 to turn into -1 to 11 (the max/min of the tolerance). 5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance). This surely involves finding spaces, /, etc., and determining what is what, but I'm having a lot of trouble getting things to work in VBA. Suggestions and existing code to improve much appreciated. Thanks, NP- $B0zMQ%F%-%9%H$rI=<($7$J$$(B - - $B0zMQ%F%-%9%H$rI=<((B - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
Ah... never mind. That's what MyVal(String) is. Excellent.
Only thing now is to figure out how to eliminate the +-. Ah... just got it! I had a whole bunch of other spacing programs that I copy/ pasted in my previous searched, found what I thought would work, and bam, golden. I used this: ToleranceValuePML = Right$(ToleranceValuePML, Len(ToleranceValuePML) - 1) Which turned +-4.6 into 4.6. I need to do a little bit of cleaning up from my old method which deleted the decimal point to the MyVal(string) method, but otherwise, I think I actually may have got it. When all is said and done I'll post the entire thing on here, with conditional formatting (which I know how to do thankfully)!!!! Thanks, and the skies are brightening up... :) On 7$B7n(B15$BF|(B, $B8a8e(B12:06, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Good to see you're still in there plugging away - hopefully the code I provided in another post in this thread will help you some more. But I see some situations in this post of yours that I possibly didn't allow for. If you try out my code and it seems to be taking forever to give results and seems to have stopped putting results on the sheet, use [Ctrl]+[Break] to go into debug mode to see if it may be locked in a loop. Might even suffer a dramatic failure somewhere along the line. But those types of things often show you situations you haven't thought of just yet. "pallaver" wrote: Alright, I was able to make a little bit of progress. I created a function which gets the cell value as a string, reverses the string, and finds the items before the first space (which since the string is reversed refer to the last items of the original string). Thus I can get 5.3, +-6, and +4/-2. Next up - How to convert a string into a number (this is probably easily searched on the archives). How to make a function which identifies if the first portion of the string is the +- symbol, and then remove it and give me the number 6. How to make a function which if the above isn't satisfied, then assumes the form of the tolerance is +X/-Y (where Y can equal 0 and there will be no - sign), and finds X and Y. The tricky part here is sometimes there will be a Y, sometimes not. The code below is what I'm using so far. Ways to make it cleaner of course appreciated: Sub ReConfirmVariableVariances() Dim TeishutsuItemRow As Long Dim TeishutsuItemColumn As Long Dim PrevXTIR As Long Dim SokuteiPointNumber As Long Dim SolutionTolerance As Long Dim ToleranceValue As String Dim XYLCounter As Long Dim TempLoop As Long ' SET THE VARIABLES FOR TEISHUTSUROW AND COLUMN. TeishutsuItemRow = 3 TeishutsuItemColumn = 8 SokuteiPointNumber = 1 PrevXTIR = 0 Do Until SokuteiPointNumber = 51 For TempLoop = 1 To 1 If Sheets("$BDs=PMQ(B").Cells(TeishutsuItemRow, TeishutsuItemColumn + 1).Value = SokuteiPointNumber Then If Sheets("$BDs=PMQ(B").Cells(TeishutsuItemRow, TeishutsuItemColumn + 2).Value = "" Then TempLoop = 2 TeishutsuItemRow = PrevXTIR Else TempLoop = 2 End If Else TeishutsuItemRow = TeishutsuItemRow + 1 End If TempLoop = TempLoop - 1 Next TempLoop For XYLCounter = 0 To 2 ToleranceString = Sheets("$BDs=PMQ(B").Cells(TeishutsuItemRow, TeishutsuItemColumn + XYLCounter + 2).Value ToleranceValue = LastNumber(ToleranceString) MsgBox "ToleranceValue = " & (ToleranceValue) Next XYLCounter PrevXTIR = TeishutsuItemRow TeishutsuItemRow = TeishutsuItemRow + 1 SokuteiPointNumber = SokuteiPointNumber + 1 Loop End Sub Function LastNumber(ToleranceString) As String Dim Newstring As String Dim SpacePlace As Integer Dim RevOut As String Newstring = StrReverse(ToleranceString) SpacePlace = InStr(1, Newstring, " ") RevOut = Left(Newstring, SpacePlace - 1) LastNumber = StrReverse(RevOut) End Function On 7$B7n(B15$BF|(B, $B8aA0(B10:03, pallaver wrote: I don't know enough about VBA yet to do this without help. I have a column of variable tolerances which have one of the following forms: 5 ST 5.3 (aka number, space, "ST", space, desired number) 5 +-6 (aka number, space, +- symbol, another numberm) 5 +4/-2 (aka number, space, positive number, /, negative number) I need to be able to extract the tolerance numbers in order to use them for conditional formatting. I would want the following from the above: 5.3 (there is a set tolerance off this number, so all I need is this number, I don't need 5 at all). 5 and 6 to turn into -1 to 11 (the max/min of the tolerance). 5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance). This surely involves finding spaces, /, etc., and determining what is what, but I'm having a lot of trouble getting things to work in VBA. Suggestions and existing code to improve much appreciated. Thanks, NP- $B0zMQ%F%-%9%H$rI=<($7$J$$(B - - $B0zMQ%F%-%9%H$rI=<((B - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
I guess you didn't want to look at the code I posted?
Rick "pallaver" wrote in message ... Ah... never mind. That's what MyVal(String) is. Excellent. Only thing now is to figure out how to eliminate the +-. Ah... just got it! I had a whole bunch of other spacing programs that I copy/ pasted in my previous searched, found what I thought would work, and bam, golden. I used this: ToleranceValuePML = Right$(ToleranceValuePML, Len(ToleranceValuePML) - 1) Which turned +-4.6 into 4.6. I need to do a little bit of cleaning up from my old method which deleted the decimal point to the MyVal(string) method, but otherwise, I think I actually may have got it. When all is said and done I'll post the entire thing on here, with conditional formatting (which I know how to do thankfully)!!!! Thanks, and the skies are brightening up... :) On 7$B7n(B15$BF|(B, $B8a8e(B12:06, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Good to see you're still in there plugging away - hopefully the code I provided in another post in this thread will help you some more. But I see some situations in this post of yours that I possibly didn't allow for. If you try out my code and it seems to be taking forever to give results and seems to have stopped putting results on the sheet, use [Ctrl]+[Break] to go into debug mode to see if it may be locked in a loop. Might even suffer a dramatic failure somewhere along the line. But those types of things often show you situations you haven't thought of just yet. "pallaver" wrote: Alright, I was able to make a little bit of progress. I created a function which gets the cell value as a string, reverses the string, and finds the items before the first space (which since the string is reversed refer to the last items of the original string). Thus I can get 5.3, +-6, and +4/-2. Next up - How to convert a string into a number (this is probably easily searched on the archives). How to make a function which identifies if the first portion of the string is the +- symbol, and then remove it and give me the number 6. How to make a function which if the above isn't satisfied, then assumes the form of the tolerance is +X/-Y (where Y can equal 0 and there will be no - sign), and finds X and Y. The tricky part here is sometimes there will be a Y, sometimes not. The code below is what I'm using so far. Ways to make it cleaner of course appreciated: Sub ReConfirmVariableVariances() Dim TeishutsuItemRow As Long Dim TeishutsuItemColumn As Long Dim PrevXTIR As Long Dim SokuteiPointNumber As Long Dim SolutionTolerance As Long Dim ToleranceValue As String Dim XYLCounter As Long Dim TempLoop As Long ' SET THE VARIABLES FOR TEISHUTSUROW AND COLUMN. TeishutsuItemRow = 3 TeishutsuItemColumn = 8 SokuteiPointNumber = 1 PrevXTIR = 0 Do Until SokuteiPointNumber = 51 For TempLoop = 1 To 1 If Sheets("$BDs=PMQ(B").Cells(TeishutsuItemRow, TeishutsuItemColumn + 1).Value = SokuteiPointNumber Then If Sheets("$BDs=PMQ(B").Cells(TeishutsuItemRow, TeishutsuItemColumn + 2).Value = "" Then TempLoop = 2 TeishutsuItemRow = PrevXTIR Else TempLoop = 2 End If Else TeishutsuItemRow = TeishutsuItemRow + 1 End If TempLoop = TempLoop - 1 Next TempLoop For XYLCounter = 0 To 2 ToleranceString = Sheets("$BDs=PMQ(B").Cells(TeishutsuItemRow, TeishutsuItemColumn + XYLCounter + 2).Value ToleranceValue = LastNumber(ToleranceString) MsgBox "ToleranceValue = " & (ToleranceValue) Next XYLCounter PrevXTIR = TeishutsuItemRow TeishutsuItemRow = TeishutsuItemRow + 1 SokuteiPointNumber = SokuteiPointNumber + 1 Loop End Sub Function LastNumber(ToleranceString) As String Dim Newstring As String Dim SpacePlace As Integer Dim RevOut As String Newstring = StrReverse(ToleranceString) SpacePlace = InStr(1, Newstring, " ") RevOut = Left(Newstring, SpacePlace - 1) LastNumber = StrReverse(RevOut) End Function On 7$B7n(B15$BF|(B, $B8aA0(B10:03, pallaver wrote: I don't know enough about VBA yet to do this without help. I have a column of variable tolerances which have one of the following forms: 5 ST 5.3 (aka number, space, "ST", space, desired number) 5 +-6 (aka number, space, +- symbol, another numberm) 5 +4/-2 (aka number, space, positive number, /, negative number) I need to be able to extract the tolerance numbers in order to use them for conditional formatting. I would want the following from the above: 5.3 (there is a set tolerance off this number, so all I need is this number, I don't need 5 at all). 5 and 6 to turn into -1 to 11 (the max/min of the tolerance). 5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance). This surely involves finding spaces, /, etc., and determining what is what, but I'm having a lot of trouble getting things to work in VBA. Suggestions and existing code to improve much appreciated. Thanks, NP- $B0zMQ%F%-%9%H$rI=<($7$J$$(B - - $B0zMQ%F%-%9%H$rI=<((B - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
Oh, sorry, I just saw now what you posted Rick, I'm sorry. I was just
replying off off the previous screen without refreshing until now. After giving it a good 10 minutes go, I had to do a little bit of variations, but otherwise much easier than my current system. For now though, much harder for me to understand what's going on. I was SOOOO close to with the other way. Maybe I'll just finish it to make me personally satisfied. Thanks to the both of you guys (and sorry for the delayed visual on your solution Rick). =NP On 7$B7n(B15$BF|(B, $B8a8e(B3:50, "Rick Rothstein \(MVP - VB\)" wrote: I guess you didn't want to look at the code I posted? Rick "pallaver" wrote in message |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
After giving it a good 10 minutes go, I had to do a little bit of
variations, but otherwise much easier than my current system. For now though, much harder for me to understand what's going on. If you have any questions about what is going on in the code, feel free to ask. Rick |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
Got both codes to work!!!
Getting late so am going to call it a night, but will post them both tomorrow with questions I have on your code Rick. As for JLatham's method, I actually understand it all plus some extra things I added into it as well (like an if statement to detect a minus and delete it, but skip over that step if it's 0). This is all to be used for a point measuring system which originally was created and modified by hand, but now can be modified by macro and hopefully speed things up. Thanks for the help, check back in tomorrow to take a peek at the codes which I'll post so that those looking for clues, tips, and to pilfer by archives searches have some useful fodder. Since, NP On 7月15日, 午後5:15, "Rick Rothstein \(MVP - VB\)" wrote: After giving it a good 10 minutes go, I had to do a little bit of variations, but otherwise much easier than my current system. For now though, much harder for me to understand what's going on. If you have any questions about what is going on in the code, feel free to ask. Rick |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
NEEDS A CORRECTION.
After reading one of your posts, I realize that the constant numericCharacters needs to have the decimal point added to it. If you try that code out, change that line to read: Const numericCharacters = "0123456789+-." The change in the sequence of characters in it will probably improve performance just a tiny bit since the probability of encountering a digit is higher than one of the +, - or . symbols. "JLatham" wrote: The following code will work for the examples you've provided, given that all entries are exactly like the ones you've provided, it should work for a list of similar type entries. There is a good chance of it failing or locking up in places if the entries in your list don't follow the same format of your examples. I leave it for the student to investigate Help on any unfamilair VB commands used. Sub ParseEntries() Dim listOfEntries As Range Dim anyEntry As Range Const numericCharacters = "+-0123456789" Const specialText = " ST " Const separator1 = " " ' single space Const separator2 = "/" Const separator3 = "+-" Dim initialValue As String Dim result1 As String Dim result2 As String Dim position As Integer 'get address of cells 'with entries in them 'assumes it starts at 'A1 and continues as an 'unbroken list down that column ' change "A1:" to have address of first entry in the list ' change the A column references as required. Set listOfEntries = Range("A1:" & _ Range("A" & Rows.Count).End(xlUp).Address) 'now work through each cell in the group For Each anyEntry In listOfEntries 'pick up the number at the beginning 'of the entry initialValue = Left(anyEntry, InStr(anyEntry, separator1) - 1) result1 = "" ' clear previous results result2 = "" ' clear previous results If InStr(anyEntry, specialText) Then 'contains " ST ", all we want is the 'number at the far right result1 = Right(anyEntry, Len(anyEntry) - _ InStrRev(anyEntry, separator1)) result2 = result1 ' so we have something later initialValue = 0 ' so the math works later! Else 'do we have an entry with "+-" 'presumably followed by single numeric value? If InStr(anyEntry, separator3) Then 'yes it has "+-" in it result1 = Right(anyEntry, Len(anyEntry) - _ InStrRev(anyEntry, separator3) - 1) result2 = "-" & result1 ' make the negative side Else 'we must assume it is an entry 'with the / separator in it 'get the value on the right side of the "/" For position = InStr(anyEntry, separator2) To Len(anyEntry) If InStr(numericCharacters, Mid(anyEntry, position, 1)) Then result1 = result1 & Mid(anyEntry, position, 1) End If Next ' position loop end 'get the value on the left side of the "/" 'work left to right from the "/" For position = (InStr(anyEntry, separator2) - 1) To 1 Step -1 If InStr(numericCharacters, Mid(anyEntry, position, 1)) Then result2 = result2 & Mid(anyEntry, position, 1) End If Next End If End If ' end of 1st/outer If..Then..Else 'display the results, If (Val(initialValue) + Val(result1) <= _ Val(initialValue) + Val(result2)) Then 'one limit in cell 1 column to the right of the entry anyEntry.Offset(0, 1) = Val(initialValue) + Val(result1) 'other limit in cell 2 columns to the right of the entry anyEntry.Offset(0, 2) = Val(initialValue) + Val(result2) Else 'one limit in cell 1 column to the right of the entry anyEntry.Offset(0, 1) = Val(initialValue) + Val(result2) 'other limit in cell 2 columns to the right of the entry anyEntry.Offset(0, 2) = Val(initialValue) + Val(result1) End If Next End Sub "pallaver" wrote: I don't know enough about VBA yet to do this without help. I have a column of variable tolerances which have one of the following forms: 5 ST 5.3 (aka number, space, "ST", space, desired number) 5 +-6 (aka number, space, +- symbol, another numberm) 5 +4/-2 (aka number, space, positive number, /, negative number) I need to be able to extract the tolerance numbers in order to use them for conditional formatting. I would want the following from the above: 5.3 (there is a set tolerance off this number, so all I need is this number, I don't need 5 at all). 5 and 6 to turn into -1 to 11 (the max/min of the tolerance). 5, 4, and -2 to turn into 3 to 9 (the max/min of the tolerance). This surely involves finding spaces, /, etc., and determining what is what, but I'm having a lot of trouble getting things to work in VBA. Suggestions and existing code to improve much appreciated. Thanks, NP |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
Hehe... don't worry about it JLatham. I already added that in !!! I
realized it was deleting it so took a guess and got it. On 7月15日, 午後8:12, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: NEEDS A CORRECTION. After reading one of your posts, I realize that the constant numericCharacters needs to have the decimal point added to it. If you try that code out, change that line to read: Const numericCharacters = "0123456789+-." The change in the sequence of characters in it will probably improve performance just a tiny bit since the probability of encountering a digit is higher than one of the +, - or . symbols. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
That being said, for the people (most likely now archive browsing folk
of this group), here are the two solutions for the problem of extracting numbers for stuff within a cell. Again, I'm posting these so hopefully people like me who try to use the archives first to figure stuff out can copy/paste this into Excel and understand it / alter it to satisfy their needs. As the saying goes, good poets borrow, great poets steal. --------- JLatham's SOLUTION Style ----------- Sub ReConfirmVariableVariances() Dim TeishutsuItemRow As Long Dim TeishutsuItemColumn As Long Dim XTIR As Long Dim YTIR As Long Dim LTIR As Long Dim SokuteiPointNumber As Long Dim HighestPointNumber As Long Dim ToleranceStringX As String Dim ToleranceStringY As String Dim ToleranceStringL As String Dim ToleranceValueX As String Dim ToleranceValueY As String Dim ToleranceValuePML As String Dim ToleranceValuePL As String Dim ToleranceValueML As String Dim ToleranceValueLHigh As String Dim ToleranceValueLLow As String Dim LValueBase As String Dim TempLoop As Long Const Separator1 = "/" Const Separator2 = "±" Const NumericCharacters = "+-0123456789." Const MinusSign = "-" Dim Position As Integer ' SET THE VARIABLES FOR TEISHUTSUROW AND COLUMN. ' ALSO SET PREVIOUS X,Y,L ITEM ROWS IN THE EVENT REPEATED (BLANK) TOLERANCE ENTRY. ' SOKUTEI AND HIGHEST POINT NUMBERS SET TO 1. TeishutsuItemRow = 3 TeishutsuItemColumn = 8 XTIR = 3 YTIR = 3 LTIR = 3 SokuteiPointNumber = 1 HighestPointNumber = 1 For TempLoop = 3 To 300 If Sheets("提出用").Cells(TempLoop, TeishutsuItemColumn + 1).Value HighestPointNumber Then HighestPointNumber = Sheets("提出用").Cells(TempLoop, TeishutsuItemColumn + 1).Value End If Next TempLoop ' LOOP FOR ALL SOKUTEI POINTS. Do Until SokuteiPointNumber = HighestPointNumber + 1 ' RESET VALUES FOR TOLERANCES ToleranceValueX = "" ToleranceValueY = "" ToleranceValuePML = "" ToleranceValuePL = "" ToleranceValueML = "" ' FIRST MATCH UP SOKUTEI POINT WITH ROW ON 提出用 SHEET, AND SOLVE FOR XYL TOLERANCES. For TempLoop = 1 To 1 If Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 1).Value = SokuteiPointNumber Then ' FIRST ROW SOLUTION FOR X TOLERANCE. If Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 2).Value = "" Then ' DO NOTHING, THIS MEANS WE WANT DEFAULT TO BE LAST NON "" COLUMN FOR XTIR Else XTIR = TeishutsuItemRow End If ' FIRST ROW SOLUTION FOR Y TOLERANCE. If Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 3).Value = "" Then ' DO NOTHING, THIS MEANS WE WANT DEFAULT TO BE LAST NON "" COLUMN FOR YTIR Else YTIR = TeishutsuItemRow End If ' FIRST ROW SOLUTION FOR L TOLERANCE. If Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 4).Value = "" Then ' DO NOTHING, THIS MEANS WE WANT DEFAULT TO BE LAST NON "" COLUMN FOR LTIR Else LTIR = TeishutsuItemRow End If Else ' THIS IS NOT A SOKUTEI POINT ROW, MOVE ALONG. TeishutsuItemRow = TeishutsuItemRow + 1 TempLoop = TempLoop - 1 End If Next TempLoop ' NOW LET'S SOLVE FOR THE TOLERANCE VALUES OF X AND Y WHICH ARE FAIRLY STRAIGHTFORWARD ToleranceStringX = Sheets("提出用").Cells(XTIR, TeishutsuItemColumn + 2).Value ToleranceStringY = Sheets("提出用").Cells(YTIR, TeishutsuItemColumn + 3).Value ToleranceValueX = LastNumber(ToleranceStringX) ToleranceValueY = LastNumber(ToleranceStringY) ToleranceValueX = Val(ToleranceValueX) ToleranceValueY = Val(ToleranceValueY) ' INPUT IN THE CONDITIONAL FORMATTING FOR X AND Y TOLERANCES Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 5)..Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=ToleranceValueX + 0.008 Selection.FormatConditions(1).Interior.ColorIndex = 48 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=ToleranceValueX - 0.008 With Selection.FormatConditions(2).Font .Bold = False .Italic = True End With Selection.FormatConditions(2).Interior.ColorIndex = 48 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:=ToleranceValueX - 0.008, _ Formula2:=ToleranceValueX + 0.008 Selection.FormatConditions(3).Interior.ColorIndex = xlNone Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 6)..Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=ToleranceValueY + 0.008 Selection.FormatConditions(1).Interior.ColorIndex = 48 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=ToleranceValueY - 0.008 With Selection.FormatConditions(2).Font .Bold = False .Italic = True End With Selection.FormatConditions(2).Interior.ColorIndex = 48 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:=ToleranceValueY - 0.008, _ Formula2:=ToleranceValueY + 0.008 Selection.FormatConditions(3).Interior.ColorIndex = xlNone ' SOLVE FOR L WHICH WILL EITHER USE +- OR /. If Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 4).Value = "" Then ' DO NOTHING, THIS MEANS WE WANT DEFAULT TO BE LAST NON "" COLUMN FOR LTIR Else LTIR = TeishutsuItemRow End If ToleranceStringL = Sheets("提出用").Cells(LTIR, TeishutsuItemColumn + 4).Value If InStr(ToleranceStringL, Separator1) Then ' THIS IS WITH / ' FIRST FIND THE BASE THEN REMOVE IT FROM THE STRING LValueBase = FirstNumber(ToleranceStringL) ToleranceStringL = Right$(ToleranceStringL, Len(ToleranceStringL) - Len(LValueBase)) LValueBase = Val(LValueBase) 'ToleranceValuePML = Right(ToleranceValueX, Len(ToleranceValueX) - InStrRev(ToleranceValueX, Separator2) - 1) For Position = (InStr(ToleranceStringL, Separator1) + 1) To Len(ToleranceStringL) Step 1 If InStr(NumericCharacters, Mid(ToleranceStringL, Position, 1)) Then ToleranceValueML = ToleranceValueML & Mid(ToleranceStringL, Position, 1) ' NOW A CHECK TO SEE IF ToleranceValueML HAS A MINUS SIGN If InStr(ToleranceValueML, MinusSign) Then ToleranceValueML = Right$(ToleranceValueML, Len(ToleranceValueML) - 1) End If End If Next Position For Position = 1 To (InStr(ToleranceStringL, Separator1) - 1) Step 1 If InStr(NumericCharacters, Mid(ToleranceStringL, Position, 1)) Then ToleranceValuePL = ToleranceValuePL & Mid(ToleranceStringL, Position, 1) End If Next Position ' THIS IS TO ADD AND FIND HIGH/LOW FOR L TOLERANCE ToleranceValueLHigh = Val(LValueBase) + Val(ToleranceValuePL) ToleranceValueLLow = Val(LValueBase) - Val(ToleranceValueML) ' LASTLY SET TO VALUE TO USE IN CONDITIONAL FORMATTING ToleranceValueLHigh = Val(ToleranceValueLHigh) ToleranceValueLLow = Val(ToleranceValueLLow) End If If InStr(ToleranceStringL, Separator2) Then ' THIS IS WITH +- LValueBase = FirstNumber(ToleranceStringL) ToleranceValuePML = LastNumber(ToleranceStringL) ' THIS IS IN ORDER TO REMOVE THE +- SYMBOL ToleranceValuePML = Right$(ToleranceValuePML, Len(ToleranceValuePML) - 1) ' THIS IS TO ADD AND FIND HIGH/LOW FOR L TOLERANCE ToleranceValueLHigh = Val(LValueBase) + Val(ToleranceValuePML) ToleranceValueLLow = Val(LValueBase) - Val(ToleranceValuePML) ' LASTLY SET TO VALUE TO USE IN CONDITIONAL FORMATTING ToleranceValueLHigh = Val(ToleranceValueLHigh) ToleranceValueLLow = Val(ToleranceValueLLow) End If ' NOW INPUT CONDITIONAL FORMATTING Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 7)..Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=ToleranceValueLHigh Selection.FormatConditions(1).Interior.ColorIndex = 48 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=ToleranceValueLLow With Selection.FormatConditions(2).Font .Bold = False .Italic = True End With Selection.FormatConditions(2).Interior.ColorIndex = 48 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:=ToleranceValueLLow, _ Formula2:=ToleranceValueLHigh Selection.FormatConditions(3).Interior.ColorIndex = xlNone ' NEXT ROW INDICATOR, POINT NUMBER ADDITION TeishutsuItemRow = TeishutsuItemRow + 1 SokuteiPointNumber = SokuteiPointNumber + 1 Loop End Sub Function LastNumber(ToleranceString) As String Dim Newstring As String Dim SpacePlace As Integer Dim RevOut As String Newstring = StrReverse(ToleranceString) SpacePlace = InStr(1, Newstring, " ") RevOut = Left(Newstring, SpacePlace - 1) LastNumber = StrReverse(RevOut) End Function Function FirstNumber(ToleranceString) As String Dim Newstring As String Dim SpacePlace As Integer Dim RevOut As String Newstring = ToleranceString SpacePlace = InStr(1, Newstring, " ") RevOut = Left(Newstring, SpacePlace - 1) FirstNumber = RevOut End Function |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Number Inside a Cell
Note here that with Rick's solution, I hadn't put in conditional
formatting yet. This is really quite easy, the tough part was getting the max/min (or high/low) to get a correct readout. I still need to sit down and look at the code to understand it. Maybe I'll try to think of some similar type program to write and try out to test myself afterwards. Regardless, in the future a question or two may come, but for now, here's Rick's solution laid out. ------ Rick's SOLUTION Style -------- Sub RCVV_WP() Dim TeishutsuItemRow As Long Dim TeishutsuItemColumn As Long Dim PrevXTIR As Long Dim PrevYTIR As Long Dim PrevLTIR As Long Dim SokuteiPointNumber As Long Dim HighestPointNumber As Long Dim SolutionTolerance As Long Dim ToleranceString As String Dim ToleranceValue As String Dim LValueBase As String Dim TempLoop As Long Const Separator1 = "/" Const Separator2 = "±" Const StanleyText = " ST " Const NumericCharacters = "+-0123456789" Dim Position As Integer Dim High As String Dim Low As String ' SET THE VARIABLES FOR TEISHUTSUROW AND COLUMN. ' ALSO SET PREVIOUS X,Y,L ITEM ROWS IN THE EVENT REPEATED (BLANK) TOLERANCE ENTRY. ' SOKUTEI AND HIGHEST POINT NUMBERS SET TO 1. TeishutsuItemRow = 3 TeishutsuItemColumn = 8 PrevXTIR = 0 PrevYTIR = 0 PrevLTIR = 0 SokuteiPointNumber = 1 HighestPointNumber = 1 For TempLoop = 3 To 300 If Sheets("提出用").Cells(TempLoop, TeishutsuItemColumn + 1).Value HighestPointNumber Then HighestPointNumber = Sheets("提出用").Cells(TempLoop, TeishutsuItemColumn + 1).Value End If Next TempLoop 'MsgBox "HighestPointNumber = " & (HighestPointNumber) ' LOOP FOR ALL SOKUTEI POINTS. Do Until SokuteiPointNumber = HighestPointNumber + 1 ' RESET VALUES FOR TOLERANCE ToleranceValue = "" ' FIRST MATCH UP SOKUTEI POINT WITH ROW ON 提出用 SHEET, AND SOLVE FOR XYL TOLERANCES. For TempLoop = 0 To 2 ToleranceString = Sheets("提出用").Cells(TeishutsuItemRow, TeishutsuItemColumn + 2 + TempLoop).Value MsgBox "ToleranceString = " & (ToleranceString) High = MaxTol(ToleranceString) Low = MinTol(ToleranceString) MsgBox "High = " & (High) MsgBox "Low = " & (Low) Next TempLoop Loop End Sub Function MaxTol(VarTol As String) As Variant Dim Parts() As String On Error GoTo BadFormat If VarTol Like "* [Ss][Tt] *" Then MaxTol = CDbl(Split(VarTol, "ST", , vbTextCompare)(1)) Exit Function End If If VarTol Like "* ±*" Then Parts = Split(VarTol, "±") MaxTol = CDbl(Parts(0)) + CDbl(Parts(1)) Exit Function End If If VarTol Like "* */*" Then MaxTol = CDbl(Split(VarTol)(0)) + CDbl(Split(Split(VarTol)(1), "/") (0)) Exit Function End If BadFormat: MaxTol = "#BADFORMAT!" End Function Function MinTol(VarTol As String) As Variant Dim Parts() As String On Error GoTo BadFormat If VarTol Like "* [Ss][Tt] *" Then MinTol = CDbl(Split(VarTol, "ST", , vbTextCompare)(1)) Exit Function End If If VarTol Like "* ±*" Then Parts = Split(VarTol, "±") MinTol = CDbl(Parts(0)) - CDbl(Parts(1)) Exit Function End If If VarTol Like "* */*" Then MinTol = CDbl(Split(VarTol)(0)) + CDbl(Split(Split(VarTol)(1), "/") (1)) Exit Function End If BadFormat: MinTol = "#BADFORMAT!" End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Cell that changes from a Positive to a Negitive number | Excel Worksheet Functions | |||
Sum x number of right cells after finding first cell more than 0. | Excel Worksheet Functions | |||
Finding the Row number based on contents of a cell | Excel Worksheet Functions | |||
finding a particular cell in another worksheet whose row number ch | Excel Discussion (Misc queries) | |||
Page Number inside Cell? | Excel Discussion (Misc queries) |