Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am going crazy with Sumproduct
I have code that I will put here, it runs sumproduct but its not workin as it should. I'm thinking there must be something in settings o something that needs to fixed. This code works flawlessly on anothe worksheet in the exact same form except the loop goes through 3 instead of 340. The error in the code starts on the line that say Range("W" & i). The first sumproduct in this line works, but th second sumproduct doesn't. I'm 90% sure theres a problem in the par after the comma that tells it to count because I can change colum reference to a lower letter and it works. When I do the step by step error analysis it shows the first sumproduc as correct, but the second one says sumproduct(--(false, false,fals 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 righ 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 differen workbook. I tried to copy and paste special the data to a new workboo and had the same problem. I've also tried it on an older version of th 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 -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429 View this thread: http://www.excelforum.com/showthread...hreadid=490641 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am going crazy with Sumproduct
Wow that is ugly, not sure how to fix it to make it easier to read. -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429 View this thread: http://www.excelforum.com/showthread...hreadid=490641 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am going crazy with Sumproduct
the code as written worked fine for me in xl97.
I don't see much point in it as for each column, it puts in an identical formula in rows 3 to 336. -- Regards, Tom Ogilvy "Ramthebuffs" wrote in message ... Wow that is ugly, not sure how to fix it to make it easier to read. -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429 View this thread: http://www.excelforum.com/showthread...hreadid=490641 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am going crazy with Sumproduct
Thanks for the response Tom, that is my problem. It should work, but it doesn't. It even works on different workbook. For some reason it fails after column W though. I get the #N/A error in each cell after column W. -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429 View this thread: http://www.excelforum.com/showthread...hreadid=490641 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am going crazy with Sumproduct
I suggest that you start a new post and explain in a simplified form what you
want to do. It appears that the code could be greatly simplified. I for one don't really want to sort it all out. IMHO, I think you'd be better off with a different approach. Regards, Greg "Ramthebuffs" wrote: Thanks for the response Tom, that is my problem. It should work, but it doesn't. It even works on different workbook. For some reason it fails after column W though. I get the #N/A error in each cell after column W. -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429 View this thread: http://www.excelforum.com/showthread...hreadid=490641 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I am going crazy with Sumproduct
#N/A would usually indicate that you have a #N/A error in your source data -
not that there is a problem with the formula. -- Regards, Tom Ogilvy "Ramthebuffs" wrote in message ... Thanks for the response Tom, that is my problem. It should work, but it doesn't. It even works on different workbook. For some reason it fails after column W though. I get the #N/A error in each cell after column W. -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429 View this thread: http://www.excelforum.com/showthread...hreadid=490641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Crazy If | Excel Worksheet Functions | |||
I know this may be a crazy question but.... | Excel Discussion (Misc queries) | |||
I'm going crazy here | Excel Discussion (Misc queries) | |||
This is crazy!!! | Excel Discussion (Misc queries) | |||
ADO SQL: Im getting CRAZY | Excel Programming |