VBA Updates Cell but not the cells that reference it with formulas

[MikeZz]

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
starthere:
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 starthere:
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

 

[ChadF]

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]

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.