Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28, AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

As usual I have made a mistake.

Excel column AA is not equal to 28
A1 --28
A2 -- 29
etc
Jack

"Jack" <replyto@it wrote in message
...
Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28, AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

am not sure what you mean exactly but one way might be:

k = ActiveCell.Column





On 23 Mar, 21:07, "Jack" <replyto@it wrote:
Hello,
* * * * * My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual *indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28, AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

I need to convert column index (shown as letters) to the number.
For example:
Column: ABCD
What is the corresponding number of that column?
Jack

"Jarek Kujawa" wrote in message
...
am not sure what you mean exactly but one way might be:

k = ActiveCell.Column





On 23 Mar, 21:07, "Jack" <replyto@it wrote:
Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28, AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

On Mar 23, 12:49 pm, "Jack" <replyto@it wrote:
I need to convert column index (shown as letters) to the number.
For example:
Column: ABCD
What is the corresponding number of that column?
Jack

"Jarek Kujawa" wrote in message

...
am not sure what you mean exactly but one way might be:

k = ActiveCell.Column

On 23 Mar, 21:07, "Jack" <replyto@it wrote:

Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28, AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

Hello Jack,

Use the Cells method to create a Range address and return only the
column number.

For example:
C = Cells(1, "ABCD").Column

Sincerely,
Leith Ross

Thank you.
That will work only, when my app is connected to Excel.
I need more general solution.
Jack

"Leith Ross" wrote in message
...
On Mar 23, 12:49 pm, "Jack" <replyto@it wrote:
I need to convert column index (shown as letters) to the number.
For example:
Column: ABCD
What is the corresponding number of that column?
Jack

"Jarek Kujawa" wrote in message

...
am not sure what you mean exactly but one way might be:

k = ActiveCell.Column

On 23 Mar, 21:07, "Jack" <replyto@it wrote:

Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28,
AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

Hello Jack,

Use the Cells method to create a Range address and return only the
column number.

For example:
C = Cells(1, "ABCD").Column

Sincerely,
Leith Ross

"Jack" <replyto@it wrote in message
...

As usual I have made a mistake.

Excel column AA is not equal to 28
A1 --28
A2 -- 29

Uh, no. You HAVE made a mistake, but you were closer the first time.

AA = 27
AB = 28

There's no such thing as column A1. A1 is a cell.

First, you have to consider whether you're going to support only version of
Excel before 2007, which only supported 256 columns (IX, or something like
that) or Excel 2007 as well, which supports...I think...1024 columns.

For the first case, where you can have at most two letters in the column
name, for anything beyond Z you have to take the first letter, convert it to
1 - 26, and multiply that by 26, then add the second letter.

For example, CQ = 3 * 26 + 17.

It's the same concept for the second case (Excel 2007), but now you have to
determine if you have 3 letters and multiply the first by 26 * 26 (26
squared) and then add the second * 26 and then the third.

In other words, you've got a base 26 number system.

"Jack" <replyto@it wrote in message
...

I need to convert column index (shown as letters)
to the number.
For example:
Column: ABCD
What is the corresponding number of that column?

Your question doesn't make sense! What are you going to do wth this "number"
when you have got it? What *exactly* are you trying to do, and why?

Mike

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=GetColNum("AA") returns 27 ("IV") returns 256

I don't run 2007 so can't test past "IV"


Gord

On Sun, 23 Mar 2008 16:49:18 -0400, "Jack" <replyto@it wrote:

I need to convert column index (shown as letters) to the number.
For example:
Column: ABCD
What is the corresponding number of that column?
Jack

"Jarek Kujawa" wrote in message
...
am not sure what you mean exactly but one way might be:

k = ActiveCell.Column





On 23 Mar, 21:07, "Jack" <replyto@it wrote:
Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28, AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

It does make sense, just again I've made a mistake.
In Excel 2007 the maximum column index is: XFD
There are not 4 letters long column's indexes.

I know how to do it now..
Jack


"Mike Williams" wrote in message
...
"Jack" <replyto@it wrote in message
...

I need to convert column index (shown as letters)
to the number.
For example:
Column: ABCD
What is the corresponding number of that column?

Your question doesn't make sense! What are you going to do wth this
"number" when you have got it? What *exactly* are you trying to do, and
why?

Mike

My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28, AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?

Not sure about "best", but the functions below should work well past any
thing you may want to handle. The ToNumber function (which is the one that
addresses your question) will convert letter combination up to BRUTMHYHIIZO
(which is converts to 9999999999999999). Likewise, the ToAlpha function
(which is the inverse of the ToNumber function) will accept values up to
9999999999999999 (which is converts to BRUTMHYHIIZO).

Rick

