if remainder is non-zero subtract 26*whole integer of division from integer value if the remainder is zero for the second column subtract one from the first column
' for columns 27 through 701, divide column count (Cnum) by 26 columns 27 and above are multiples of 26 (A-Z) from columns 1 to 26, single character column names are A thru Z using CHR(Cnum + 64) ' ASCII CHARACTERS A thru Z are ascii 65 thru 90 for number values three column letters ("AAA" through "AMJ" or 703 - 1024) double column letters ("AA" through "ZZ" or 27 - 702)ģ. single column letters ("A" through "Z" or 1 - 26)Ģ. The Algorithm breaks the problem down into three groupsġ. Numb2Colmn(1) returns "A", Numb2Colmn(28) returns "AB", This allows me to build the same row and column constructs and pass numbers to repetitively access and format appropriate cells, column widths, and text Given an integer from 1 to 1024, the function returns the appropriate column string. So I created a function called Numb2Colmn. The second row merges the second and third columns cells and displays the week ending date. The next 52 groups that followed were groups of four (spacer column, 'cash', 'check' and 'other' contribution columns) 52 groups that represented the 52 week ending totals. The sheet's layout uses the first three columns holding the contributor names and their year to date total. The user need only run a dialog, select a year value and supply the number of the first Sunday for that year to get a new spreadsheet, pre-configured with equations using the right ranges and cell locations for 52 weeks of collections. Hence, I built a set of macros two automate the construction of the sheet to automate the sheet's construction and eliminate computational errors from the cut-and-paste technique. This effort or adding members was prone to equation errors. In early January of each year, the practice was to cut and paste the spreadsheet and update the week ending dates to create the next year's contribution spreadsheet.
The need became apparent when a church contribution sheet was being used. This was useful function to step through column by column, but I found myself in a situation to repetitively create groups of four columns with the same labels and equations of specific rows and it would be easier to work in integer loops to create the same formats over and over again. In my first edition of my calc macros book, I described a function that would return the next column name based on a current column name value.
0 kommentar(er)
