Getting column number of cell with particular text using vba

Question

screeshot of the excel 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.

i have attached the screenshot.The requirement is that if i select two values from dropdown then two values will be added to cell with "+".Suppose if try to edit the cell with some new value which is not in the list then i m facing the issue.The result is like the second cell in the attached sheet.The values are getting replicated.I did not find any way to add the file,hence i added screenshot. — user1668653
– user1668653, Commented Mar 20, 2013 at 11:25

brettdj · Accepted Answer · 2013-03-17 09:59:50Z

8

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

answered Mar 17, 2013 at 9:59

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1668653 Over a year ago

Thanks its working.Could you please let me know how to edit the values in the dropdownlist on the fly instead of updating the list the in the excel?

user1668653 Over a year ago

Thanks its working.The problem i am facing is that i am applying the above logic on particular column.That Column's each cell is a drop down.For the first time i will select one value from dropdown and secondtime if i select other value in that dropdown from the same cell then the new value will appended with "+" symbol with the existing value.while adding the second value.Example i have selected a value "one" from the drop down.and if i want to add a new value something like "THOUSAND" to the drop down on the fly then i am seeing as one+one+THOUSAND instead of one+THOUSAND.

Collectives™ on Stack Overflow

Getting column number of cell with particular text using vba

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related