Value not quite the same as formula result
 

I'm struggling with the following. I have a template with cell E2 containing
a formula as follows;

=IF(D2="","New",IF(D2<=0.25,"Selected","Not selected"))

For new workbooks, D2 is always blank, so the formula returns "New".

In the workbook_open macro, I have a section

If Range("E2").Value = "New" Then
Randomize
Range("D2").Value = Rnd()
End If

The problem is that sometimes this works, and sometimes it doesn't. When it
doesn't, I put the same formula into a different cell, shift the cell to E2 -
and hey presto the macro works. But I've already gone through several cycles
of this, where I make a small unconnected design change, and next time of
opening, it just stops working. Somehow (sometimes) the formula is returning
something that is not quite recognizable by the macro, or the macro is not
evaluating it quite right.

Is there a different way of expressing either the formula or macro that is
more reliable? I can't release the sheet to users as it stands, and it's very
frustrating!

Many thanks,
Geoff.

Value not quite the same as formula result
 

Many thanks, so far so good - difficult to know if it's a permanent fix
unless it stops again!

"Tom Ogilvy" wrote:

Perhaps you need to qualify your range

With thisworkbook.Worksheets(1)
If len(trim( .Range("D2").Text)) = 0 Then
Randomize
.Range("D2").Value = Rnd()
End If
End With

--
Regards,
Tom Ogilvy


"Geoff C" wrote in message
...
I'm struggling with the following. I have a template with cell E2
containing
a formula as follows;

=IF(D2="","New",IF(D2<=0.25,"Selected","Not selected"))

For new workbooks, D2 is always blank, so the formula returns "New".

In the workbook_open macro, I have a section

If Range("E2").Value = "New" Then
Randomize
Range("D2").Value = Rnd()
End If

The problem is that sometimes this works, and sometimes it doesn't. When
it
doesn't, I put the same formula into a different cell, shift the cell to
E2 -
and hey presto the macro works. But I've already gone through several
cycles
of this, where I make a small unconnected design change, and next time of
opening, it just stops working. Somehow (sometimes) the formula is
returning
something that is not quite recognizable by the macro, or the macro is not
evaluating it quite right.

Is there a different way of expressing either the formula or macro that is
more reliable? I can't release the sheet to users as it stands, and it's
very
frustrating!

Many thanks,
Geoff.