Getting column number of cell with particular text using vba

excel, vba

Solution

Use a `Find` to determine the (first) column containing 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

Problem

Hi i need to get column of a cell with the text as 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 Instead of hard coding the value with 3 i would like to apply this logic for the complete column which contains the value ACTION in one of its cell in that column.

Original source