Value not quite the same as formula result

[Guest]

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.

 

[Bob Phillips]

It is not clear from your description what this Workbook_Open macro is
trying to do. Where does this code relate to the values being tested in D2,
do you always expect to se "Not Selected".

Also, did you store the code in ThisWorkbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[NickHK]

Geoff,
I do not see how you could ever obtain a value of E2="New" upon opening,
because if D2="", then it is populated with a random number, which means
that "New" cannot be returned.

NickHK

 

[Tom Ogilvy]

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

 

[Guest]

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