Change worksheet formulas from button on chart sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,
This one has really got me scratching my head
The macros below change the formulas in column ranges "required" and
"completed". They work fine when run from a forms menu button on any
worksheet, but when I try to run them from a similar button on a chart sheet
I get the following extraordinary results for the 4 formula variants:
=SUMPRODUCT(--(LEFT(R[-6]C[212]:R[-6]C[230])<>"X"))
=SUMPRODUCT(--(R[-6]C[210]:R[-6]C[228]="C"))
=SUMPRODUCT(--(R[-6]C[210]:R[-6]C[228]="C"),UserWeighting)
=SUMPRODUCT(--(LEFT(R[-6]C[212]:R[-6]C[230])<>"X"),UserWeighting)

Sub ApplyWeights()
With Sheets("Matrix")
.Range("Required").FormulaR1C1 = _
"=SUMPRODUCT(--(LEFT(RC[-19]:RC[-1])<>""X""),UserWeighting)"
.Range("Completed").FormulaR1C1 = _
"=SUMPRODUCT(--(RC[-20]:RC[-2]=""C""),UserWeighting)"
End With
End Sub

Sub RemoveWeights()
With Sheets("Matrix")
.Range("Required").FormulaR1C1 = _
"=SUMPRODUCT(--(LEFT(RC[-19]:RC[-1])<>""X""))"
.Range("Completed").FormulaR1C1 = _
"=SUMPRODUCT(--(RC[-20]:RC[-2]=""C""))"
End With
End Sub

Any advice will be much appreciated
TIA
 
the quick fix would be

Dim sh as Object
set sh = Activesheet

Application.ScreenUpdating = False
Wth Sheets("Matrix")
.Activate


sh.Activate
End With
Application.ScreenUpdating = True
 
Back
Top