Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) |