Comparing Floating Point For Greater Than or Equal To

[dbmathis]

Hi All,

I am working with rating scales and I need to be able to check to see if a floating point number is >= another floating point number with VBA. I only need 2 decimal places for my comparisons. I

For example I on my worksheet I have two different cells that have 40 in them. and do >= it returns false.

Code looks like:

    For rating_scale_range = ws2.Cells(Rows.Count, numeric_value_column).End(xlUp).Row To row_number_rating_scale Step -1
        If ws2.Cells(row_number_ratings, raw_normalized_rating_column) >= ws2.Cells(rating_scale_range, normalized_threshold_value_column) Then
            ' Numeric
            ws2.Cells(row_number_ratings, calculated_manager_numeric_rating_column) = ws2.Cells(rating_scale_range, numeric_value_column)
            ' Normalized
            ws2.Cells(row_number_ratings, calculated_manager_normalized_rating_column) = ws2.Cells(rating_scale_range, normalized_value_column): Exit For
        End If
    Next

Is there a way to do this?

 

[Rick Rothstein]

If ws2.Cells(row_number_ratings, raw_normalized_rating_column) >= ws2.Cells(rating_scale_range, normalized_threshold_value_column) Then

Don't test the floating point numbers directly, round the values to two decimal places and test that instead...

If Round(ws2.Cells(row_number_ratings, raw_normalized_rating_column), 2) >= Round(ws2.Cells(rating_scale_range, normalized_threshold_value_column), 2) Then

 

[dbmathis]

I tried that, along with WorksheetFunction.Round and it didn't make a difference.

 

[Rick Rothstein]

I tried that, along with WorksheetFunction.Round and it didn't make a difference.

In that case, I think one of your "numbers" is actually text while the other is a real number. If I am right, then you are asking if 40="40" which is not true. If I am right, try wrapping your values in a CDbl function call (and still use the Round function as well)...

If Round(CDbl(ws2.Cells(row_number_ratings, raw_normalized_rating_column)), 2) >= Round(CDbl(ws2.Cells(rating_scale_range, normalized_threshold_value_column)), 2) Then

 

[dbmathis]

I can't reproduce it right now so I will keep an eye on this and post back if I see it again.

 

[mikerickson]

Try

If Abs(Val(CStr(oneCell)) - Val(CStr(otherCell))) <= 0.01 Then