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

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

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

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

Hi Tom,

When i use quotes around the sheet name i receive a compile error. If I
delete the quotes from the code i receive the value error in me.range("b2").

do you know what i'm doing wrong?

thanks again.

"Tom Ogilvy" wrote:

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

My apologies. I really didn't look at your formula - I assumed it was in
good shape and you had executed it successfully as written - were just asking
how to do it in VBA. But on closer examination, I see you have a mixture of
vba and worksheet formula notation. Evaluate just works as a virtual cell,
so the formula should be as you would place it in a cell:

Sub testit()
Dim sStr As String
Dim v As Variant
sStr = "=SumProduct(--(Sheet4!A1:A30000=" & _
"'Processd Amount'!B1),--(Sheet4!AL1:AL30000 0))"
v = Evaluate(sStr)
Debug.Print v
End Sub

tested fine for me. Adapt it to what you are doing.

Note that Processd does not contain an e between the ss and d - if that is
the correct spelling, then no problem. If not, fix it.

--
Regards,
Tom Ogilvy



"ram" wrote:

Hi Tom,

When i use quotes around the sheet name i receive a compile error. If I
delete the quotes from the code i receive the value error in me.range("b2").

do you know what i'm doing wrong?

thanks again.

"Tom Ogilvy" wrote:

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