Change worksheet formulas from button on chart sheet
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
--
Regards,
Tom Ogilvy
"David" wrote in message
...
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
--
David
|