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

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


--

Regards,
Nigel
(E-Mail Removed)



"Jassen Cali" <Jassen (E-Mail Removed)> wrote in message
news:BB9B4283-C778-40DB-86BD-(E-Mail Removed)...
> 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
>

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..

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



"Jassen Cali" <(E-Mail Removed)> schreef in bericht
news:411CDC2C-5B5A-4936-B2AF-(E-Mail Removed)...
> 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..