vba 将列字母转换为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19877693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting column letter to number
提问by user1902849
I found code to convert number to column letter.
我找到了将数字转换为列字母的代码。
How can I convert from column letter to number?
如何将列字母转换为数字？
Sub colLtr()
Dim mycolumn
mycolumn = 1000
Mcl = Left(Cells(1, mycolumn).Address(1, 0), InStr(1, Cells(1, mycolumn).Address(1, 0), "$") - 1)
MsgBox Mcl
End Sub
回答by ARich
You can reference columns by their letter like this:
您可以通过它们的字母引用列，如下所示：
Columns("A")
So to get the column number, just modify the above code like this:
所以要获取列号，只需像这样修改上面的代码：
Columns("A").Column
The above line returns an integer (1 in this case).
上面的行返回一个整数（在本例中为 1）。
So if you were using the variable mycolumnto store and reference column numbers, you could set the value this way:
因此，如果您使用变量mycolumn来存储和引用列号，则可以这样设置值：
mycolumn = Sheets("Sheet1").Columns("A").Column
And then you could reference your variable this way:
然后你可以这样引用你的变量：
Sheets("Sheet1").Columns(mycolumn)
or to reference a cell (A1):
或引用单元格 ( A1)：
Sheets("Sheet1").Cells(1,mycolumn)
or to reference a range of cells (A1:A10)you could use:
或者要引用一系列单元格 ( A1:A10)，您可以使用：
Sheets("Sheet1").Range(Cells(1,mycolumn),Cells(10,mycolumn))
回答by Gary's Student
To see the numerical equivalent of a letter-designated column:
要查看字母指定列的数字等效项：
Sub dural()
ltrs = "ABC"
MsgBox Cells(1, ltrs).Column
End Sub
回答by Sancarn
The answer given may be simple but it is massively sub-optimal, because it requires getting a Range and querying a property. An optimal solution would be as follows:
给出的答案可能很简单，但它在很大程度上是次优的，因为它需要获取 Range 并查询属性。最佳解决方案如下：
Function getColIndex(sColRef As String) As Long
Dim sum As Long, iRefLen As Long
sum = 0: iRefLen = Len(sColRef)
For i = iRefLen To 1 Step -1
sum = sum + Base26(Mid(sColRef, i)) * 26 ^ (iRefLen - i)
Next
getColIndex = sum
End Function
Private Function Base26(sLetter As String) As Long
Base26 = Asc(UCase(sLetter)) - 64 'fixed
End Function
Some examples:
一些例子：
getColIndex("A") '-->1
getColIndex("Z") '-->26
getColIndex("AA") '-->27
getColIndex("AZ") '-->52
getColIndex("AAA") '-->703
