| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-update of formula results
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-update of formula results
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" wrote in message om... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-update of formula results
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-update of formula results
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" wrote in message . com... 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-update of formula results
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" wrote in message ... 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" wrote in message . com... 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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-update of formula results
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" wrote in message ... 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" wrote in message . com... 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 . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto-update of formula results
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" wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto-update column formula | Excel Discussion (Misc queries) | |||
| My cell formula results won't update automatically. Help? | Excel Discussion (Misc queries) | |||
| A formula to auto-update a classification ONLY if it goes UP | Excel Worksheet Functions | |||
| formuls results won't update automatically (auto is turned on) | Excel Worksheet Functions | |||
| Can I disable auto formula update? | New Users to Excel | |||
Linear Mode
