Sum Frequencies in VBA in Excel

SailorJerry7030 · #1 04-30-2018, 07:10 AM

Quote:

Originally Posted by ArviLaanemets

Having Policy in column A, and months in columns B:M

Code:

=AND(SUM(B2)>0,C2>0,D2=0)+AND(SUM(C2)>0,D2>0,E2=0)+AND(SUM(D2)>0,E2>0,F2=0)+AND(SUM(E2)>0,F2>0,G2=0)+AND(SUM(F2)>0,G2>0,H2=0)+AND(SUM(G2)>0,H2>0,I2=0)+AND(SUM(H2)>0,I2>0,J2=0)+AND(SUM(I2)>0,J2>0,K2=0)+AND(SUM(J2)>0,K2>0,L2=0)+AND(SUM(K2)>0,L2>0,M2=0)+AND(SUM(L2)>0,M2>0)

Enter the formula into cell N2, and copy down.

Works like a charm

butttttttt is there a way to put this into VBA code? I only ask because this is going to apply to 96,000 records

ArviLaanemets · #2 04-30-2018, 08:08 AM

Quote:

Originally Posted by SailorJerry7030

is there a way to put this into VBA code? I only ask because this is going to apply to 96,000 records

Is the formula slowing the worksheet down? I ask because this is simple arithmetical formula which needs not much resources.

When yes, then of-course you can write a VBA procedure, which counts the number of rows in UsedRange, and processes then every datarow in your table, calcualtes same formula in VBA (you have to remember, that in VBA FALSE is 0 like in worksheet, but TRUE converts to -1, so you hafe to multiply the result with -1) for every row, and writes the result into proper column in your table. You can run the procedure from button on worksheet, or you can create a shortcut key combination to run it.

I myself don't have Office available for today and tomorrow.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word & Excel 2010 - Best Options To Auto Insert MySQL & Excel Data?	Hoser	Word	1	03-17-2017 03:47 PM
Embeding Excel Docs in Word - Receiving Memory Error Message if Excel is open	kdash	Word	0	05-06-2015 09:38 AM
How To Open an Macro From 2003 Excel in 2013 Excel Spread Sheet?	ADubin	Excel Programming	3	02-08-2015 04:57 AM
[Excel 2007] Building Power Point Slides from data in an Excel Table	bremen22	Excel Programming	1	08-07-2013 11:01 AM
Excel 2011 can't open old Excel 98 or Excel X files	FLJohnson	Excel	8	05-09-2012 11:26 PM