Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > using VB to enter a sumproduct formula in a cell
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Ram Ram is offline
external usenet poster
  
Posts: 138
Default using VB to enter a sumproduct formula in a cell
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default using VB to enter a sumproduct formula in a cell
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Ram Ram is offline
external usenet poster
  
Posts: 138
Default using VB to enter a sumproduct formula in a cell
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
Ram Ram is offline
external usenet poster
  
Posts: 138
Default using VB to enter a sumproduct formula in a cell
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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default using VB to enter a sumproduct formula in a cell
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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
Ram Ram is offline
external usenet poster
  
Posts: 138
Default using VB to enter a sumproduct formula in a cell
HI Tom,

I apologies for asking som many questions.

i tried the code you just provided and came up with a ref error in cell b2

Private Sub worksheet_Activate()

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

Many thanks for all your help


"Tom Ogilvy" wrote:

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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default using VB to enter a sumproduct formula in a cell
I can get that error if I don't have sheet named Sheet4 or I don't have a
sheet named Processed Amount or don't have either.

Sounds like a configuration problem with your workbook rather than anything
wrong with the code.

You don't need the Debug.print line.

--
regards,
Tom Ogilvy

"ram" wrote in message
...
HI Tom,

I apologies for asking som many questions.

i tried the code you just provided and came up with a ref error in cell b2

Private Sub worksheet_Activate()

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

Many thanks for all your help


"Tom Ogilvy" wrote:

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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to enter a formula in a cell? novastar Excel Worksheet Functions 2 September 1st 09 09:25 PM
Cannot enter formula in a cell after removing a circular formula Big Corona Excel Worksheet Functions 0 April 5th 05 06:07 PM
Trying To Enter A Formula Into Every Cell In A Column. Mag1c1an Excel Programming 5 January 3rd 05 04:50 PM
Enter formula in cell James Stephens Excel Programming 3 January 22nd 04 05:26 PM
Enter Formula in a cell with VBA Bruce Roberson Excel Programming 2 July 31st 03 08:54 PM


All times are GMT +1. The time now is 06:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement