Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > sumproduct in vba
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default sumproduct in vba
I have a sheet which could have up to 300 cells of a
complicated sumproduct formula. I have decided to try and
do the sums by vba but cannot get the following to work -
type mismatch error:-
Sub dothesums()
Application.ScreenUpdating = False
Dim rng, rng1, rng2, rng3, rng4, rng5, rng6, rng7, rng8,
rng9 As Name
Dim cell As Range
Range("C4:C7").Name = "rng"
Range("D4:D7").Name = "rng1"
Range("F4:F7").Name = "rng2"
Range("G4:G7").Name = "rng3"
Range("I4:I7").Name = "rng4"
Range("J4:J7").Name = "rng5"
Range("L4:L7").Name = "rng6"
Range("M4:M7").Name = "rng7"
Range("O4:O7").Name = "rng8"
Range("P4:P7").Name = "rng9"
For Each cell In Range("R4:R30")
cell.Value = Application.SumProduct((rng = "SFP - IACS") *
(rng1 = "visit")) / 4 + _
Application.SumProduct((rng2 = "SFP - IACS") * (rng3
= "visit")) / 4 + _
Application.SumProduct((rng4 = "SFP - IACS") * (rng5
= "visit")) / 4 + _
Application.SumProduct((rng6 = "SFP - IACS") * (rng7
= "visit")) / 4 + _
Application.SumProduct((rng8 = "SFP - IACS") * (rng9
= "visit")) / 4
Range("rng").Offset(4, 0).Resize(4, 1).Name = "rng"
Range("rng1").Offset(4, 0).Resize(4, 1).Name = "rng1"
Range("rng2").Offset(4, 0).Resize(4, 1).Name = "rng2"
Range("rng3").Offset(4, 0).Resize(4, 1).Name = "rng3"
Range("rng4").Offset(4, 0).Resize(4, 1).Name = "rng4"
Range("rng5").Offset(4, 0).Resize(4, 1).Name = "rng5"
Range("rng6").Offset(4, 0).Resize(4, 1).Name = "rng6"
Range("rng7").Offset(4, 0).Resize(4, 1).Name = "rng7"
Range("rng8").Offset(4, 0).Resize(4, 1).Name = "rng8"
Range("rng9").Offset(4, 0).Resize(4, 1).Name = "rng9"
Next
Application.ScreenUpdating = True
End Sub

PS
I am a novice when it comes to using worksheet functions
in vba.

Thanks in advance.

Gareth
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default sumproduct in vba
Try

cell.Value = Evaluate("SUMPRODUCT((C4:C7 = ""SFP - IACS"") *
(D4:D7 = ""visit"")) / 4 + _

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gareth" wrote in message
...
I have a sheet which could have up to 300 cells of a
complicated sumproduct formula. I have decided to try and
do the sums by vba but cannot get the following to work -
type mismatch error:-
Sub dothesums()
Application.ScreenUpdating = False
Dim rng, rng1, rng2, rng3, rng4, rng5, rng6, rng7, rng8,
rng9 As Name
Dim cell As Range
Range("C4:C7").Name = "rng"
Range("D4:D7").Name = "rng1"
Range("F4:F7").Name = "rng2"
Range("G4:G7").Name = "rng3"
Range("I4:I7").Name = "rng4"
Range("J4:J7").Name = "rng5"
Range("L4:L7").Name = "rng6"
Range("M4:M7").Name = "rng7"
Range("O4:O7").Name = "rng8"
Range("P4:P7").Name = "rng9"
For Each cell In Range("R4:R30")
cell.Value = Application.SumProduct((rng = "SFP - IACS") *
(rng1 = "visit")) / 4 + _
Application.SumProduct((rng2 = "SFP - IACS") * (rng3
= "visit")) / 4 + _
Application.SumProduct((rng4 = "SFP - IACS") * (rng5
= "visit")) / 4 + _
Application.SumProduct((rng6 = "SFP - IACS") * (rng7
= "visit")) / 4 + _
Application.SumProduct((rng8 = "SFP - IACS") * (rng9
= "visit")) / 4
Range("rng").Offset(4, 0).Resize(4, 1).Name = "rng"
Range("rng1").Offset(4, 0).Resize(4, 1).Name = "rng1"
Range("rng2").Offset(4, 0).Resize(4, 1).Name = "rng2"
Range("rng3").Offset(4, 0).Resize(4, 1).Name = "rng3"
Range("rng4").Offset(4, 0).Resize(4, 1).Name = "rng4"
Range("rng5").Offset(4, 0).Resize(4, 1).Name = "rng5"
Range("rng6").Offset(4, 0).Resize(4, 1).Name = "rng6"
Range("rng7").Offset(4, 0).Resize(4, 1).Name = "rng7"
Range("rng8").Offset(4, 0).Resize(4, 1).Name = "rng8"
Range("rng9").Offset(4, 0).Resize(4, 1).Name = "rng9"
Next
Application.ScreenUpdating = True
End Sub

