sumproduct in vba

[Gareth]

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("D47").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("P47").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

 

[Bob Phillips]

Try

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

etc.

--

HTH

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

 

[Gareth]

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

 

[Bob Phillips]

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)