Force Cells To Recalculate (simulate Double Click?)

trphilli · May 3rd 2007

Hello again,

I have an interesting question for everybody here. Let me see how to best explain it.

I start in the code making sure column Action Plan completion has a value in it. Copying in the issue modified column if neccesary.

Code

'Back fill last modified
   'I need to start with the first data row (3) and the action plan completion column
   Let intcurrentrow = 3
   Let intcurrentcol = intactionplancompletion
   Do Until intcurrentrow > intmaxrow
     If Cells(intcurrentrow, intcurrentcol).Value = "" Then
        Let Cells(intcurrentrow, intcurrentcol).Value = Cells(intcurrentrow, intissuemodified).Value
        'Need to ensure that everything is formatted as a date
        Let Cells(intcurrentrow, intcurrentcol).NumberFormat = "m/d/yy h:mm AM/PM"
     End If
     Let intcurrentrow = intcurrentrow + 1
   Loop

Display More

Later, I place this formula in a given cell.

Code

=IF(I9="Open-Remediated",M9-K9, IF(I9="Closed-Remediated and Validated",M9-K9,"" ))

The info I copied above is placed into the column K.

When I run the macro, the cell formula outputs "#value".

However, if I go double-click then enter the cell calculates just fine.

I've tried excels overall recalculate function, but that doesn't change this.

Does anybody know how to simulate a double-click on a cell?

Any other suggestions?

Thanks,

Tom

shg · May 3rd 2007

Re: Force Cells To Recalculate (simulate Double Click?)

I think we'd be interested first in seeing the rest of your code.

Craig Ottley · May 3rd 2007

Re: Force Cells To Recalculate (simulate Double Click?)

Code

Application.Volatile

Quote

Volatile Optional Variant. True to mark the function as volatile. False to mark the function as nonvolatile. The default value is True

JimFuller1 · May 3rd 2007

Re: Force Cells To Recalculate (simulate Double Click?)

Plus, you should be sure you're using a statement like unto this:

Code

ActiveCell.FormulaR1C1 = "=IF(Blah,Blah,Blah)"

or else you could be writing text to the cell which requires Excel to "RE" evaluate the cell (the double clicking).

Jim

Anonymous · May 3rd 2007

Re: Force Cells To Recalculate (simulate Double Click?)

Jim, Please use code tags for all VBA code.

trphilli · May 3rd 2007

Re: Force Cells To Recalculate (simulate Double Click?)

Thanks for the comments. I'm not sure function.volatile is the right approach here since the recalculation is in the excel cell, not the vba module. but I could be wrong. For those who wanted more code, here is the code I use to assemble all the formulas. It is much more expansive than section that causes the error. The formula that has the problem is strformhistoricalunremed.

Any additional thoughts?

Thanks,

Tom

Code

'Assemble formulas
   'Okay so I don't really need these formulas, but it's a good copy paste exercise
  Let strOAPND = "Open-Action Plan Not Developed"
  Let strOAPD = "Open-Action Plan Developed"
  Let strOR = "Open-Remediated"
  Let strCRAV = "Closed-Remediated and Validated"
  Let strempty = ""


  Let intcurrentrow = 3
  
  Let vartemp1 = strissuestatus & intcurrentrow    'Issue Status Cell in A1 Notation
  Let vartemp2 = strissuecreated & intcurrentrow   'Issue Created Date Cell in A1 Notation
  Let vartemp3 = stractionplancompletion & intcurrentrow 'Action Plan completion Date cell in A1 Notation
  Let vartemp4 = strissuemodified & intcurrentrow  'Issue Modified date cell in A1 notation
  
  Let strformcurrentunremed = "=IF(" & vartemp1 & "=""Open-Action Plan Not Developed""," & vardate & "-" & vartemp2 & ", if(" & vartemp1 & "=""Open-Action Plan Developed""," & vardate & "-" & vartemp2 & ","""" ))"
  Let strformhistoricalunremed = "=IF(" & vartemp1 & "=""Open-Remediated""," & vartemp3 & "-" & vartemp2 & ", if(" & vartemp1 & "=""Closed-Remediated and Validated""," & vartemp3 & "-" & vartemp2 & ","""" ))"
  
  'These two formulas were copied from above.  They still need to be revised into the actual formulas.
  
  Let strformcurrentopen = "=IF(" & vartemp1 & "=""Open-Action Plan Not Developed""," & vardate & "-" & vartemp2 & ", if(" & vartemp1 & "=""Open-Action Plan Developed""," & vardate & "-" & vartemp2 & ", if(" & vartemp1 & "=""Open-Remediated""," & vardate & "-" & vartemp2 & ","""" )))"
  Let strformhistoricalopen = "=IF(" & vartemp1 & "=""Closed-Remediated and Validated""," & vartemp4 & "-" & vartemp2 & ","""")"
  
'Place into cells


  Let Cells(intcurrentrow, intcurrentunremed).Value = strformcurrentunremed
  Let Cells(intcurrentrow, inthistoricalunremed).Value = strformhistoricalunremed
  Let Cells(intcurrentrow, intcurrentopen).Value = strformcurrentopen
  Let Cells(intcurrentrow, inthistoricalopen).Value = strformhistoricalopen
  
'Copy down
  Let vartemp1 = strcurrentunremed & "3"
  Let vartemp2 = strhistoricalopen & "3"
  Let vartemp3 = vartemp1 & ":" & vartemp2
  Let vartemp4 = vartemp1 & ":" & strhistoricalopen & intmaxrow
  
  Range(vartemp3).Select
  Selection.Copy
  
  Range(vartemp4).Select
  ActiveSheet.Paste
End Sub

Display More

shg · May 3rd 2007

Re: Force Cells To Recalculate (simulate Double Click?)

Tom,

You might try these, in decreasing order of preference:

Code

Range(problemRange).Calculate
    Application.CalculateFull
    Application.CalculateFullRebuild

Force Cells To Recalculate (simulate Double Click?)

Participate now!

Share

Users Viewing This Thread