"Mark Stephens" wrote:
I have a routine that check a column of values row by row,
cell by cell for various undesirable conditions which if
it finds it ends the sub and highlights the offending cell
to allow the user to correct it. It is a series of doubles
which I have dimmed as variant, and it checks to see if its
numeric, empty or 0.5.

What has happened is that one data entry person has messed
up somewhat and entered the numbers in wrongly and to fix
it I just entered a formula in excel to turn any cells that
were too big into the right number by dividing by 100, and
if the cell was empty to give it and empty string value ("").

What is happening is that when it is evaluation an empty cell,
it is throwing an error for the condition 0.5 which is very
puzzling indeed since it is an empty string.
[....]
I cannot see why this happens as if it is a string then why
would it be evaluated as .05[.] Any ideas?

If you had included some snippets of code, it would have eliminated
ambiguities in your description and made things clearer.

On the one hand, you say "it is throwing an error for the condition 0.5".

On the other hand, you ask why "a string [...] would be evaluated as .05"
[sic].

Which is happening?

If you do:

Dim x As Variant
x = ""
Msgbox Typename(x) & vbNewLine & (x 0.5)

that displays True because any text in a Variant variable (subtype String)
is considered larger than a number, just like in Excel.

But Msgbox ("" 0.5) does cause a VBA error due to the mismatch.

I think the inconsistency arises from how VBA wants to emulate Excel when we
use Range(...), which is type Variant. But that is only guess. The fact
is: it is what it is.

You might avoid the problem by doing the following:

x = Empty ' instead of ""

Alternatively, you might avoid the problem by ordering and separating your
tests appropriately, for example:

If x < "" And WorksheetFunction.IsNumber(x) Then
If x tooBig Then x = x / 100
If x 0.5 Then
' .... whatever ....
End If
End If

FYI, I use WorksheetFunction.IsNumber instead of VBA IsNumeric because the
latter does not behave the same way.

If that does not answer your question(s), please post code snippets so we
can better understand the problem.