blocks|key|598421|text|您可以跳过所有这些操作，只需将数据放入表中。然后参考表格和标题，它将是完全动态的。我知道这是3年前的事了，但有些人可能仍然会觉得它很有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|598422|示例代码：|598423|Activesheet.Range("TableName[ColumnName]").Copy|code-block|syntax|javascript|598424|您还可以使用：|598425|activesheet.listobjects("TableName[ColumnName]").Copy|598426|您甚至可以在工作表公式中使用此参考系统。它非常有活力。|598427|希望这能有所帮助！|598428|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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. 

Example code: 

<pre><code>Activesheet.Range("TableName[ColumnName]").Copy
</code></pre>

You can also use : 

<pre><code>activesheet.listobjects("TableName[ColumnName]").Copy
</code></pre>

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

Hope this helps!

blocks|key|598479|text|在“即时”窗口中编写并运行以下代码|type|unstyled|depth|inlineStyleRanges|entityRanges|data|598480|?cells(,"type+the+column+name+here").column|code-block|syntax|javascript|598481|例如，?cells(,"BYL").column将返回2014。代码不区分大小写，因此您可以编写?cells(,"byl").column，但输出仍然是相同的。|offset|length|style|CODE|598482|entityMap^0|0|0|3|L|1C|L|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

Write and run the following code in the Immediate Window

<pre><code>?cells(,"type the column name here").column
</code></pre>

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

blocks|key|598522|text|在我看来，获取列号的最简单方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|598523|Sub+Sample()+++++ColName+=+ActiveCell.Column+++++MsgBox+ColName+End+Sub|offset|length|style|CODE|598524|entityMap^0|0|0|1Z|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

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

blocks|key|598563|text|根据阿纳斯塔西娅的回答。我认为这是最短的vba命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|598564|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|code-block|syntax|javascript|598565|注意:此代码工作的唯一条件是工作表处于活动状态，因为Columns等同于ActiveSheet.Columns。;)|offset|length|style|CODE|598566|entityMap^0|0|0|Q|7|10|J|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>Option Explicit

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

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

 MsgBox "The column number is " &amp; iColumnNumber
End Sub
</code></pre>

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

blocks|key|382714|text|这是一个纯粹的VBA解决方案，因为Excel可以保存联接的单元格：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|382715|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+%2B+1),+1)
++++Next
++++n+=+1
++++For+i+=+UBound(astrColumn)+To+0+Step+-1
++++++++Result+=+(Result+%2B+((Asc(astrColumn(i))+-+64)+*+n))
++++++++n+=+(n+*+26)
++++Next
++++GetIndexForColumn+=+Result
End+Function|code-block|syntax|javascript|382716|基本上，这个函数与任何十六进制到十进制函数的作用相同，只是它只接受字母字符(A+=+1，B=+2，...)。最右侧的字符计数为1，左侧的每个字符计数为右侧字符的26倍(这使得AA+=271%2B+26，AAA+=7031%2B+26+%2B+676)。UCase()的使用使得该函数不区分大小写。|382717|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>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
</code></pre>

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.

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

Excel column number from column name

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

问列名称中的Excel列号
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问列名称中的Excel列号EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问列名称中的Excel列号
EN