Excel列号从列名 [英] Excel column number from column name

问题描述

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

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

推荐答案

我想你想要这个？

列名称到列号

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

编辑：还包括你想要的相反的

Edit: Also including the reverse of what you want

列号到列名

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

关注

如果我在最上面有薪水字段，可以在单元格C（1,1）中说如果我改变文件并将薪资栏转移到其他地方说F（1,1），那么我将不得不修改代码，所以我希望代码检查薪水，并找到列号，然后执行其余的操作。

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.

在这种情况下将推荐使用 .FIND 请参阅下面的示例

In such a case I would recommend using .FIND See 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

这篇关于Excel列号从列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！