using VB to enter a sumproduct formula in a cell
str should have been sStr (a typo)
Private Sub worksheet_Activate()
Dim sStr as String
If Not ActiveSheet.Previous Is Nothing Then
sStr = "=SumProduct(--(Sheets("Sheet4").Range(A1:A30000) = Sheets(Processd
Amount).Range(B1)),--(Sheets(Sheet4).Range(AL1:AL30000) 0))"
Me.Range("B2").Value = Evaluate(sStr)
End If
End Sub
--
Regards,
Tom Ogilvy
"ram" wrote:
Hi Tim,
I copied your code and received a compile error message.
I'm using the code below:
Private Sub worksheet_Activate()
If Not ActiveSheet.Previous Is Nothing Then
str = "=SumProduct(--(Sheets("Sheet4").Range(A1:A30000) = Sheets(Processd
Amount).Range(B1)),--(Sheets(Sheet4).Range(AL1:AL30000) 0))"
Me.Range("B2").Value = Evaluate(sStr)
End If
End Sub
Thanks for the help
"Tom Ogilvy" wrote:
Private Sub worksheet_Activate()
If Not ActiveSheet.Previous Is Nothing Then
str = "=SumProduct(--(Sheets("Sheet4").Range(A1:A30000) = Sheets(Processd
Amount).Range(B1)),--(Sheets(Sheet4).Range(AL1:AL30000) 0))"
Me.Range("B2").Value = Evaluate(sStr)
End If
End Sub
--
Regards,
Tom Ogilvy
"ram" wrote:
I would like help with the following syntax error:
Private Sub worksheet_Activate()
If Not ActiveSheet.Previous Is Nothing Then
Me.Range("B2").Formula =
"=SumProduct(--(Sheets("Sheet4").Range(A1:A30000) = Sheets(Processd
Amount).Range(B1)),--(Sheets(Sheet4).Range(AL1:AL30000) 0))"
End If
End Sub
also is there a way to enter the value of the above formula instead of the
formula?
Thanks for all your help
|