来自列名的 Excel 列号 [英] Excel column number from column name
问题描述
如何使用 Excel 宏从 Excel 中的列名中获取列号?
我想你想要这个?
列名到列号
子样本()列名 = "C"Debug.Print Range(ColName & 1).Column结束子
编辑:还包括你想要的反面
列号到列名
子样本()列号 = 3Debug.Print Split(Cells(, ColNo).Address, "$")(1)结束子
跟进
<块引用><块引用><块引用>就像如果我在最顶部有薪水字段,现在可以在单元格 C(1,1) 说,如果我更改文件并将薪水列移到其他地方说 F(1,1) 那么我将不得不修改代码,所以我希望代码检查薪水并找到列号,然后根据该列号执行其余操作.
在这种情况下,我建议使用 .FIND
请参见下面的示例
选项显式子样本()Dim strSearch As String将单元格调暗为范围strSearch = "薪水"Set aCell = Sheet1.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _MatchCase:=False, SearchFormat:=False)如果不是 aCell 什么都不是MsgBox "在单元格中找到的值" &aCell.Address &_" 并且单元格列号是 " &单元格列万一结束子
快照
How to get the column number from column name in Excel using Excel macro?
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.
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屋!