evaluation of a cell with a formula

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

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(A42>4000,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)
 
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.
 
Thank you Dave.
________________
Dave Peterson said:
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.
Click to expand...
 
Back
Top