Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 62
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding Cell that changes from a Positive to a Negitive number tpeter Excel Worksheet Functions 2 December 12th 07 07:27 PM
Sum x number of right cells after finding first cell more than 0. Learn Excel Worksheet Functions 4 December 9th 07 05:57 PM
Finding the Row number based on contents of a cell Chris Youlden Excel Worksheet Functions 5 September 6th 07 04:38 PM
finding a particular cell in another worksheet whose row number ch Andrew Excel Discussion (Misc queries) 1 September 12th 05 01:36 PM
Page Number inside Cell? mae1778 Excel Discussion (Misc queries) 3 August 13th 05 09:34 PM


All times are GMT +1. The time now is 10:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"