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