comparing a number in a string

[kolarbr]

I want to compare a number in some text box to another number that is in a string. For example I have a "control" number that needs to be compared to all the other numbers in the column and if its greater than the control number, then change the fill color in the box to another color

Control #
0.33

Rest of Column
0.041 J
<0.5
NA
0.51
0.02 J etc...

Is there a way I can compare the control number to the each of the numbers in the rest of the column without changing or parsing that value? I know how to change the color and loop but don't know how to do the compare without changing anything or making a new column and hiding it. Thanks!!!!!!!!

 

[machopicho]

You can use a VBA code with 2 For... Next cicle to compare Control number and other numbers in column

 

[kolarbr]

I don't understand...I know I need to use VBA code, can you provide?

 

[machopicho]

I supposed your column is A and your Ceck number is in B1. In a Vba module copy this code:

Function EXTRACT_NUMBERS(s As Range) As Double
Dim T As String
Dim C As String
Dim i As Integer
Dim IsNumber As Boolean
Application.Volatile True
T = s.Value
C = ""
IsNumber = False
For i = 1 To Len(T)
If Mid(T, i, 1) = "," And IsNumber = True Then
C = C + ","
IsNumber = False
End If
On Error Resume Next
If Mid(T, i, 1) <= "9" And Mid(T, i, 1) >= "0" Then
C = C + Mid(T, i, 1)
IsNumber = True
Else
IsNumber = False
End If
Next i
EXTRACT_NUMBERS = CDbl(C)
On Error GoTo 0
End Function
 
------------------------
 
Sub test()
n = Range("A" & Rows.Count).End(xlUp).Row
For r = 1 To n
  If EXTRACT_NUMBERS(Range("A" & r)) > Range("B1") Then
     Range("A" & r).Interior.ColorIndex = 3
  End If
Next
End Sub

Run Sub test.
Enjoy!!

 

[Scott Huish]

In your rest of column Example, 0.041 J
Is the J in the same cell as the 0.041 or a Separate column?

 

[kolarbr]

It's the same column...There are no longer any characters before the number it is simply: a number only, or a number followed by some letter(s) with a space in between

ex.
0.041
0.041 J
0.041 UJ
0.041 JU
0.041 U

 

[kolarbr]

@Hotpepper Thanks for the reply but it did not help. I could not incorporate that into the code i already had. The value in which it starts is not "column A" it is defined in my array that I made so it varies...Cells(i, j).Value
Thanks though

 

[kolarbr]

Here is what I have so far...

Sub Highlight()

Dim LastCol As Integer
Dim LastRow As Integer


'Find the last used column in a Row
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

'Find the last used row in a Column
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


For i = 1 To LastCol
If Cells(1, i).Value = "1,1,1-Trichloroethane" Then
For j = 1 To LastRow
'Do some compare here
'The below line obviously doesn't work because the value contains a character
If Cells(j, i).Value > 0.33 Then
'Highlight Cell Some color
End If
Next j
End If
Next i

End Sub

 

[kolarbr]

Can anyone see if they can fix the "find" function code on this...to my understanding vba code for the find function is different than if you type it into a cell in the document. Thanks!

Sub Highlight()

Dim LastCol As Integer
Dim LastRow As Integer
Dim FindSpace As Integer
Dim Value As Integer


'Find the last used column in a Row
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

'Find the last used row in a Column
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


For i = 1 To LastCol
If Cells(1, i).Value = "1,1,1-Trichloroethane" Then
For k = 1 To LastRow
'Used the -1 to get the value I need for the Left function to work properly
FindSpace = Find(" ", Cells(j, i).Value, 1) - 1
Value = Left(Cells(k, i).Value, FindSpace)
If Value > 0.33 Then
Cells(k, i).Interior.Color = 15
End If
Next k
End If
Next i

End Sub

 

[kolarbr]

Well thanks for trying everybody but I figured it out. For your future reference, turns out there is a built in function "val()" that returns the numbers in a string. You can simply save that to a variable and do the compare easily without changing the data in the cell. Who'd a thunk it...