I want to convert my calculations into a Macro

[Jack-P-Winner]

I have several sheets with the same type calculations going on that is bogging down my excel and causing it to freeze up several times an hour and normally for at least 3 to 4 minutes. needless to say its not productive. I would like to turn my formulas into a Macro. I think if someone can get it started for me I can finish it.



In columns B,R,AH,AX,BN,CD,CT,DJ,DZ,EP,FF,FV,GL,HB,HR,II,IY,JO I am counting how many cells are off to their right. I am not sure if how they work is of importance to the Macro but I really do not know so this is what they are doing. I count how many times a number repeats itself going vertically in the Gray and Yellow cells (CDEF for example) so D:20 and D:21 each has the number 2 so I put a 2 out to the left in column B:20.

D:22 to D:25 has 4 number 3's so I place a 4 in B:22. okay in all the columns mentioned above ( B,R,AH,AX,BN,CD,CT,DJ,DZ,EP,FF,FV,GL,HB,HR,II,IY,JO) I have the formula =SUMPRODUCT((J4:M4<>1)*(J3:M3=1),J4:M4) beginning in B:4 and this formula goes all the way down to row

63 with =SUMPRODUCT((J63:M63<>1)*(J62:M62=1),J63:M63)



The formulas are all the same in all the other columns just a different column letter.



In (J:3K:3,L:3,M:3), Z3,AO:3,BF:3,BT:3,CK:3,CY3,DP:3,EG:3,EV:3,FK:3,GA:3,GQ:3,HG:3,HW:3,IN:3,JD:3,JT:3

I have formulas for the gray and yellow cells (JKLM) in each group like these =IF(C3=C4,J4+1,1), =IF(D3=D4,K4+1,1), =IF(E3=E4,L4+1,1), =IF(F3=F4,M4+1,1) to =IF(C63=C64,J64+1,1), =IF(D63=D64,K64+1,1), =IF(E63=E64,L64+1,1), =IF(F63=F64,M64+1,1) using the 1st group as an example



In ( N, AD, AS, BJ, BX, CO, DC, DT, EK, EZ, FO, GE, GU, HK, IA, IR, JH, JX ) I have =ISODD(IFERROR(INDEX($C3:$F3,MATCH($B3,$J3:$M3,0)),1)) to =ISODD(IFERROR(INDEX($C63:$F63,MATCH($B63,$J63:$M63,0)),1)) which goes along with JKLM



How can a Macro be done to take care of these calculations instead of using the formulas? This is 1 sheet in a book of 5. I have a lot of conditional formatting also but I do not think that is bogging me down causing excel to freeze up



Thank you in advance for help as I know this is very time consuming so I wanted you to know in advance how much I appreciate your expertise and time.



I have attached a copy of 1 of the sheets with all the formula's in the appropriate places

 

[dan_l]

Maybe? This looks like something that could be achieved with arrays. I'd really need to understand your application though....

 

[Jack-P-Winner]

Maybe? This looks like something that could be achieved with arrays. I'd really need to understand your application though....

Hi Dan,

I am counting the number of cells are gray with green font and yellow with red font vertically. If I have six 2'a in rows 33 to 28 I place a 6 to the left. If you open the spreadsheet we can take column B, they are all the same C, R, AH, AX and so on. Column B has the total number of cells I am counting from CDEF vertically. You can see in B:28 I have a 2, B:27 I have a 10 and B:26 I have a 2. B:28 comes from having two number 1's (D:28 and D;29) the 10 in B:27 comes from ten 1's in column E (from E:27 to E:36)

does this make sense? That's what all of them do count the cells vertically

Is using an array better?

 

[dan_l]

Ehhhh maybe not. If you're not counting actual values and instead are counting formatting instances an array is probably not going to work.