使用 vba 获取具有特定文本的单元格的列号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15457639/
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
Getting column number of cell with particular text using vba
提问by user1668653
Hi i need to get column of a cell with the text as ACTION.
嗨，我需要获取文本为 ACTION 的单元格列。
My current code is as below.
我目前的代码如下。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim actionColName As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& "+ " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
In the above code there is a condition as below If Target.Column = 3 Then
在上面的代码中有一个条件如下 If Target.Column = 3 Then
Instead of hard coding the value with 3 i would like to apply this logic for the complete column which contains the value ACTIONin one of its cell in that column.
我不想用 3 对值进行硬编码，我想将此逻辑应用于完整列， 该列在该列的其中一个单元格中包含值ACTION。
回答by brettdj
Use a Findto determine the (first) column containing Action
使用 aFind确定包含Action的（第一）列
Sub GetAction()
Dim rng1 As Range
Set rng1 = ActiveSheet.UsedRange.Find("Action", , xlValues, xlWhole)
If Not rng1 Is Nothing Then
MsgBox "Found in column " & rng1.Column
Else
MsgBox "Not found", vbCritical
End If
End Sub
