procedure too large error vb

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Well I went through all the trouble of doing all this code for a button to find out there is a 64k limit. "Help" says I need to break it down. Doesn't say how to do this, but tells me I need to do it. Any ideas on how I could break this down?
Private Sub CommandButton32_Click()
Range("J44") = [J5] + [L5] + [N5] + [P5] + [R5] + [T5] + [V5] + [X5] + [Z5] + [AB5] + [AD5] + [AF5] + [AH5] + [AJ5] + [AL5] + [AN5] + [AP5] + [AR5] + [AT5] + [AV5] + [AX5] + [AZ5] + [BB5] + [BD5] + [BF5] + [BH5] + [BJ5] + [BL5] + [BN5] + [BP5] + [BR5]
Range("K44") = [K5] + [M5] + [O5] + [Q5] + [S5] + [U5] + [W5] + [Y5] + [AA5] + [AC5] + [AE5] + [AG5] + [AI5] + [AK5] + [AM5] + [AO5] + [AQ5] + [AS5] + [AU5] + [AW5] + [AY5] + [BA5] + [BC5] + [BE5] + [BG5] + [BI5] + [BK5] + [BM5] + [BO5] + [BQ5] + [BS5]
Range("J45") = [J6] + [L6] + [N6] + [P6] + [R6] + [T6] + [V6] + [X6] + [Z6] + [AB6] + [AD6] + [AF6] + [AH6] + [AJ6] + [AL6] + [AN6] + [AP6] + [AR6] + [AT6] + [AV6] + [AX6] + [AZ6] + [BB6] + [BD6] + [BF6] + [BH6] + [BJ6] + [BL6] + [BN6] + [BP6] + [BR6]
Range("K45") = [K6] + [M6] + [O6] + [Q6] + [S6] + [U6] + [W6] + [Y6] + [AA6] + [AC6] + [AE6] + [AG6] + [AI6] + [AK6] + [AM6] + [AO6] + [AQ6] + [AS6] + [AU6] + [AW6] + [AY6] + [BA6] + [BC6] + [BE6] + [BG6] + [BI6] + [BK6] + [BM6] + [BO6] + [BQ6] + [BS6]
Range("J46") = [J7] + [L7] + [N7] + [P7] + [R7] + [T7] + [V7] + [X7] + [Z7] + [AB7] + [AD7] + [AF7] + [AH7] + [AJ7] + [AL7] + [AN7] + [AP7] + [AR7] + [AT7] + [AV7] + [AX7] + [AZ7] + [BB7] + [BD7] + [BF7] + [BH7] + [BJ7] + [BL7] + [BN7] + [BP7] + [BR7]
Range("K46") = [K7] + [M7] + [O7] + [Q7] + [S7] + [U7] + [W7] + [Y7] + [AA7] + [AC7] + [AE7] + [AG7] + [AI7] + [AK7] + [AM7] + [AO7] + [AQ7] + [AS7] + [AU7] + [AW7] + [AY7] + [BA7] + [BC7] + [BE7] + [BG7] + [BI7] + [BK7] + [BM7] + [BO7] + [BQ7] + [BS7]
Range("J47") = [J8] + [L8] + [N8] + [P8] + [R8] + [T8] + [V7] + [X8] + [Z8] + [AB8] + [AD8] + [AF8] + [AH8] + [AJ8] + [AL8] + [AN8] + [AP8] + [AR8] + [AT8] + [AV8] + [AX8] + [AZ8] + [BB8] + [BD8] + [BF8] + [BH8] + [BJ8] + [BL8] + [BN8] + [BP8] + [BR8]
Range("K47") = [K8] + [M8] + [O8] + [Q8] + [S8] + [U8] + [W7] + [Y8] + [AA8] + [AC8] + [AE8] + [AG8] + [AI8] + [AK8] + [AM8] + [AO8] + [AQ8] + [AS8] + [AU8] + [AW8] + [AY8] + [BA8] + [BC8] + [BE8] + [BG8] + [BI8] + [BK8] + [BM8] + [BO8] + [BQ8] + [BS8]
Range("J48") = [J9] + [L9] + [N9] + [P9] + [R9] + [T9] + [V9] + [X9] + [Z9] + [AB9] + [AD9] + [AF9] + [AH9] + [AJ9] + [AL9] + [AN9] + [AP9] + [AR9] + [AT9] + [AV9] + [AX9] + [AZ9] + [BB9] + [BD9] + [BF9] + [BH9] + [BJ9] + [BL9] + [BN9] + [BP9] + [BR9]
Range("K48") = [K9] + [M9] + [O9] + [Q9] + [S9] + [U9] + [W9] + [Y9] + [AA9] + [AC9] + [AE9] + [AG9] + [AI9] + [AK9] + [AM9] + [AO9] + [AQ9] + [AS9] + [AU9] + [AW9] + [AY9] + [BA9] + [BC9] + [BE9] + [BG9] + [BI9] + [BK9] + [BM9] + [BO9] + [BQ9] + [BS9]
Range("J49") = [J10] + [L10] + [N10] + [P10] + [R10] + [T10] + [V10] + [X10] + [Z10] + [AB10] + [AD10] + [AF10] + [AH10] + [AJ10] + [AL10] + [AN10] + [AP10] + [AR10] + [AT10] + [AV10] + [AX10] + [AZ10] + [BB10] + [BD10] + [BF10] + [BH10] + [BJ10] + [BL10] + [BN10] + [BP10] + [BR10]
Range("K49") = [K10] + [M10] + [O10] + [Q10] + [S10] + [U10] + [W10] + [Y10] + [AA10] + [AC10] + [AE10] + [AG10] + [AI10] + [AK10] + [AM10] + [AO10] + [AQ10] + [AS10] + [AU10] + [AW10] + [AY10] + [BA10] + [BC10] + [BE100] + [BG10] + [BI10] + [BK10] + [BM10] + [BO10] + [BQ10] + [BS10]
Range("J50") = [J11] + [L11] + [N11] + [P11] + [R11] + [T11] + [V11] + [X11] + [Z11] + [AB11] + [AD11] + [AF11] + [AH11] + [AJ11] + [AL11] + [AN11] + [AP11] + [AR11] + [AT11] + [AV11] + [AX11] + [AZ11] + [BB11] + [BD11] + [BF11] + [BH11] + [BJ11] + [BL11] + [BN11] + [BP11] + [BR11]
Range("K50") = [K11] + [M11] + [O11] + [Q11] + [S11] + [U11] + [W11] + [Y11] + [AA11] + [AC11] + [AE11] + [AG11] + [AI11] + [AK11] + [AM11] + [AO11] + [AQ11] + [AS11] + [AU11] + [AW11] + [AY11] + [BA11] + [BC11] + [BE11] + [BG11] + [BI11] + [BK11] + [BM11] + [BO11] + [BQ11] + [BS11]
Range("J51") = [J12] + [L12] + [N12] + [P12] + [R12] + [T12] + [V12] + [X12] + [Z12] + [AB12] + [AD12] + [AF12] + [AH12] + [AJ12] + [AL12] + [AN12] + [AP12] + [AR12] + [AT12] + [AV12] + [AX12] + [AZ12] + [BB12] + [BD12] + [BF12] + [BH12] + [BJ12] + [BL12] + [BN12] + [BP12] + [BR12]
Range("K51") = [K12] + [M12] + [O12] + [Q12] + [S12] + [U12] + [W12] + [Y12] + [AA12] + [AC12] + [AE12] + [AG12] + [AI12] + [AK12] + [AM12] + [AO12] + [AQ12] + [AS12] + [AU12] + [AW12] + [AY12] + [BA12] + [BC12] + [BE12] + [BG12] + [BI12] + [BK12] + [BM12] + [BO12] + [BQ12] + [BS12]
Range("J52") = [J13] + [L13] + [N13] + [P13] + [R13] + [T13] + [V13] + [X13] + [Z13] + [AB13] + [AD13] + [AF13] + [AH13] + [AJ13] + [AL13] + [AN13] + [AP13] + [AR13] + [AT13] + [AV13] + [AX13] + [AZ13] + [BB13] + [BD13] + [BF13] + [BH13] + [BJ13] + [BL13] + [BN13] + [BP13] + [BR13]
Range("K52") = [K13] + [M13] + [O13] + [Q13] + [S13] + [U13] + [W13] + [Y13] + [AA13] + [AC13] + [AE13] + [AG13] + [AI13] + [AK13] + [AM13] + [AO13] + [AQ13] + [AS13] + [AU13] + [AW13] + [AY13] + [BA13] + [BC13] + [BE13] + [BG13] + [BI13] + [BK13] + [BM13] + [BO13] + [BQ13] + [BS13]
Range("J53") = [J14] + [L14] + [N14] + [P14] + [R14] + [T14] + [V14] + [X14] + [Z14] + [AB14] + [AD14] + [AF14] + [AH14] + [AJ14] + [AL14] + [AN14] + [AP14] + [AR14] + [AT14] + [AV14] + [AX14] + [AZ14] + [BB14] + [BD14] + [BF14] + [BH14] + [BJ14] + [BL14] + [BN14] + [BP14] + [BR14]
Range("K53") = [K14] + [M14] + [O14] + [Q14] + [S14] + [U14] + [W14] + [Y14] + [AA14] + [AC14] + [AE14] + [AG14] + [AI14] + [AK14] + [AM14] + [AO14] + [AQ14] + [AS14] + [AU14] + [AW14] + [AY14] + [BA14] + [BC14] + [BE14] + [BG14] + [BI14] + [BK14] + [BM14] + [BO14] + [BQ14] + [BS14]
Range("J54") = [J15] + [L15] + [N15] + [P15] + [R15] + [T15] + [V15] + [X15] + [Z15] + [AB15] + [AD15] + [AF15] + [AH15] + [AJ15] + [AL15] + [AN15] + [AP15] + [AR15] + [AT15] + [AV15] + [AX15] + [AZ15] + [BB15] + [BD15] + [BF15] + [BH15] + [BJ15] + [BL15] + [BN15] + [BP15] + [BR15]
Range("K54") = [K15] + [M15] + [O15] + [Q15] + [S15] + [U15] + [W15] + [Y15] + [AA15] + [AC15] + [AE15] + [AG15] + [AI15] + [AK15] + [AM15] + [AO15] + [AQ15] + [AS15] + [AU15] + [AW15] + [AY15] + [BA15] + [BC15] + [BE15] + [BG15] + [BI15] + [BK15] + [BM15] + [BO15] + [BQ15] + [BS15]
Range("J55") = [J16] + [L16] + [N16] + [P16] + [R16] + [T16] + [V16] + [X16] + [Z16] + [AB16] + [AD16] + [AF16] + [AH16] + [AJ16] + [AL16] + [AN16] + [AP16] + [AR16] + [AT16] + [AV16] + [AX16] + [AZ16] + [BB16] + [BD16] + [BF16] + [BH16] + [BJ16] + [BL16] + [BN16] + [BP16] + [BR16]
Range("K55") = [K16] + [M16] + [O16] + [Q16] + [S16] + [U16] + [W16] + [Y16] + [AA16] + [AC16] + [AE16] + [AG16] + [AI16] + [AK16] + [AM16] + [AO16] + [AQ16] + [AS16] + [AU16] + [AW16] + [AY16] + [BA16] + [BC16] + [BE16] + [BG16] + [BI16] + [BK16] + [BM16] + [BO16] + [BQ16] + [BS16]
Range("J56") = [J17] + [L17] + [N17] + [P17] + [R17] + [T17] + [V17] + [X17] + [Z17] + [AB17] + [AD17] + [AF17] + [AH17] + [AJ17] + [AL17] + [AN17] + [AP17] + [AR17] + [AT17] + [AV17] + [AX17] + [AZ17] + [BB17] + [BD17] + [BF17] + [BH17] + [BJ17] + [BL17] + [BN17] + [BP17] + [BR17]
Range("K56") = [K17] + [M17] + [O17] + [Q17] + [S17] + [U17] + [W17] + [Y17] + [AA17] + [AC17] + [AE17] + [AG17] + [AI17] + [AK17] + [AM17] + [AO17] + [AQ17] + [AS17] + [AU17] + [AW17] + [AY17] + [BA17] + [BC17] + [BE17] + [BG17] + [BI17] + [BK17] + [BM17] + [BO17] + [BQ17] + [BS17]
Range("J57") = [J18] + [L18] + [N18] + [P18] + [R18] + [T18] + [V18] + [X18] + [Z18] + [AB18] + [AD18] + [AF18] + [AH18] + [AJ18] + [AL18] + [AN18] + [AP18] + [AR18] + [AT18] + [AV18] + [AX18] + [AZ18] + [BB18] + [BD18] + [BF18] + [BH18] + [BJ18] + [BL18] + [BN18] + [BP18] + [BR18]
Range("K57") = [K18] + [M18] + [O18] + [Q18] + [S18] + [U18] + [W18] + [Y18] + [AA18] + [AC18] + [AE18] + [AG18] + [AI18] + [AK18] + [AM18] + [AO18] + [AQ18] + [AS18] + [AU18] + [AW18] + [AY18] + [BA18] + [BC18] + [BE18] + [BG18] + [BI18] + [BK18] + [BM18] + [BO18] + [BQ18] + [BS18]
Range("J58") = [J19] + [L19] + [N19] + [P19] + [R19] + [T19] + [V19] + [X19] + [Z19] + [AB19] + [AD19] + [AF19] + [AH19] + [AJ19] + [AL19] + [AN19] + [AP19] + [AR19] + [AT19] + [AV19] + [AX19] + [AZ19] + [BB19] + [BD19] + [BF19] + [BH19] + [BJ19] + [BL19] + [BN19] + [BP19] + [BR19]
Range("K58") = [K19] + [M19] + [O19] + [Q19] + [S19] + [U19] + [W19] + [Y19] + [AA19] + [AC19] + [AE19] + [AG19] + [AI19] + [AK19] + [AM19] + [AO19] + [AQ19] + [AS19] + [AU19] + [AW19] + [AY19] + [BA19] + [BC19] + [BE19] + [BG19] + [BI19] + [BK19] + [BM19] + [BO19] + [BQ19] + [BS19]
Range("J59") = [J20] + [L20] + [N20] + [P20] + [R20] + [T20] + [V20] + [X20] + [Z20] + [AB20] + [AD20] + [AF20] + [AH20] + [AJ20] + [AL20] + [AN20] + [AP20] + [AR20] + [AT20] + [AV20] + [AX20] + [AZ20] + [BB20] + [BD20] + [BF20] + [BH20] + [BJ20] + [BL20] + [BN20] + [BP20] + [BR20]
Range("K59") = [K20] + [M20] + [O20] + [Q20] + [S20] + [U20] + [W20] + [Y20] + [AA20] + [AC20] + [AE20] + [AG20] + [AI20] + [AK20] + [AM20] + [AO20] + [AQ20] + [AS20] + [AU20] + [AW20] + [AY20] + [BA20] + [BC20] + [BE20] + [BG20] + [BI20] + [BK20] + [BM20] + [BO20] + [BQ20] + [BS20]
Range("J60") = [J21] + [L21] + [N21] + [P21] + [R21] + [T21] + [V21] + [X21] + [Z21] + [AB21] + [AD21] + [AF21] + [AH21] + [AJ21] + [AL21] + [AN21] + [AP21] + [AR21] + [AT21] + [AV21] + [AX21] + [AZ21] + [BB21] + [BD21] + [BF21] + [BH21] + [BJ21] + [BL21] + [BN21] + [BP21] + [BR21]
Range("K60") = [K21] + [M21] + [O21] + [Q21] + [S21] + [U21] + [W21] + [Y21] + [AA21] + [AC21] + [AE21] + [AG21] + [AI21] + [AK21] + [AM21] + [AO21] + [AQ21] + [AS21] + [AU21] + [AW21] + [AY21] + [BA21] + [BC21] + [BE21] + [BG21] + [BI21] + [BK21] + [BM21] + [BO21] + [BQ21] + [BS21]
Range("J61") = [J22] + [L22] + [N22] + [P22] + [R22] + [T22] + [V22] + [X22] + [Z22] + [AB22] + [AD22] + [AF22] + [AH22] + [AJ22] + [AL22] + [AN22] + [AP22] + [AR22] + [AT22] + [AV22] + [AX22] + [AZ22] + [BB22] + [BD22] + [BF22] + [BH22] + [BJ22] + [BL22] + [BN22] + [BP22] + [BR22]
Range("K61") = [K22] + [M22] + [O22] + [Q22] + [S22] + [U22] + [W22] + [Y22] + [AA22] + [AC22] + [AE22] + [AG22] + [AI22] + [AK22] + [AM22] + [AO22] + [AQ22] + [AS22] + [AU22] + [AW22] + [AY22] + [BA22] + [BC22] + [BE22] + [BG22] + [BI22] + [BK22] + [BM22] + [BO22] + [BQ22] + [BS22]
Range("J62") = [J23] + [L23] + [N23] + [P23] + [R23] + [T23] + [V23] + [X23] + [Z23] + [AB23] + [AD23] + [AF23] + [AH23] + [AJ23] + [AL23] + [AN23] + [AP23] + [AR23] + [AT23] + [AV23] + [AX23] + [AZ23] + [BB23] + [BD23] + [BF23] + [BH23] + [BJ23] + [BL23] + [BN23] + [BP23] + [BR23]
Range("K62") = [K23] + [M23] + [O23] + [Q23] + [S23] + [U23] + [W23] + [Y23] + [AA23] + [AC23] + [AE23] + [AG23] + [AI23] + [AK23] + [AM23] + [AO23] + [AQ23] + [AS23] + [AU23] + [AW23] + [AY23] + [BA23] + [BC23] + [BE23] + [BG23] + [BI23] + [BK23] + [BM23] + [BO23] + [BQ23] + [BS23]
Range("J63") = [J24] + [L24] + [N24] + [P24] + [R24] + [T24] + [V24] + [X24] + [Z24] + [AB24] + [AD24] + [AF24] + [AH24] + [AJ24] + [AL24] + [AN24] + [AP24] + [AR24] + [AT24] + [AV24] + [AX24] + [AZ24] + [BB24] + [BD24] + [BF24] + [BH24] + [BJ24] + [BL24] + [BN24] + [BP24] + [BR24]
Range("K63") = [K24] + [M24] + [O24] + [Q24] + [S24] + [U24] + [W24] + [Y24] + [AA24] + [AC24] + [AE24] + [AG24] + [AI24] + [AK24] + [AM24] + [AO24] + [AQ24] + [AS24] + [AU24] + [AW24] + [AY24] + [BA24] + [BC24] + [BE24] + [BG24] + [BI24] + [BK24] + [BM24] + [BO24] + [BQ24] + [BS24]
Range("J64") = [J25] + [L25] + [N25] + [P25] + [R25] + [T25] + [V25] + [X25] + [Z25] + [AB25] + [AD25] + [AF25] + [AH25] + [AJ25] + [AL25] + [AN25] + [AP25] + [AR25] + [AT25] + [AV25] + [AX25] + [AZ25] + [BB25] + [BD25] + [BF25] + [BH25] + [BJ25] + [BL25] + [BN25] + [BP25] + [BR25]
Range("K64") = [K25] + [M25] + [O25] + [Q25] + [S25] + [U25] + [W25] + [Y25] + [AA25] + [AC25] + [AE25] + [AG25] + [AI25] + [AK25] + [AM25] + [AO25] + [AQ25] + [AS25] + [AU25] + [AW25] + [AY25] + [BA25] + [BC25] + [BE25] + [BG25] + [BI25] + [BK25] + [BM25] + [BO25] + [BQ25] + [BS25]
Range("J65") = [J26] + [L26] + [N26] + [P26] + [R26] + [T26] + [V26] + [X26] + [Z26] + [AB26] + [AD26] + [AF26] + [AH26] + [AJ26] + [AL26] + [AN26] + [AP26] + [AR26] + [AT26] + [AV26] + [AX26] + [AZ26] + [BB26] + [BD26] + [BF26] + [BH26] + [BJ26] + [BL26] + [BN26] + [BP26] + [BR26]
Range("K65") = [K26] + [M26] + [O26] + [Q26] + [S26] + [U26] + [W26] + [Y26] + [AA26] + [AC26] + [AE26] + [AG26] + [AI26] + [AK26] + [AM26] + [AO26] + [AQ26] + [AS26] + [AU26] + [AW26] + [AY26] + [BA26] + [BC26] + [BE26] + [BG26] + [BI26] + [BK26] + [BM26] + [BO26] + [BQ26] + [BS26]
Range("J66") = [J27] + [L27] + [N27] + [P27] + [R27] + [T27] + [V27] + [X27] + [Z27] + [AB27] + [AD27] + [AF27] + [AH27] + [AJ27] + [AL27] + [AN27] + [AP27] + [AR27] + [AT27] + [AV27] + [AX27] + [AZ27] + [BB27] + [BD27] + [BF27] + [BH27] + [BJ27] + [BL27] + [BN27] + [BP27] + [BR27]
Range("K66") = [K27] + [M27] + [O27] + [Q27] + [S27] + [U27] + [W27] + [Y27] + [AA27] + [AC27] + [AE27] + [AG27] + [AI27] + [AK27] + [AM27] + [AO27] + [AQ27] + [AS27] + [AU27] + [AW27] + [AY27] + [BA27] + [BC27] + [BE27] + [BG27] + [BI27] + [BK27] + [BM27] + [BO27] + [BQ27] + [BS27]
Range("J67") = [J28] + [L28] + [N28] + [P28] + [R28] + [T28] + [V28] + [X28] + [Z28] + [AB28] + [AD28] + [AF28] + [AH28] + [AJ28] + [AL28] + [AN28] + [AP28] + [AR28] + [AT28] + [AV28] + [AX28] + [AZ28] + [BB28] + [BD28] + [BF28] + [BH28] + [BJ28] + [BL28] + [BN28] + [BP28] + [BR28]
Range("K67") = [K28] + [M28] + [O28] + [Q28] + [S28] + [U28] + [W28] + [Y28] + [AA28] + [AC28] + [AE28] + [AG28] + [AI28] + [AK28] + [AM28] + [AO28] + [AQ28] + [AS28] + [AU28] + [AW28] + [AY28] + [BA28] + [BC28] + [BE28] + [BG28] + [BI28] + [BK28] + [BM28] + [BO28] + [BQ28] + [BS28]
Range("J68") = [J29] + [L29] + [N29] + [P29] + [R29] + [T29] + [V29] + [X29] + [Z29] + [AB29] + [AD29] + [AF29] + [AH29] + [AJ29] + [AL29] + [AN29] + [AP29] + [AR29] + [AT29] + [AV29] + [AX29] + [AZ29] + [BB29] + [BD29] + [BF29] + [BH29] + [BJ29] + [BL29] + [BN29] + [BP29] + [BR29]
Range("K68") = [K29] + [M29] + [O29] + [Q29] + [S29] + [U29] + [W29] + [Y29] + [AA29] + [AC29] + [AE29] + [AG29] + [AI29] + [AK29] + [AM29] + [AO29] + [AQ29] + [AS29] + [AU29] + [AW29] + [AY29] + [BA29] + [BC29] + [BE29] + [BG29] + [BI29] + [BK29] + [BM29] + [BO29] + [BQ29] + [BS29]
Range("J69") = [J30] + [L30] + [N30] + [P30] + [R30] + [T30] + [V30] + [X30] + [Z30] + [AB30] + [AD30] + [AF30] + [AH30] + [AJ30] + [AL30] + [AN30] + [AP30] + [AR30] + [AT30] + [AV30] + [AX30] + [AZ30] + [BB30] + [BD30] + [BF30] + [BH30] + [BJ30] + [BL30] + [BN30] + [BP30] + [BR30]
Range("K69") = [K30] + [M30] + [O30] + [Q30] + [S30] + [U30] + [W30] + [Y30] + [AA30] + [AC30] + [AE30] + [AG30] + [AI30] + [AK30] + [AM30] + [AO30] + [AQ30] + [AS30] + [AU30] + [AW30] + [AY30] + [BA30] + [BC30] + [BE30] + [BG30] + [BI30] + [BK30] + [BM30] + [BO30] + [BQ30] + [BS30]
Range("J70") = [J31] + [L31] + [N31] + [P31] + [R31] + [T31] + [V31] + [X31] + [Z31] + [AB31] + [AD31] + [AF31] + [AH31] + [AJ31] + [AL31] + [AN31] + [AP31] + [AR31] + [AT31] + [AV31] + [AX31] + [AZ31] + [BB31] + [BD31] + [BF31] + [BH31] + [BJ31] + [BL31] + [BN31] + [BP31] + [BR31]
Range("K70") = [K31] + [M31] + [O31] + [Q31] + [S31] + [U31] + [W31] + [Y31] + [AA31] + [AC31] + [AE31] + [AG31] + [AI31] + [AK31] + [AM31] + [AO31] + [AQ31] + [AS31] + [AU31] + [AW31] + [AY31] + [BA31] + [BC31] + [BE31] + [BG31] + [BI31] + [BK31] + [BM31] + [BO31] + [BQ31] + [BS31]
Range("J71") = [J32] + [L32] + [N32] + [P32] + [R32] + [T32] + [V32] + [X32] + [Z32] + [AB32] + [AD32] + [AF32] + [AH32] + [AJ32] + [AL32] + [AN32] + [AP32] + [AR32] + [AT32] + [AV32] + [AX32] + [AZ32] + [BB32] + [BD32] + [BF32] + [BH32] + [BJ32] + [BL32] + [BN32] + [BP32] + [BR32]
Range("K71") = [K32] + [M32] + [O32] + [Q32] + [S32] + [U32] + [W32] + [Y32] + [AA32] + [AC32] + [AE32] + [AG32] + [AI32] + [AK32] + [AM32] + [AO32] + [AQ32] + [AS32] + [AU32] + [AW32] + [AY32] + [BA32] + [BC32] + [BE32] + [BG32] + [BI32] + [BK32] + [BM32] + [BO32] + [BQ32] + [BS32]
Range("J72") = [J33] + [L33] + [N33] + [P33] + [R33] + [T33] + [V33] + [X33] + [Z33] + [AB33] + [AD33] + [AF33] + [AH33] + [AJ33] + [AL33] + [AN33] + [AP33] + [AR33] + [AT33] + [AV33] + [AX33] + [AZ33] + [BB33] + [BD33] + [BF33] + [BH33] + [BJ33] + [BL33] + [BN33] + [BP33] + [BR33]
Range("K72") = [K33] + [M33] + [O33] + [Q33] + [S33] + [U33] + [W33] + [Y33] + [AA33] + [AC33] + [AE33] + [AG33] + [AI33] + [AK33] + [AM33] + [AO33] + [AQ33] + [AS33] + [AU33] + [AW33] + [AY33] + [BA33] + [BC33] + [BE33] + [BG33] + [BI33] + [BK33] + [BM33] + [BO33] + [BQ33] + [BS33]
Range("J73") = [J34] + [L34] + [N34] + [P34] + [R34] + [T34] + [V34] + [X34] + [Z34] + [AB34] + [AD34] + [AF34] + [AH34] + [AJ34] + [AL34] + [AN34] + [AP34] + [AR34] + [AT34] + [AV34] + [AX34] + [AZ34] + [BB34] + [BD34] + [BF34] + [BH34] + [BJ34] + [BL34] + [BN34] + [BP34] + [BR34]
Range("K73") = [K34] + [M34] + [O34] + [Q34] + [S34] + [U34] + [W34] + [Y34] + [AA34] + [AC34] + [AE34] + [AG34] + [AI34] + [AK34] + [AM34] + [AO34] + [AQ34] + [AS34] + [AU34] + [AW34] + [AY34] + [BA34] + [BC34] + [BE34] + [BG34] + [BI34] + [BK34] + [BM34] + [BO34] + [BQ34] + [BS34]
Range("J74") = [J35] + [L35] + [N35] + [P35] + [R35] + [T35] + [V35] + [X35] + [Z35] + [AB35] + [AD35] + [AF35] + [AH35] + [AJ35] + [AL35] + [AN35] + [AP35] + [AR35] + [AT35] + [AV35] + [AX35] + [AZ35] + [BB35] + [BD35] + [BF35] + [BH35] + [BJ35] + [BL35] + [BN35] + [BP35] + [BR35]
Range("K74") = [K35] + [M35] + [O35] + [Q35] + [S35] + [U35] + [W35] + [Y35] + [AA35] + [AC35] + [AE35] + [AG35] + [AI35] + [AK35] + [AM35] + [AO35] + [AQ35] + [AS35] + [AU35] + [AW35] + [AY35] + [BA35] + [BC35] + [BE35] + [BG35] + [BI35] + [BK35] + [BM35] + [BO35] + [BQ35] + [BS35]
Range("J75") = [J36] + [L36] + [N36] + [P36] + [R36] + [T36] + [V36] + [X36] + [Z36] + [AB36] + [AD36] + [AF36] + [AH36] + [AJ36] + [AL36] + [AN36] + [AP36] + [AR36] + [AT36] + [AV36] + [AX36] + [AZ36] + [BB36] + [BD36] + [BF36] + [BH36] + [BJ36] + [BL36] + [BN36] + [BP36] + [BR36]
Range("K75") = [K36] + [M36] + [O36] + [Q36] + [S36] + [U36] + [W36] + [Y36] + [AA36] + [AC36] + [AE36] + [AG36] + [AI36] + [AK36] + [AM36] + [AO36] + [AQ36] + [AS36] + [AU36] + [AW36] + [AY36] + [BA36] + [BC36] + [BE36] + [BG36] + [BI36] + [BK36] + [BM36] + [BO36] + [BQ36] + [BS36]
Range("J76") = [J37] + [L37] + [N37] + [P37] + [R37] + [T37] + [V37] + [X37] + [Z37] + [AB37] + [AD37] + [AF37] + [AH37] + [AJ37] + [AL37] + [AN37] + [AP37] + [AR37] + [AT37] + [AV37] + [AX37] + [AZ37] + [BB37] + [BD37] + [BF37] + [BH37] + [BJ37] + [BL37] + [BN37] + [BP37] + [BR37]
Range("K76") = [K37] + [M37] + [O37] + [Q37] + [S37] + [U37] + [W37] + [Y37] + [AA37] + [AC37] + [AE37] + [AG37] + [AI37] + [AK37] + [AM37] + [AO37] + [AQ37] + [AS37] + [AU37] + [AW37] + [AY37] + [BA37] + [BC37] + [BE37] + [BG37] + [BI37] + [BK37] + [BM37] + [BO37] + [BQ37] + [BS37]
Range("J77") = [J38] + [L38] + [N38] + [P38] + [R38] + [T9] + [V38] + [X38] + [Z38] + [AB38] + [AD38] + [AF38] + [AH38] + [AJ38] + [AL38] + [AN38] + [AP38] + [AR38] + [AT38] + [AV38] + [AX38] + [AZ38] + [BB38] + [BD38] + [BF38] + [BH38] + [BJ38] + [BL38] + [BN38] + [BP38] + [BR38]
Range("K77") = [K38] + [M38] + [O38] + [Q38] + [S38] + [U9] + [W38] + [Y38] + [AA38] + [AC38] + [AE38] + [AG38] + [AI38] + [AK38] + [AM38] + [AO38] + [AQ38] + [AS38] + [AU38] + [AW38] + [AY38] + [BA38] + [BC38] + [BE38] + [BG38] + [BI38] + [BK38] + [BM38] + [BO38] + [BQ38] + [BS38]
Range("J78") = [J39] + [L39] + [N39] + [P39] + [R39] + [T39] + [V39] + [X39] + [Z39] + [AB39] + [AD39] + [AF39] + [AH39] + [AJ39] + [AL39] + [AN39] + [AP39] + [AR39] + [AT39] + [AV39] + [AX39] + [AZ39] + [BB39] + [BD39] + [BF39] + [BH39] + [BJ39] + [BL39] + [BN39] + [BP39] + [BR39]
Range("K78") = [K39] + [M39] + [O39] + [Q39] + [S39] + [U39] + [W39] + [Y39] + [AA39] + [AC39] + [AE39] + [AG39] + [AI39] + [AK39] + [AM39] + [AO39] + [AQ39] + [AS39] + [AU39] + [AW39] + [AY39] + [BA39] + [BC39] + [BE39] + [BG39] + [BI39] + [BK39] + [BM39] + [BO39] + [BQ39] + [BS39]
End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
Hi!
Corect me if I'm wrong. I have noticed that you are trying to add cell every other cell at a regular interval at the same row.
If so, I have made a funtion to do that.
Code:
Function Sumx(StartRange As Range, Endrange As Range, Interval As Integer)
Dim tmp
    tmp = 0
    For i = StartRange.Column To Endrange.Column Step Interval
        tmp = tmp + Cells(StartRange.Row, i)
    Next i
    Sumx = tmp