PS
I am a novice when it comes to using worksheet functions
in vba.

Thanks in advance.

Gareth


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default sumproduct in vba
Not exactly what I'm looking for, I want the least code
possible to do the job so I thought my original suggestion
was OK but I couldn't get it to work.

Is it possible to do it using names instead of actual
ranges?

Gareth

-----Original Message-----
Try

cell.Value = Evaluate("SUMPRODUCT((C4:C7 = ""SFP -
IACS"") *
(D4:D7 = ""visit"")) / 4 + _

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gareth" wrote in
message
...
I have a sheet which could have up to 300 cells of a
complicated sumproduct formula. I have decided to try
and
do the sums by vba but cannot get the following to
work -
type mismatch error:-
Sub dothesums()
Application.ScreenUpdating = False
Dim rng, rng1, rng2, rng3, rng4, rng5, rng6, rng7, rng8,
rng9 As Name
Dim cell As Range
Range("C4:C7").Name = "rng"
Range("D4:D7").Name = "rng1"
Range("F4:F7").Name = "rng2"
Range("G4:G7").Name = "rng3"
Range("I4:I7").Name = "rng4"
Range("J4:J7").Name = "rng5"
Range("L4:L7").Name = "rng6"
Range("M4:M7").Name = "rng7"
Range("O4:O7").Name = "rng8"
Range("P4:P7").Name = "rng9"
For Each cell In Range("R4:R30")
cell.Value = Application.SumProduct((rng = "SFP -
IACS") *
(rng1 = "visit")) / 4 + _
Application.SumProduct((rng2 = "SFP - IACS") * (rng3
= "visit")) / 4 + _
Application.SumProduct((rng4 = "SFP - IACS") * (rng5
= "visit")) / 4 + _
Application.SumProduct((rng6 = "SFP - IACS") * (rng7
= "visit")) / 4 + _
Application.SumProduct((rng8 = "SFP - IACS") * (rng9
= "visit")) / 4
Range("rng").Offset(4, 0).Resize(4, 1).Name = "rng"
Range("rng1").Offset(4, 0).Resize(4, 1).Name = "rng1"
Range("rng2").Offset(4, 0).Resize(4, 1).Name = "rng2"
Range("rng3").Offset(4, 0).Resize(4, 1).Name = "rng3"
Range("rng4").Offset(4, 0).Resize(4, 1).Name = "rng4"
Range("rng5").Offset(4, 0).Resize(4, 1).Name = "rng5"
Range("rng6").Offset(4, 0).Resize(4, 1).Name = "rng6"
Range("rng7").Offset(4, 0).Resize(4, 1).Name = "rng7"
Range("rng8").Offset(4, 0).Resize(4, 1).Name = "rng8"
Range("rng9").Offset(4, 0).Resize(4, 1).Name = "rng9"
Next
Application.ScreenUpdating = True
End Sub

PS
I am a novice when it comes to using worksheet functions
in vba.

Thanks in advance.

Gareth


.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default sumproduct in vba
Yes, but it gets to be more code

cell.Value = Evaluate("SUMPRODUCT((" & rng.Address & "= ""SFP - IACS"") * ("
& rng1.Address & "= ""visit"")) / 4 + _

etc.
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gareth" wrote in message
...
Not exactly what I'm looking for, I want the least code
possible to do the job so I thought my original suggestion
was OK but I couldn't get it to work.

Is it possible to do it using names instead of actual
ranges?

Gareth

-----Original Message-----
Try

cell.Value = Evaluate("SUMPRODUCT((C4:C7 = ""SFP -
IACS"") *
(D4:D7 = ""visit"")) / 4 + _

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gareth" wrote in
message
...
I have a sheet which could have up to 300 cells of a
complicated sumproduct formula. I have decided to try
and
do the sums by vba but cannot get the following to
work -
type mismatch error:-
Sub dothesums()
Application.ScreenUpdating = False
Dim rng, rng1, rng2, rng3, rng4, rng5, rng6, rng7, rng8,
rng9 As Name
Dim cell As Range
Range("C4:C7").Name = "rng"
Range("D4:D7").Name = "rng1"
Range("F4:F7").Name = "rng2"
Range("G4:G7").Name = "rng3"
Range("I4:I7").Name = "rng4"
Range("J4:J7").Name = "rng5"
Range("L4:L7").Name = "rng6"
Range("M4:M7").Name = "rng7"
Range("O4:O7").Name = "rng8"
Range("P4:P7").Name = "rng9"
For Each cell In Range("R4:R30")
cell.Value = Application.SumProduct((rng = "SFP -
IACS") *
(rng1 = "visit")) / 4 + _
Application.SumProduct((rng2 = "SFP - IACS") * (rng3
= "visit")) / 4 + _
Application.SumProduct((rng4 = "SFP - IACS") * (rng5
= "visit")) / 4 + _
Application.SumProduct((rng6 = "SFP - IACS") * (rng7
= "visit")) / 4 + _
Application.SumProduct((rng8 = "SFP - IACS") * (rng9
= "visit")) / 4
Range("rng").Offset(4, 0).Resize(4, 1).Name = "rng"
Range("rng1").Offset(4, 0).Resize(4, 1).Name = "rng1"
Range("rng2").Offset(4, 0).Resize(4, 1).Name = "rng2"
Range("rng3").Offset(4, 0).Resize(4, 1).Name = "rng3"
Range("rng4").Offset(4, 0).Resize(4, 1).Name = "rng4"
Range("rng5").Offset(4, 0).Resize(4, 1).Name = "rng5"
Range("rng6").Offset(4, 0).Resize(4, 1).Name = "rng6"
Range("rng7").Offset(4, 0).Resize(4, 1).Name = "rng7"
Range("rng8").Offset(4, 0).Resize(4, 1).Name = "rng8"
Range("rng9").Offset(4, 0).Resize(4, 1).Name = "rng9"
Next
Application.ScreenUpdating = True
End Sub

PS
I am a novice when it comes to using worksheet functions
in vba.

Thanks in advance.

Gareth


.



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
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE gholly Excel Discussion (Misc queries) 2 September 28th 09 05:07 PM
SUMPRODUCT Chad Portman Excel Discussion (Misc queries) 4 February 26th 09 08:47 PM
Conditional SUMPRODUCT or SUMPRODUCT with Filters Ted M H Excel Worksheet Functions 4 August 14th 08 07:50 PM
sumproduct? sumif(sumproduct)? David Excel Worksheet Functions 3 July 13th 07 07:06 PM
sumproduct RGlade Excel Discussion (Misc queries) 2 December 8th 06 09:41 PM


All times are GMT +1. The time now is 04:46 PM.

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