Auto-update of formula results

  • Thread starter Thread starter Shetty
  • Start date Start date
S

Shetty

Hi All,
I have a code which enters the formula in the different cells based on
the colour index. My problem is the formula results are not updated
automaticaly based on the new colour. If I run the macro again, it is
updated and if I press F2 and Enter, it gets updated. My code is as
below.

================
Sub Conditional_Formula_Entry()

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

ActiveCell.FormulaR1C1 = "=showrgb(RC[2])"

ActiveCell.Offset(1, 0).Select

Loop

End Sub
==================
NOte : ShowRGB is UDF and working fine in the same workbook.

I need to update automatically just like sum or any other formulas.

Can somebody point out my mistakes or any additions to the codde?

Regards,
Shetty
 
Formulas are updated when Excel does a Calculation. Changing the colorindex
does not cause a calculation. You would need to make your formula volatile

Application.Volatile as the first line after the declaration in the UDF

Then force a calculation when you want the formulas to update.
 
Add Application.Volatile to your function...then it will
update every tiem the sheet recalcs

Sub Conditional_Formula_Entry()

With Range(ActiveCell, _
ActiveCell.Offset(0, 1).End(xlDown).Offset(0, -1))

.FormulaR1C1 = "=showrgb(RC[2])"

End With
End Sub

Function showrgb(x)
Application.Volatile
End Function
 
Thanks Tom,
I have added application.volatile as you have suggested
and then tried F9 as well as tools/options/calc sheet and
tools/options/calc sheet but it is still showing the same
results. More suggetions please.
Regards,
Shetty
-----Original Message-----
Formulas are updated when Excel does a Calculation. Changing the colorindex
does not cause a calculation. You would need to make your formula volatile

Application.Volatile as the first line after the declaration in the UDF

Then force a calculation when you want the formulas to update.

--
Regards,
Tom Ogilvy

Shetty said:
Hi All,
I have a code which enters the formula in the different cells based on
the colour index. My problem is the formula results are not updated
automaticaly based on the new colour. If I run the macro again, it is
updated and if I press F2 and Enter, it gets updated. My code is as
below.

================
Sub Conditional_Formula_Entry()

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

ActiveCell.FormulaR1C1 = "=showrgb(RC[2])"

ActiveCell.Offset(1, 0).Select

Loop

End Sub
==================
NOte : ShowRGB is UDF and working fine in the same workbook.

I need to update automatically just like sum or any other formulas.

Can somebody point out my mistakes or any additions to the codde?

Regards,
Shetty
Click to expand...


.
Click to expand...
 
It shows the same result on a recalc, but if you select the cell, do F2 to
edit, then hit enter, it changes?

Setting the function to volatile causes it to be recalced on each
calculation. Beyond that, there are not other options unless you want to
use the Edit=>replace functionality to replace the equal sign in the formula
with an equal sign to simulate editing the cell.

--
Regards,
Tom Ogilvy

Shetty said:
Thanks Tom,
I have added application.volatile as you have suggested
and then tried F9 as well as tools/options/calc sheet and
tools/options/calc sheet but it is still showing the same
results. More suggetions please.
Regards,
Shetty
-----Original Message-----
Formulas are updated when Excel does a Calculation. Changing the colorindex
does not cause a calculation. You would need to make your formula volatile

Application.Volatile as the first line after the declaration in the UDF

Then force a calculation when you want the formulas to update.

--
Regards,
Tom Ogilvy

Shetty said:
Hi All,
I have a code which enters the formula in the different cells based on
the colour index. My problem is the formula results are not updated
automaticaly based on the new colour. If I run the macro again, it is
updated and if I press F2 and Enter, it gets updated. My code is as
below.

================
Sub Conditional_Formula_Entry()

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

ActiveCell.FormulaR1C1 = "=showrgb(RC[2])"

ActiveCell.Offset(1, 0).Select

Loop

End Sub
==================
NOte : ShowRGB is UDF and working fine in the same workbook.

I need to update automatically just like sum or any other formulas.

Can somebody point out my mistakes or any additions to the codde?

Regards,
Shetty
Click to expand...


.
Click to expand...
Click to expand...
 
Yes. It changes with select the cell, do F2 to edit, then
hit enter. Since no other option is available , I will
follow the find = and replace all with = to update. Is it
possible with a macro? I will run the macro for with event
that will fir with the change in any cell in column B.

Thanks again.
Shetty

-----Original Message-----
It shows the same result on a recalc, but if you select the cell, do F2 to
edit, then hit enter, it changes?

Setting the function to volatile causes it to be recalced on each
calculation. Beyond that, there are not other options unless you want to
use the Edit=>replace functionality to replace the equal sign in the formula
with an equal sign to simulate editing the cell.

--
Regards,
Tom Ogilvy

Shetty said:
Thanks Tom,
I have added application.volatile as you have suggested
and then tried F9 as well as tools/options/calc sheet and
tools/options/calc sheet but it is still showing the same
results. More suggetions please.
Regards,
Shetty
-----Original Message-----
Formulas are updated when Excel does a Calculation. Changing the colorindex
does not cause a calculation. You would need to make your formula volatile

Application.Volatile as the first line after the declaration in the UDF

Then force a calculation when you want the formulas to update.

--
Regards,
Tom Ogilvy

Hi All,
I have a code which enters the formula in the
Click to expand...
different
cells based on
the colour index. My problem is the formula results
Click to expand...
are
not updated
automaticaly based on the new colour. If I run the macro again, it is
updated and if I press F2 and Enter, it gets updated. My code is as
below.

================
Sub Conditional_Formula_Entry()

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

ActiveCell.FormulaR1C1 = "=showrgb(RC[2])"

ActiveCell.Offset(1, 0).Select

Loop

End Sub
==================
NOte : ShowRGB is UDF and working fine in the same workbook.

I need to update automatically just like sum or any other formulas.

Can somebody point out my mistakes or any additions
Click to expand...
to
the codde?
Regards,
Shetty


.
Click to expand...
Click to expand...


.
Click to expand...
 
Thanks Patrick.
Now the results are updated every time I press F9. I have added
application.volatile in the function as shown by you.

Tom Ogilvy :
My apology. You also gave me the same solution (adding
application.volatile to UDF) But by mistake I added it to the macro
code. When I saw the code by Patrick, I realise my mistake. Sorry
again and Thanks.

Regards,
Shetty

Patrick Molloy said:
Add Application.Volatile to your function...then it will
update every tiem the sheet recalcs

Sub Conditional_Formula_Entry()

With Range(ActiveCell, _
ActiveCell.Offset(0, 1).End(xlDown).Offset(0, -1))

.FormulaR1C1 = "=showrgb(RC[2])"

End With
End Sub

Function showrgb(x)
Application.Volatile
End Function
-----Original Message-----
Hi All,
I have a code which enters the formula in the different cells based on
the colour index. My problem is the formula results are not updated
automaticaly based on the new colour. If I run the macro again, it is
updated and if I press F2 and Enter, it gets updated. My code is as
below.

================
Sub Conditional_Formula_Entry()

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

ActiveCell.FormulaR1C1 = "=showrgb(RC[2])"

ActiveCell.Offset(1, 0).Select

Loop

End Sub
==================
NOte : ShowRGB is UDF and working fine in the same workbook.

I need to update automatically just like sum or any other formulas.

Can somebody point out my mistakes or any additions to the codde?

Regards,
Shetty
.
Click to expand...
Click to expand...
 
Back
Top