LinkBack URL
About LinkBacks
I have code that I will put here, it runs sumproduct but its not working as it should. I'm thinking there must be something in settings or something that needs to fixed. This code works flawlessly on another worksheet in the exact same form except the loop goes through 32 instead of 340. The error in the code starts on the line that says Range("W" & i). The first sumproduct in this line works, but the second sumproduct doesn't. I'm 90% sure theres a problem in the part after the comma that tells it to count because I can change column reference to a lower letter and it works.
When I do the step by step error analysis it shows the first sumproduct as correct, but the second one says sumproduct(--(false, false,false about 20 times),DataBase!$X$3:$X$20000)
I really have no clue whats going on. Maybe theres a way to get "into" excel to view the page formats on a deeper level than simply right clicking? This error is the same on every line after the Range("W" line. The second sumproduct doesn't work.
Heres the code. Like I said it works perfectly on a different workbook. I tried to copy and paste special the data to a new workbook and had the same problem. I've also tried it on an older version of the workbook I have on a separate computer and it doesn't work.
Sub TotalsSheet()
Dim Team As String
On Error Resume Next
ThisWorkbook.Worksheets("Totals").Select
For i = 3 To 336
Team = Range("A" & i)
Range("B" & i) = ("=COUNTIF(DataBase!$B$3:$B$20000,""" & Team & """)+COUNTIF(DataBase!$C$3:$C$20000,""" & Team & """)")
Range("C" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$D$3:$D$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$N$3:$N$20000)")
Range("D" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$E$3:$E$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$O$3:$O$20000)")
Range("E" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$F$3:$F$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$P$3:$P$20000)")
Range("F" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$G$3:$G$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$Q$3:$Q$20000)")
Range("G" & i) = Range("E" & i) / Range("F" & i)
Range("H" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$I$3:$I$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$S$3:$S$20000)")
Range("I" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$J$3:$J$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$T$3:$T$20000)")
Range("J" & i) = Range("H" & i) / Range("I" & i)
Range("K" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$L$3:$L$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$V$3:$V$20000)")
Range("L" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$M$3:$M$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$W$3:$W$20000)")
Range("M" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$N$3:$N$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$D$3:$D$20000)")
Range("N" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$O$3:$O$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$E$3:$E$20000)")
Range("O" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$P$3:$P$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$F$3:$F$20000)")
Range("P" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$Q$3:$Q$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$G$3:$G$20000)")
Range("Q" & i) = Range("O" & i) / Range("P" & i)
Range("R" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$S$3:$S$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$I$3:$I$20000)")
Range("S" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$T$3:$T$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$J$3:$J$20000)")
Range("T" & i) = Range("R" & i) / Range("S" & i)
Range("U" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$V$3:$V$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$L$3:$L$20000)")
Range("V" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$W$3:$W$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$M$3:$M$20000)")
Range("W" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$AR$3:$AR$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$X$3:$X$20000)")
Range("X" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$AS$3:$AS$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$Y$3:$Y$20000)")
Range("Y" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$AT$3:$AT$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$Z$3:$Z$20000)")
Range("Z" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$AU$3:$AU$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AA$3:$AA$20000)")
Range("AA" & i) = Range("Y" & i) / Range("Z" & i)
Range("AB" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$AW$3:$AW$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AC$3:$AC$20000)")
Range("AC" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$AX$3:$AX$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AD$3:$AD$20000)")
Range("AD" & i) = Range("AB" & i) / Range("AC" & i)
Range("AE" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$AZ$3:$AZ$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AF$3:$AF$20000)")
Range("AF" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$BA$3:$BA$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AG$3:$AG$20000)")
Range("AG" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$BB$3:$BB$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AH$3:$AH$20000)")
Range("AH" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$BC$3:$BC$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AI$3:$AI$20000)")
Range("AI" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$BD$3:$BD$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AJ$3:$AJ$20000)")
Range("AJ" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$BE$3:$BE$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AK$3:$AK$20000)")
Range("AK" & i) = Range("AI" & i) / Range("AJ" & i)
Range("AL" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$BG$3:$BG$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AM$3:$AM$20000)")
Range("AM" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$BH$3:$BH$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AN$3:$AN$20000)")
Range("AN" & i) = Range("AL" & i) / Range("AM" & i)
Range("AO" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$BJ$3:$BJ$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AP$3:$AP$20000)")
Range("AP" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team & """),DataBase!$BK$3:$BK$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" & Team & """),DataBase!$AQ$3:$AQ$20000)")
Next
End Sub
Register To Reply
Bookmarks