Hello and Merry Christmas to all
How can I evaluate cells that contain formula? Even if the cell does
not show a result, it is not empty and applying or < is of no use?

My cells contain a formula like: =IF(AND(A424000,A42<9000),I42,"") and I
would like to be able to evaluate and compare them for further action. Like
this (which is not working):
If Not IsEmpty(thisCell) Or Not thisCell.Value = 0# Then
getThisPlanNumberAmount = thisCell.Value
ElseIf thisCell.Value thisCell.Offset(0, 1).Value Then
getThisPlanNumberAmount = thisCell.Offset(0, 1).Value
Else
...................

Thank you

Hi Daniel,

A range has a HasFormula property which you can test like so

If ActiveCell.HasFormula Or Not IsEmpty(ActiveCell.Value) Then
MsgBox "hello"
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Daniel" wrote in message
...
Hello and Merry Christmas to all
How can I evaluate cells that contain formula? Even if the cell does
not show a result, it is not empty and applying or < is of no use?

My cells contain a formula like: =IF(AND(A424000,A42<9000),I42,"") and I
would like to be able to evaluate and compare them for further action.
Like
this (which is not working):
If Not IsEmpty(thisCell) Or Not thisCell.Value = 0# Then
getThisPlanNumberAmount = thisCell.Value
ElseIf thisCell.Value thisCell.Offset(0, 1).Value Then
getThisPlanNumberAmount = thisCell.Offset(0, 1).Value
Else
..................

Thank you

Sometimes if you write your if/thens as separate lines, it'll be easier to break
it down to do what you want:


If Trim(myCell.Value) = "" Then
'either it was outside 4000-9000 or I42 contained ""
Else
If IsNumeric(myCell.Value) Then
If IsNumeric(myCell.Offset(0, 1).Value) Then
'both numeric, so do your comparison
If myCell.Value myCell.Offset(0, 1).Value Then
getThisPlanNumberAmount = myCell.Offset(0, 1).Value
Else
'what happens here
End If
Else
'what happens if myCell is a number, but .offset(0,1) isn't
End If
Else
'what happens if myCell.value is not numeric
End If
End If


There's a lot of "what happens here" lines that you could probably eliminate if
you included And/Or's in your if/then statements.

And one more thing ThisCell is actually a reserved word in some versions of
excel (I think it was added in xl2k--it's in xl2002 for sure).

It refers to the cell that a user defined function was called from.

You may want to change your variable name to something else. I used myCell.




Daniel wrote:

Hello and Merry Christmas to all
How can I evaluate cells that contain formula? Even if the cell does
not show a result, it is not empty and applying or < is of no use?

My cells contain a formula like: =IF(AND(A424000,A42<9000),I42,"") and I
would like to be able to evaluate and compare them for further action. Like
this (which is not working):
If Not IsEmpty(thisCell) Or Not thisCell.Value = 0# Then
getThisPlanNumberAmount = thisCell.Value
ElseIf thisCell.Value thisCell.Offset(0, 1).Value Then
getThisPlanNumberAmount = thisCell.Offset(0, 1).Value
Else
..................

Thank you

--

Dave Peterson

Thank you Dave.
________________
"Dave Peterson" a écrit dans le message de
...
Sometimes if you write your if/thens as separate lines, it'll be easier to
break
it down to do what you want:


If Trim(myCell.Value) = "" Then
'either it was outside 4000-9000 or I42 contained ""
Else
If IsNumeric(myCell.Value) Then
If IsNumeric(myCell.Offset(0, 1).Value) Then
'both numeric, so do your comparison
If myCell.Value myCell.Offset(0, 1).Value Then
getThisPlanNumberAmount = myCell.Offset(0, 1).Value
Else
'what happens here
End If
Else
'what happens if myCell is a number, but .offset(0,1) isn't
End If
Else
'what happens if myCell.value is not numeric
End If
End If


There's a lot of "what happens here" lines that you could probably
eliminate if
you included And/Or's in your if/then statements.

And one more thing ThisCell is actually a reserved word in some versions
of
excel (I think it was added in xl2k--it's in xl2002 for sure).

It refers to the cell that a user defined function was called from.

You may want to change your variable name to something else. I used
myCell.




Daniel wrote:

Hello and Merry Christmas to all
How can I evaluate cells that contain formula? Even if the cell
does
not show a result, it is not empty and applying or < is of no use?

My cells contain a formula like: =IF(AND(A424000,A42<9000),I42,"") and
I
would like to be able to evaluate and compare them for further action.
Like
this (which is not working):
If Not IsEmpty(thisCell) Or Not thisCell.Value = 0# Then
getThisPlanNumberAmount = thisCell.Value
ElseIf thisCell.Value thisCell.Offset(0, 1).Value Then
getThisPlanNumberAmount = thisCell.Offset(0, 1).Value
Else
..................

Thank you

--

Dave Peterson