End Function

To use this funtion you only need to input the starting range and the ending range. example

Code:
Range("J44") = Sumx(range( "j5"),range("br5"),2)
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, youve certainly put a fair bit of effort in there. :biggrin:

Heres a version which will hopefully do the same thing. Please test it out on a copy of your workbook to be sure.

Code:
Sub Test()
Dim Rw As Long, SumRw As Long, SumRng As String, SumRng2 As String

SumRw = 5

For Rw = 44 To 78
    SumRng = "J" & SumRw & ":BR" & SumRw
    SumRng2 = "K" & SumRw & ":BS" & SumRw
    Cells(Rw, 10) = Evaluate("SUMPRODUCT((" & SumRng & ")*(MOD(COLUMN(" & SumRng & "),2)=0))")
    Cells(Rw, 11) = Evaluate("SUMPRODUCT((" & SumRng2 & ")*(MOD(COLUMN(" & SumRng2 & "),2)=1))")
    SumRw = SumRw + 1
Next Rw

End Sub

EDIT:
Why dont you just have a formula in J44:K78?

In cell J44 and copy down
=SUMPRODUCT(($J5:$BR5)*(MOD(COLUMN($J5:$BR5),2)=0))

In cell K44 and copy down
=SUMPRODUCT(($K5:$BS5)*(MOD(COLUMN($K5:$BS5),2)=1))
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Is this the correct way to use this? I keep getting an error expected: ), and the "L44" part of the code is highlighted.
Private Sub CommandButton33_Click()
Function Sumx(Range("L44") = Sumx(range("j5"),range("br5"),2)
Dim tmp
tmp = 0
For i = StartRange.Column To Endrange.Column Step Interval
tmp = tmp + Cells(StartRange.Row, i)
Next i
Sumx = tmp
End Function
You are correct in your assumption. I am trying to add all the even columns from a single row together. I am also adding the odd columns together. Hopefully I can put two parts of the sheet on this post to better explain.
inventory_ sheet_for_hank.xls
IJKLMNOPQ
2ADDREMOVEADDREMOVEADDREMOVEADDREMOVE
3
4
5ANTI-FOAMAEMULSION4453535435
6ATRAZINEHERBICIDE35353535
7LAN95TECH45353545
85-5-3OMRI35353535
914-1-6LAWNACTIVEFERTILIZER45353545
10NUTRALENE40-0-035353535
Sheet1
inventory_ sheet_for_hank.xls
IJKL
41
42
43
44ANTI-FOAMAEMULSION762115
45ATRAZINEHERBICIDE69115
46LAN95TECH86115
475-5-3OMRI69115
4814-1-6LAWNACTIVEFERTILIZER86115
49NUTRALENE40-0-069115
Sheet1

The buttons might not show so I'll tell you where they are first. Keep in mind these are only small parts of the sheet. On the second example I placed a button (January) right above the two columns with figures in them. When I press the January button it would add all the add columns from the first example above for a particular product. Then it would add all the data from the remove columns for a particular product. The first example above has buttons also. Each was given a number from 1-31 representing the days off the month. When the end of the month comes you will press say January, and everything will get summed up for the month. This is the one thing I was really curious about in your code. How do I continue adding additional rows? The January command button is meant to give a total for every product. So of course I am working on the sum of several rows, but not a great big lump sum of all the products. Do I have to continuously redo the entire code, or is there a way to just add the others in? My guess would be the latter, but had to ask. Can you give an example of this when you give an example for combining your original code with your sample code from above? Thanks for reading this really long drawn out explanation. I really appreciate the help.
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

ok.
You pasted the code wrong.
You paste the code I posted outside the command button procedure.It should look like this.

Code:
Function Sumx(StartRange As Range, Endrange As Range, Interval As Integer) 
Dim tmp 
    tmp = 0 
    For i = StartRange.Column To Endrange.Column Step Interval 
        tmp = tmp + Cells(StartRange.Row, i) 
    Next i 
    Sumx = tmp 
End Function 

Private Sub CommandButton32_Click() 
Range("J44") = Sumx(range( "j5"),range("br5"),2) 
' more addition here
end sub
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, I presume you are replying to Sixth Sense's code? The problem with your code is that your including a function code within procedure code so poor XL doesnt know whats what.

So using Sixth Sense's option you will need 2 separate procedures - one a Function and the other a Sub while your code is trying to incorporate both. If you use my code you only need the procedure Ive shown and thats it. Its just changing the row numbers for a SumProduct formula. I dont see why you just dont use a formula?

hth
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395

ADVERTISEMENT

I haven't tried a formula because I assume that the formulas for the two columns under January would be pretty much the same as the formulas for the two columns in the following months. Maybe there is a way to do this, but the problem I see in doing this is that formulas automatically update themselves. When I finished January's columns and started into February wouldn't the information in January be lost, and replaced with the new information I put into February? If there is a way to do this with a formula please share. Seriously! I have 31 days set up now each with two columns (add,remove). Each day is labeled using a command button. When the command button is pressed a vb code runs a copy paste routine I set up. This is used to get information from another table not shown here. When all the days are finished being filled for the month I have planned to put there sums under the month buttons discussed here. This will allow me to reuse the columns I already have set up for the days of the month. I haven't tried these new codes you posted yet, but I will in the next few minutes. Will post back with my results. Thanks for the help guys.
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Thanks Sixth Sense that worked perfectly. A couple of more steps, and I will finally be finished with this workbook. Thanks for everyones help.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
I get the impression you dont understand the code I posted and havent even tried it. For the way you appear to be doing it you will still end up with 70 lines of code in your click event procedure - being one line for every cell you want to place a value. My procedure has 8 lines of code but if you want to go the long way then thats up to you.

The code I posted uses the SumProduct formula in code, but places the value from the formula result, not an actual formula in the destination cells. This uses a loop to change the destination cells rather than entering in 70 exact addresses for the destination cells.

Perhaps you got confused. I am offering 2 solutions 1 using code to place the value and another option suggesting plain formulas and not code. You can change formulas into values by copying and then paste special over itself if you want to avoid code but you may not want to do that if this is a regular thing & a macro makes more sense.

Good luck on your project.
 

Forum statistics

Threads
1,141,715
Messages
5,708,046
Members
421,540
Latest member
quocbinh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top