ACCESSING A COLUMN BY NUMBER
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. 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.
A REAL WORLD PROBLEM
The need became apparent when a church contribution sheet was being used. 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. This effort or adding members was prone to equation errors.
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. 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.
The sheet's layout uses the first three columns holding the contributor names and their year to date total. 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 second row merges the second and third columns cells and displays the week ending date..
So I created a function called Numb2Colmn. Given an integer from 1 to 1024, the function returns the appropriate column string. 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
Examples
Numb2Colmn(1) returns "A", Numb2Colmn(28) returns "AB",
Numb2Colmn(1022) returns AMH and so on....
The Algorithm breaks the problem down into three groups
1. single column letters ("A" through "Z" or 1 - 26)
2. double column letters ("AA" through "ZZ" or 27 - 702)
3. three column letters ("AAA" through "AMJ" or 703 - 1024)
'function Numb2Colmn(integer)
'Convert a Number to column text value
'examples "1=A, 26=Z. 27=AA 53=BA 79=CA....702=ZZ 703=AAA AMJ=1024 (max column value)
'ALGORITHM
' ASCII CHARACTERS A thru Z are ascii 65 thru 90 for number values
' 1. from columns 1 to 26, single character column names are A thru Z using CHR(Cnum + 64)
' 2. columns 27 and above are multiples of 26 (A-Z)
' for columns 27 through 701 , divide column count (Cnum) by 26
' 2a. if the remainder is zero for the second column subtract one from the first column
' and force the last column to "Z"
' 2b. if remainder is non-zero subtract 26*whole integer of division from integer value
' rY=Cnum-(iY*26)
' Example 52 is an even multiple (2) of 26 and represents column AZ
' CHR(iY-1) & CHR("Z")
' Example 53 has a remainder of 1 and represents column BA
' CHR(iY) & CHR(rY)
' 3. This loop works up to 702 which is 27 multiples of 26
' 4. 703 is the first three character column string "AAA"
' Subtract 676 (702-26) from Cnum to retrieve the lower significant two column letters
' and force the highest significant letter to "A
function Numb2Col(CNum as integer)
'first case is single column letter values
if CNum <= 26 then
'single column
Numb2Col=Chr(Cnum+64)
endif
'second case is for double char letter values
'702 = "ZZ"
if Cnum >26 and Cnum<703 then
'return dual column AA - ZZ
' y=Cnum/26
iY=int(Cnum/26)
if iY=27 then
'special case Cnum=702
Numb2Col="AAA"
else
if Cnum-(iY*26)=0 then
iY=iY-1
rY=26
Numb2Col= (CHR(iY+64) & CHR(rY+64))
else
rY=Cnum-(iY*26)
Numb2Col= (CHR(iY+64) & CHR(rY+64))
endif
endif
endif
'from 702 ZZ to 703 AAA
if Cnum >=703 and Cnum < 1025 then
'three column
Cnum=Cnum-676 'subtract 702 less the first 26
'treat remaining values like two column letters
if Cnum >26 then
iY=int(Cnum/26)
if Cnum-(iY*26)=0 then
iY=iY-1
rY=26
Numb2Col= ("A" & CHR(iY+64) & CHR(rY+64))
else
rY=Cnum-(iY*26)
Numb2Col= ("A" & CHR(iY+64) & CHR(rY+64))
endif
endif
endif
if Cnum > 1024 then
'ERROR greater than column 1024 or 'AMJ'
Print("ERROR - Column value greater than 1024 or AMJ")
Numb2Col=""
endif
end function
