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

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("´£¥X¥Î").Cells(TeishutsuItemRow, TeishutsuItemColumn +
1).Value = SokuteiPointNumber Then
If Sheets("´£¥X¥Î").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("´£¥X¥Î").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¤é, ¤È«e10: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

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

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

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 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

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 -

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 -

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 -

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

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