Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula result and displayed result are different | Excel Worksheet Functions | |||
Formula result does not match displayed result | Excel Worksheet Functions | |||
Formula Bar F9 Result differs from cell result??? | Excel Worksheet Functions | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
Median result used in formula gives incorrect result | Excel Worksheet Functions |