| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Updates Cell but not the cells that reference it with formulas
Hi,
I have a problem with cells updating after I use VBA to change a cell referenced in the formula. I use VBA to udpate a value in a cell that is used in other formulas but the formulas don't "udpate" the calculated value.... BUT when I mouseclick on the cell and press "Enter", it then recaculates the formula. The workbook is set to auto calculate so that's not the issue and if I press F9 to recalculate the workbook, it still does not update. The worksheet is protected but I don't think that is the problem. The other strange thing is that if I loop the macro twice, it seems to update all the values. It's like I'm doing something in the beginning of the code that triggers the worksheet to recalcualate, then the macro changes the values referenced in the formulas but doesn't trigger a recalc. Then when I go back and loop the macro, it does the first recalc, but doesn't at the end. Since I'm not changing the cell used in the formula, it doesn't matter if it recalculates at that point. I wonder if I'm somehow "loosing focus" of something by switching sheets or assigning the cell valute in the wrong way causing the issue. The "Redoloop" code is the loop that I added which seems to make it work in my example. Some ideas but not sure if they are the problem... I do a lot of switching between sheets.. I may be trying to overwrite a cell that is on a sheet that is not "Active" Should I be using range.value = number or range.formula = "=number" Or just range = number? I've been doing VBA in excel for quite a while and have had this come up before... just never figured it out. If you want me to explain in more detail, just let me know. I think it's just a syntax or a rule thing where I just need to write the code a little differently. Thanks! Here's my VBA.... Sub CopyFormulas() Dim baseformula As String Dim startformula As String Dim thelist As Variant redoloop = 0 starthe thelist = Range("analyzelist") Range("lasterror").Value = False startingpoint = ActiveSheet.Name Sheets("Detail").Select baseformula = Range("baseform") baseformula2 = Range("baseform2") NewFormula = "=" found = 0 Call UnprotectMe("Detail") For j = 1 To UBound(thelist, 1) If thelist(j, 2) = "x" Then If IsEmpty(thelist(j, 1)) = False Then found = found + 1 If found = 1 Then 'start base formula here. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' This could be the culprit section... or.... '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Sheets("Detail").Range("C7").Formula = "=" & Replace(baseformula, "SheetName", thelist(j, 1)) startformula = "=min(" & Replace(baseformula2, "SheetName", thelist(j, 1)) Else 'Add to base formula here On Error GoTo errorcheck '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' This could be the culprit section... or.... '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Sheets("Detail").Range("C7").Formula = Sheets("Detail").Range("C7").Formula & "+" & Replace(baseformula, "SheetName", thelist(j, 1)) startformula = startformula & "," & Replace(baseformula2, "SheetName", thelist(j, 1)) End If End If End If Next j finishsub: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' This could be the culprit section... or.... '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Range("start").Formula = startformula & ")" If found = 0 Then Range("start").Value = Now() Sheets("Detail").Range("C7").Formula = "TBD" End If Sheets("Lookups").Range("g11") = "'" & Range("C7").Formula Range("C7").Select Selection.Copy Range("C7:k244").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.NumberFormat = "$#,##0" Call ProtectMe("Detail") Sheets(startingpoint).Select redoloop = redoloop + 1 If redoloop = 2 Then Exit Sub Else GoTo starthe End If Exit Sub errorcheck: If Err.Number = 7 Then MsgBox "You passed the limit of allowable projects to calculate. Only the first " & j - 1 & " Projects are included in Detail" Range("lasterror").Value = True Else MsgBox "Error Number " & Err.Number & " has occured. " & Err.Description End If GoTo finishsub End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Updates Cell but not the cells that reference it with formulas
Hey Mike,
I've had a similar problem with this as well. What fixed mine was sticking a line at the bottom of the subroutine just before the Exit Sub: Application.CalculateFull This will 'force' a complete re-calculation / formula update without the manual need to press 'F9' (or do it through some menu option) Hope this helps, Chad "MikeZz" wrote: Hi, I have a problem with cells updating after I use VBA to change a cell referenced in the formula. I use VBA to udpate a value in a cell that is used in other formulas but the formulas don't "udpate" the calculated value.... BUT when I mouseclick on the cell and press "Enter", it then recaculates the formula. The workbook is set to auto calculate so that's not the issue and if I press F9 to recalculate the workbook, it still does not update. The worksheet is protected but I don't think that is the problem. The other strange thing is that if I loop the macro twice, it seems to update all the values. It's like I'm doing something in the beginning of the code that triggers the worksheet to recalcualate, then the macro changes the values referenced in the formulas but doesn't trigger a recalc. Then when I go back and loop the macro, it does the first recalc, but doesn't at the end. Since I'm not changing the cell used in the formula, it doesn't matter if it recalculates at that point. I wonder if I'm somehow "loosing focus" of something by switching sheets or assigning the cell valute in the wrong way causing the issue. The "Redoloop" code is the loop that I added which seems to make it work in my example. Some ideas but not sure if they are the problem... I do a lot of switching between sheets.. I may be trying to overwrite a cell that is on a sheet that is not "Active" Should I be using range.value = number or range.formula = "=number" Or just range = number? I've been doing VBA in excel for quite a while and have had this come up before... just never figured it out. If you want me to explain in more detail, just let me know. I think it's just a syntax or a rule thing where I just need to write the code a little differently. Thanks! Here's my VBA.... Sub CopyFormulas() Dim baseformula As String Dim startformula As String Dim thelist As Variant redoloop = 0 starthe thelist = Range("analyzelist") Range("lasterror").Value = False startingpoint = ActiveSheet.Name Sheets("Detail").Select baseformula = Range("baseform") baseformula2 = Range("baseform2") NewFormula = "=" found = 0 Call UnprotectMe("Detail") For j = 1 To UBound(thelist, 1) If thelist(j, 2) = "x" Then If IsEmpty(thelist(j, 1)) = False Then found = found + 1 If found = 1 Then 'start base formula here. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' This could be the culprit section... or.... '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Sheets("Detail").Range("C7").Formula = "=" & Replace(baseformula, "SheetName", thelist(j, 1)) startformula = "=min(" & Replace(baseformula2, "SheetName", thelist(j, 1)) Else 'Add to base formula here On Error GoTo errorcheck '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' This could be the culprit section... or.... '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Sheets("Detail").Range("C7").Formula = Sheets("Detail").Range("C7").Formula & "+" & Replace(baseformula, "SheetName", thelist(j, 1)) startformula = startformula & "," & Replace(baseformula2, "SheetName", thelist(j, 1)) End If End If End If Next j finishsub: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' This could be the culprit section... or.... '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Range("start").Formula = startformula & ")" If found = 0 Then Range("start").Value = Now() Sheets("Detail").Range("C7").Formula = "TBD" End If Sheets("Lookups").Range("g11") = "'" & Range("C7").Formula Range("C7").Select Selection.Copy Range("C7:k244").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.NumberFormat = "$#,##0" Call ProtectMe("Detail") Sheets(startingpoint).Select redoloop = redoloop + 1 If redoloop = 2 Then Exit Sub Else GoTo starthe End If Exit Sub errorcheck: If Err.Number = 7 Then MsgBox "You passed the limit of allowable projects to calculate. Only the first " & j - 1 & " Projects are included in Detail" Range("lasterror").Value = True Else MsgBox "Error Number " & Err.Number & " has occured. " & Err.Description End If GoTo finishsub End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Updates Cell but not the cells that reference it with form
Thanks Chad,
This seemed to work so I added it to the code of all the macros & buttons just to make sure it's always updating. "ChadF" wrote: Hey Mike, I've had a similar problem with this as well. What fixed mine was sticking a line at the bottom of the subroutine just before the Exit Sub: Application.CalculateFull This will 'force' a complete re-calculation / formula update without the manual need to press 'F9' (or do it through some menu option) Hope this helps, Chad "MikeZz" wrote: Hi, I have a problem with cells updating after I use VBA to change a cell referenced in the formula. I use VBA to udpate a value in a cell that is used in other formulas but the formulas don't "udpate" the calculated value.... BUT when I mouseclick on the cell and press "Enter", it then recaculates the formula. The workbook is set to auto calculate so that's not the issue and if I press F9 to recalculate the workbook, it still does not update. The worksheet is protected but I don't think that is the problem. The other strange thing is that if I loop the macro twice, it seems to update all the values. It's like I'm doing something in the beginning of the code that triggers the worksheet to recalcualate, then the macro changes the values referenced in the formulas but doesn't trigger a recalc. Then when I go back and loop the macro, it does the first recalc, but doesn't at the end. Since I'm not changing the cell used in the formula, it doesn't matter if it recalculates at that point. I wonder if I'm somehow "loosing focus" of something by switching sheets or assigning the cell valute in the wrong way causing the issue. The "Redoloop" code is the loop that I added which seems to make it work in my example. Some ideas but not sure if they are the problem... I do a lot of switching between sheets.. I may be trying to overwrite a cell that is on a sheet that is not "Active" Should I be using range.value = number or range.formula = "=number" Or just range = number? I've been doing VBA in excel for quite a while and have had this come up before... just never figured it out. If you want me to explain in more detail, just let me know. I think it's just a syntax or a rule thing where I just need to write the code a little differently. Thanks! Here's my VBA.... Sub CopyFormulas() Dim baseformula As String Dim startformula As String Dim thelist As Variant redoloop = 0 starthe thelist = Range("analyzelist") Range("lasterror").Value = False startingpoint = ActiveSheet.Name Sheets("Detail").Select baseformula = Range("baseform") baseformula2 = Range("baseform2") NewFormula = "=" found = 0 Call UnprotectMe("Detail") For j = 1 To UBound(thelist, 1) If thelist(j, 2) = "x" Then If IsEmpty(thelist(j, 1)) = False Then found = found + 1 If found = 1 Then 'start base formula here. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' This could be the culprit section... or.... '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Sheets("Detail").Range("C7").Formula = "=" & Replace(baseformula, "SheetName", thelist(j, 1)) startformula = "=min(" & Replace(baseformula2, "SheetName", thelist(j, 1)) Else 'Add to base formula here On Error GoTo errorcheck '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' This could be the culprit section... or.... '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Sheets("Detail").Range("C7").Formula = Sheets("Detail").Range("C7").Formula & "+" & Replace(baseformula, "SheetName", thelist(j, 1)) startformula = startformula & "," & Replace(baseformula2, "SheetName", thelist(j, 1)) End If End If End If Next j finishsub: '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' This could be the culprit section... or.... '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''' Range("start").Formula = startformula & ")" If found = 0 Then Range("start").Value = Now() Sheets("Detail").Range("C7").Formula = "TBD" End If Sheets("Lookups").Range("g11") = "'" & Range("C7").Formula Range("C7").Select Selection.Copy Range("C7:k244").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.NumberFormat = "$#,##0" Call ProtectMe("Detail") Sheets(startingpoint).Select redoloop = redoloop + 1 If redoloop = 2 Then Exit Sub Else GoTo starthe End If Exit Sub errorcheck: If Err.Number = 7 Then MsgBox "You passed the limit of allowable projects to calculate. Only the first " & j - 1 & " Projects are included in Detail" Range("lasterror").Value = True Else MsgBox "Error Number " & Err.Number & " has occured. " & Err.Description End If GoTo finishsub End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Protect formulas which reference to unlocked cells in protected s. | Excel Worksheet Functions | |||
| Setting a cell reference in one sheet that updates a workbook? | Excel Discussion (Misc queries) | |||
| How to change cell reference within formulas | Excel Worksheet Functions | |||
| Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
| Chart updates using cell reference | Charts and Charting in Excel | |||
Linear Mode
