vba 来自列名的 Excel 列号

Excel column number from column name

提问by Paritosh Ahuja

How to get the column number from column name in Excel using Excel macro?

如何使用Excel宏从Excel中的列名中获取列号？

回答by Siddharth Rout

I think you want this?

我想你想要这个？

Column Name to Column Number

列名到列号

Sub Sample()
    ColName = "C"
    Debug.Print Range(ColName & 1).Column
End Sub

Edit: Also including the reverse of what you want

编辑：还包括您想要的反向

Column Number to Column Name

列号到列名

Sub Sample()
    ColNo = 3
    Debug.Print Split(Cells(, ColNo).Address, "$")(1)
End Sub

FOLLOW UP

跟进

Like if i have salary field at the very top lets say at cell C(1,1) now if i alter the file and shift salary column to some other place say F(1,1) then i will have to modify the code so i want the code to check for Salary and find the column number and then do rest of the operations according to that column number.

就像我在最顶部有薪水字段一样，现在可以在单元格 C(1,1) 说如果我更改文件并将薪水列移到其他地方说 F(1,1) 那么我将不得不修改代码我希望代码检查薪水并找到列号，然后根据该列号执行其余操作。

In such a case I would recommend using .FINDSee this example below

在这种情况下，我建议使用.FINDSee this example below

Option Explicit

Sub Sample()
    Dim strSearch As String
    Dim aCell As Range

    strSearch = "Salary"

    Set aCell = Sheet1.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        MsgBox "Value Found in Cell " & aCell.Address & _
        " and the Cell Column Number is " & aCell.Column
    End If
End Sub

SNAPSHOT

快照

回答by Grade 'Eh' Bacon

While you were looking for a VBA solution, this was my top result on google when looking for a formula solution, so I'll add this for anyone who came here for that like I did:

当您在寻找 VBA 解决方案时，这是我在 google 上寻找公式解决方案时的最佳结果，所以我会为任何像我一样来到这里的人添加这个：

Excel formula to return the number from a column letter(From @A. Klomp's comment above), where cell A1 holds your column letter(s):

Excel 公式从列字母返回数字（来自上面@A. Klomp 的评论），其中单元格 A1 包含您的列字母：

=column(indirect(A1&"1"))

=列（间接（A1&“1”））

As the indirect function is volatile, it recalculates whenever any cell is changed, so if you have a lot of these it could slow down your workbook. Consider another solution, such as the 'code' function, which gives you the number for an ASCII character, starting with 'A' at 65. Note that to do this you would need to check how many digits are in the column name, and alter the result depending on 'A', 'BB', or 'CCC'.

由于间接函数是不稳定的，它会在任何单元格发生更改时重新计算，因此如果您有很多这样的函数，它可能会减慢您的工作簿的速度。考虑另一种解决方案，例如“code”函数，它为您提供 ASCII 字符的编号，从 65 处的 'A' 开始。请注意，要执行此操作，您需要检查列名中有多少位数字，以及根据“A”、“BB”或“CCC”更改结果。

Excel formula to return the column letter from a number(From this previous question How to convert a column number (eg. 127) into an excel column (eg. AA), answered by @Ian), where A1 holds your column number:

Excel 公式从数字返回列字母（来自上一个问题How to convert a column number (eg. 127) into an excel column (eg. AA)，由@Ian 回答），其中 A1 保存您的列号：

=substitute(address(1,A1,4),"1","")

=substitute(address(1,A1,4),"1","")

Note that both of these methods work regardless of how many letters are in the column name.

请注意，无论列名中有多少个字母，这两种方法都有效。

Hope this helps someone else.

希望这对其他人有帮助。

回答by u3397819

Write and run the following code in the Immediate Window

在立即窗口中编写并运行以下代码

?cells(,"type the column name here").column

For example ?cells(,"BYL").columnwill return 2014. The code is case-insensitive, hence you may write ?cells(,"byl").columnand the output will still be the same.

例如?cells(,"BYL").column将返回 2014。代码不区分大小写，因此您可以编写?cells(,"byl").column并且输出仍然相同。

回答by loved.by.Jesus

Based on Anastasiya's answer. I think this is the shortest vba command:

基于 Anastasiya 的回答。我认为这是最短的 vba 命令：

Option Explicit

Sub Sample()
    Dim sColumnLetter as String
    Dim iColumnNumber as Integer

    sColumnLetter = "C"
    iColumnNumber = Columns(sColumnLetter).Column

    MsgBox "The column number is " & iColumnNumber
End Sub

Caveat: The only condition for this code to work is that a worksheet is active, because Columnsis equivalent to ActiveSheet.Columns. ;)

警告：此代码工作的唯一条件是工作表处于活动状态，因为Columns等效于ActiveSheet.Columns. ;)

回答by Spider IT

Here's a pure VBA solution because Excel can hold joined cells:

这是一个纯 VBA 解决方案，因为 Excel 可以保存连接的单元格：

Public Function GetIndexForColumn(Column As String) As Long
    Dim astrColumn()    As String
    Dim Result          As Long
    Dim i               As Integer
    Dim n               As Integer

    Column = UCase(Column)
    ReDim astrColumn(Len(Column) - 1)
    For i = 0 To (Len(Column) - 1)
        astrColumn(i) = Mid(Column, (i + 1), 1)
    Next
    n = 1
    For i = UBound(astrColumn) To 0 Step -1
        Result = (Result + ((Asc(astrColumn(i)) - 64) * n))
        n = (n * 26)
    Next
    GetIndexForColumn = Result
End Function

Basically, this function does the same as any Hex to Dec function, except that it only takes alphabetical chars (A = 1, B = 2, ...). The rightmost char counts single, each char to the left counts 26 times the char right to it (which makes AA = 27 [1 + 26], AAA = 703 [1 + 26 + 676]). The use of UCase() makes this function case-insensitive.

基本上，此函数与任何 Hex to Dec 函数的作用相同，只是它只接受字母字符 (A = 1, B = 2, ...)。最右边的字符是单个字符，左边的每个字符是右边字符的 26 倍（这使得 AA = 27 [1 + 26]，AAA = 703 [1 + 26 + 676]）。UCase() 的使用使这个函数不区分大小写。

回答by Troy

You could skip all this and just put your data in a table. Then refer to the table and header and it will be completely dynamic. I know this is from 3 years ago but someone may still find this useful.

你可以跳过这一切，把你的数据放在一个表中。然后参考表格和标题，它将是完全动态的。我知道这是 3 年前的，但有人可能仍然觉得这很有用。

Example code:

示例代码：

Activesheet.Range("TableName[ColumnName]").Copy

You can also use :

您还可以使用：

activesheet.listobjects("TableName[ColumnName]").Copy

You can even use this reference system in worksheet formulas as well. Its very dynamic.

您甚至可以在工作表公式中使用此参考系统。它非常有活力。

Hope this helps!

希望这可以帮助！

回答by FatBatman

In my opinion the simpliest way to get column number is:
Sub Sample() ColName = ActiveCell.Column MsgBox ColName End Sub

在我看来，获取列号的最简单方法是：
Sub Sample() ColName = ActiveCell.Column MsgBox ColName End Sub