counting through the alphabet that is above 702..

[Jassen Cali]

Have a question.. I'm currently working on a project that's all about
updating excel documents with a current one through a program. since we're
dealing with spreadsheets that have a huge number of cells (some more than
702 sheets) I have to do everything through the program to make it faster..

I have to name the columns alphabetically just like excel does...
e.g. 1 = A, 702 = ZZ, 27 = AZ, so on and so forth..

now I got up to ZZ with one go.. forgive me if I show my solution in c#...
and if it's not optimized then forgive me as well since I'm still new at it.

string[] alphabet = new string[] { "A", "B", "C", "D", ............ , "Z" };
string[] alphabet2 = new string[] { "Z", "A", "B", "C", "D", ...... , "Y" };
//you'll understand why later...//

int input = 0;
int otherletter = 0;

if (input < 27)
{
output = alphabet[input - 1] //since the index starts at 0//
console.writeline(output);
}
else if (input > 26) //AA - ZZ or 27-702
{
otherletter = input / 26 //just get the whole number no
fraction..right letter//
input = input % 26; //modulus for the letter on the right//
output += alphabet2[input];
output += alphabet[otherletter];
console.writeline(output);
}

so for 702 (which is ZZ)
input = 702
input = 702 % 26 (which is zero...that is why Z is at index zero in alphabet2)
otherletter = 702 / 26 = 26.something (which uses the alphabet string array)

that isn't very nice to look at but it gets the job done.. the problem now
is what to do beyod 702? I already have a tentative solution.. which includes
using the number 702... it's almost the same as this solution just add
another "else if" but the problem is when I get to 18954 which is "ZZZ" and
so on and so forth?

does it really mean I have to make another else if everytime a letter is
added as the number goes higher? or is there a better logic other than my
approach? I'm sure there is... anybody out there? I don't care if the answer
isn't in C#... I'll just have to get the idea then maybe I could use it..
thanks.. sorry for the poor code work

 

[Nigel]

You can refer to cells by their numerical value thus avoiding the alpha
construct.

e.g Range("ZZ1") = Cells(1,702)

Note: that in the first case the column is first the row second, whilst the
later is Row, then Column

 

[Jassen Cali]

that's exactly the problem... since our instructor insists on using the alpha
construct.. since the output of the program is an XML based excel document we
need to reconstruct it the way it was and we need to find a logic to generate
the column names in a more optimized way..

 

[Ernst Schuurman]

maybe
cells(1,27).entirecolumn.address
or
left(cells(1,27).entirecolumn.address,instr(cells(1,27).entirecolumn.address,":")-1)

gives the column name

regards Ernst