Function ToNumber(Value As String) As Variant
Dim x As Integer
If Format$(Value, "@@@@@@@@@@@@") "BRUTMHYHIIZO" _
Or Value Like "*[!A-Za-z]*" Then
ToNumber = -1
Else
ToNumber = CDec(0)
For x = Len(Value) To 1 Step -1
ToNumber = ToNumber + _
(Asc(UCase$(Mid$(Value, x, 1))) - 64) * _
26 ^ (Len(Value) - x)
Next
End If
End Function

Function ToAlpha(ByVal Value As Variant) As String
Dim AsciiValue As Variant
If Len(Value) 16 Or Value Like "*[!0-9]*" Then
ToAlpha = "###"
Else
Value = CDec(Value)
Do While Value 0
AsciiValue = CDec(64 + Value - 26 * Int(Value / 26))
If AsciiValue = 64 Then AsciiValue = 90
ToAlpha = Chr$(AsciiValue) & ToAlpha
Value = Int(Value / 26)
If AsciiValue = 90 Then Value = Value - 1
Loop
End If
End Function

Jack wrote:
It does make sense, just again I've made a mistake.
In Excel 2007 the maximum column index is: XFD
There are not 4 letters long column's indexes.

I know how to do it now..
Jack

<snip

Nice of you to tell the rest how you got there, but that's no mystery

I take it you finally, at last, worked out that there's 26 letters in
the alphabet, and you did some homework on powers and bases, and learnt
there's other counting systems than the decimal system

like binary, and octal, and hexadecimal, and ...

So 'A- Z' is 1 to 26

'AA' is 27, 'AB' is 28.... (like 26 + 1, 26 + 2)
The base is 26 ....
so each letter to the left is a power of 26

so 'ABC' is
26 * 26, (26^2 * 1 ('A')),
plus 26 * 2, (26^1 * 2 ('B')),
plus 1 * 3, (26^0 * 3 ('C'))

and 'XFD' then is 16384
or 2^14
or Hexadecimal 4000 - nice round number, innit?
strange that - 'XFD' being the last column .....

I think most of us in this newsgroup would know the above, but took you
a while to see it, didn't it

Problem solved.
Just removed the reference, saved the project, restarted the project and
added again the same reference.
Any thoughts on that?
Jack

"Jack" <replyto@it wrote in message
...
Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28, AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

Which reference did your remove and then add back in again? In one of your
previous responses, you said you needed to be able to convert the letters to
their numerical equivalent without being connected to Excel; that is, you
said you needed a general solution. What reference is associated with the
"general solution" you sought?

For your stated general solution, did you look at the ToNumber function I
posted in a previous response in this thread? While I admit this function
handles much, much more than you need to for your application, I would point
out that there is no time penalty in using it for the range of letters you
are interested it (the function only loops as many times as there are
letters in the argument passed into it).

Rick


"Jack" <replyto@it wrote in message
...
Problem solved.
Just removed the reference, saved the project, restarted the project and
added again the same reference.
Any thoughts on that?
Jack

"Jack" <replyto@it wrote in message
...
Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28, AB--29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

Sorry, it supposed to be a reply to the other thread.
Jack

"Rick Rothstein (MVP - VB)" wrote in
message ...
Which reference did your remove and then add back in again? In one of your
previous responses, you said you needed to be able to convert the letters
to their numerical equivalent without being connected to Excel; that is,
you said you needed a general solution. What reference is associated with
the "general solution" you sought?

For your stated general solution, did you look at the ToNumber function I
posted in a previous response in this thread? While I admit this function
handles much, much more than you need to for your application, I would
point out that there is no time penalty in using it for the range of
letters you are interested it (the function only loops as many times as
there are letters in the argument passed into it).

Rick


"Jack" <replyto@it wrote in message
...
Problem solved.
Just removed the reference, saved the project, restarted the project and
added again the same reference.
Any thoughts on that?
Jack

"Jack" <replyto@it wrote in message
...
Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28,
AB--29 etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

So, has the question you asked in **this** thread been resolved yet?

Rick


"Jack" <replyto@it wrote in message
...
Sorry, it supposed to be a reply to the other thread.
Jack

"Rick Rothstein (MVP - VB)" wrote in
message ...
Which reference did your remove and then add back in again? In one of
your previous responses, you said you needed to be able to convert the
letters to their numerical equivalent without being connected to Excel;
that is, you said you needed a general solution. What reference is
associated with the "general solution" you sought?

For your stated general solution, did you look at the ToNumber function I
posted in a previous response in this thread? While I admit this function
handles much, much more than you need to for your application, I would
point out that there is no time penalty in using it for the range of
letters you are interested it (the function only loops as many times as
there are letters in the argument passed into it).

Rick


"Jack" <replyto@it wrote in message
...
Problem solved.
Just removed the reference, saved the project, restarted the project and
added again the same reference.
Any thoughts on that?
Jack

"Jack" <replyto@it wrote in message
...
Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A --1, B --2..., AA--28,
AB--29 etc) must be used..

What will be the best method of converting those letters into numbers?
Jack