Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not calculate or update formula's during macro
Is there so code to keep formulas in a spreadsheet from updating? I have a
macro that changes sheet names and location of some data but I do not want the formulas referring to those sheets to update. In other words, my formula may read ='Section 2'!A2-'Section 3'!A2 Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2" and "Section 2" becomes "Section 1". After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2 I want to avoid that happening. I don't want my formula to know that my sheets renamed. Any suggestions? Thanks! Jules |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not calculate or update formula's during macro
If you want to stop your formulas from automatically calculating you can use
this code. Application.Calculation = xlCalculationManual ' your code here Application.Calculation =xlCalculationAutomatic But this will not stop the other issue you are having. Having your formulas automatically updated when a sheet is renamed is a big convience that Excel offers. I would suggest adding a sheet to your workbook in your macro. I don't know any details of your application or macro so I can't post any code or give detailed suggestions. If you need help post your macro and we can see if there is a logical way around your issue. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Jules" wrote: Is there so code to keep formulas in a spreadsheet from updating? I have a macro that changes sheet names and location of some data but I do not want the formulas referring to those sheets to update. In other words, my formula may read ='Section 2'!A2-'Section 3'!A2 Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2" and "Section 2" becomes "Section 1". After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2 I want to avoid that happening. I don't want my formula to know that my sheets renamed. Any suggestions? Thanks! Jules |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not calculate or update formula's during macro
I had a similar problem. First freeze the formulas and then un-freeze them.
So first call: Sub freezeUm() q = Chr(39) eq = "=" For Each r In ActiveSheet.UsedRange If r.HasFormula Then r.Value = q & r.Formula End If Next End Sub and after you do your thing, call: Sub unfreezeUm() eq = "=" For Each r In ActiveSheet.UsedRange If Not r.HasFormula And Left(r.Value, 1) = eq Then r.Formula = r.Value End If Next End Sub -- Gary''s Student - gsnu201001 "Jules" wrote: Is there so code to keep formulas in a spreadsheet from updating? I have a macro that changes sheet names and location of some data but I do not want the formulas referring to those sheets to update. In other words, my formula may read ='Section 2'!A2-'Section 3'!A2 Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2" and "Section 2" becomes "Section 1". After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2 I want to avoid that happening. I don't want my formula to know that my sheets renamed. Any suggestions? Thanks! Jules |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not calculate or update formula's during macro
"I see" said the blind man. Clever piece of code there. Never would have
thought of that. -- Cheers, Ryan "Gary''s Student" wrote: I had a similar problem. First freeze the formulas and then un-freeze them. So first call: Sub freezeUm() q = Chr(39) eq = "=" For Each r In ActiveSheet.UsedRange If r.HasFormula Then r.Value = q & r.Formula End If Next End Sub and after you do your thing, call: Sub unfreezeUm() eq = "=" For Each r In ActiveSheet.UsedRange If Not r.HasFormula And Left(r.Value, 1) = eq Then r.Formula = r.Value End If Next End Sub -- Gary''s Student - gsnu201001 "Jules" wrote: Is there so code to keep formulas in a spreadsheet from updating? I have a macro that changes sheet names and location of some data but I do not want the formulas referring to those sheets to update. In other words, my formula may read ='Section 2'!A2-'Section 3'!A2 Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2" and "Section 2" becomes "Section 1". After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2 I want to avoid that happening. I don't want my formula to know that my sheets renamed. Any suggestions? Thanks! Jules |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not calculate or update formula's during macro
My hero! Awesome. Thanks!!
"Gary''s Student" wrote: I had a similar problem. First freeze the formulas and then un-freeze them. So first call: Sub freezeUm() q = Chr(39) eq = "=" For Each r In ActiveSheet.UsedRange If r.HasFormula Then r.Value = q & r.Formula End If Next End Sub and after you do your thing, call: Sub unfreezeUm() eq = "=" For Each r In ActiveSheet.UsedRange If Not r.HasFormula And Left(r.Value, 1) = eq Then r.Formula = r.Value End If Next End Sub -- Gary''s Student - gsnu201001 "Jules" wrote: Is there so code to keep formulas in a spreadsheet from updating? I have a macro that changes sheet names and location of some data but I do not want the formulas referring to those sheets to update. In other words, my formula may read ='Section 2'!A2-'Section 3'!A2 Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2" and "Section 2" becomes "Section 1". After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2 I want to avoid that happening. I don't want my formula to know that my sheets renamed. Any suggestions? Thanks! Jules |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not calculate or update formula's during macro
Ok...still my hero! But I can't get this to work quite right. How did you
define your variables to make this work? Thanks! "Jules" wrote: My hero! Awesome. Thanks!! "Gary''s Student" wrote: I had a similar problem. First freeze the formulas and then un-freeze them. So first call: Sub freezeUm() q = Chr(39) eq = "=" For Each r In ActiveSheet.UsedRange If r.HasFormula Then r.Value = q & r.Formula End If Next End Sub and after you do your thing, call: Sub unfreezeUm() eq = "=" For Each r In ActiveSheet.UsedRange If Not r.HasFormula And Left(r.Value, 1) = eq Then r.Formula = r.Value End If Next End Sub -- Gary''s Student - gsnu201001 "Jules" wrote: Is there so code to keep formulas in a spreadsheet from updating? I have a macro that changes sheet names and location of some data but I do not want the formulas referring to those sheets to update. In other words, my formula may read ='Section 2'!A2-'Section 3'!A2 Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2" and "Section 2" becomes "Section 1". After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2 I want to avoid that happening. I don't want my formula to know that my sheets renamed. Any suggestions? Thanks! Jules |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not calculate or update formula's during macro
Dim eq As String
Dim q As String Dim r As Range -- Gary''s Student - gsnu201001 "Jules" wrote: Ok...still my hero! But I can't get this to work quite right. How did you define your variables to make this work? Thanks! "Jules" wrote: My hero! Awesome. Thanks!! "Gary''s Student" wrote: I had a similar problem. First freeze the formulas and then un-freeze them. So first call: Sub freezeUm() q = Chr(39) eq = "=" For Each r In ActiveSheet.UsedRange If r.HasFormula Then r.Value = q & r.Formula End If Next End Sub and after you do your thing, call: Sub unfreezeUm() eq = "=" For Each r In ActiveSheet.UsedRange If Not r.HasFormula And Left(r.Value, 1) = eq Then r.Formula = r.Value End If Next End Sub -- Gary''s Student - gsnu201001 "Jules" wrote: Is there so code to keep formulas in a spreadsheet from updating? I have a macro that changes sheet names and location of some data but I do not want the formulas referring to those sheets to update. In other words, my formula may read ='Section 2'!A2-'Section 3'!A2 Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2" and "Section 2" becomes "Section 1". After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2 I want to avoid that happening. I don't want my formula to know that my sheets renamed. Any suggestions? Thanks! Jules |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do not calculate or update formula's during macro
Ok...still my hero! But I can't get this to work quite right. How did you
define your variables to make this work? Thanks! "Gary''s Student" wrote: I had a similar problem. First freeze the formulas and then un-freeze them. So first call: Sub freezeUm() q = Chr(39) eq = "=" For Each r In ActiveSheet.UsedRange If r.HasFormula Then r.Value = q & r.Formula End If Next End Sub and after you do your thing, call: Sub unfreezeUm() eq = "=" For Each r In ActiveSheet.UsedRange If Not r.HasFormula And Left(r.Value, 1) = eq Then r.Formula = r.Value End If Next End Sub -- Gary''s Student - gsnu201001 "Jules" wrote: Is there so code to keep formulas in a spreadsheet from updating? I have a macro that changes sheet names and location of some data but I do not want the formulas referring to those sheets to update. In other words, my formula may read ='Section 2'!A2-'Section 3'!A2 Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2" and "Section 2" becomes "Section 1". After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2 I want to avoid that happening. I don't want my formula to know that my sheets renamed. Any suggestions? Thanks! Jules |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to calculate or make/update formula | Excel Programming | |||
Formula to calculate month of last status update | Excel Worksheet Functions | |||
MACRO: Add formula's across row if criteria is met | Excel Discussion (Misc queries) | |||
Update Formula's based on drop down list value | Excel Worksheet Functions | |||
Macro for setting range of Vlookup formula's | Excel Programming |