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

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

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