VBA code to give column number based on cell value

[baleshst]

Hi,

I am using the following code, on a checkbox. However i have manually enter column number (Field:=7) below . I would request someone to adjust the below code such that it would give the column number based on the cell content. ie, find a specific word in the worksheet and provide its column number.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=7, Criteria1:= _
"<>"
Else
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=7

End If

End Sub

 

[Legacy 143009]

How about:

Sub getColumnHeader()
  Dim r As Range, IsItThere As Range
  Set r = ActiveSheet.ListObjects("Table1").Range
  Set IsItThere = r.Find(What:="search_string", After:=r(1))
  If Not IsItThere Is Nothing Then
    MsgBox IsItThere.Column
  End If
End Sub

 

[baleshst]

How about:

Sub getColumnHeader()
  Dim r As Range, IsItThere As Range
  Set r = ActiveSheet.ListObjects("Table1").Range
  Set IsItThere = r.Find(What:="search_string", After:=r(1))
  If Not IsItThere Is Nothing Then
    MsgBox IsItThere.Column
  End If
End Sub

Not sure how to integrate into my vba code given above

 

[Legacy 143009]

Sub getColumnHeaderIndex()
  Dim r As Range, IsItThere As Range
  Dim tb As ListObject
 
  Set tb = ActiveSheet.ListObjects("Table1")
  Set r = tb.Range
  Set IsItThere = r.Find(What:="specific_word", After:=r(1))
 
  If Not IsItThere Is Nothing Then
    MsgBox tb.ListColumns(Cells(IsItThere.ListObject.Range.Row, IsItThere.Column).Value).Index
  End If
End Sub

Ok this will give the column number according to specific word. I don't know what you are trying to achieve. Write anything instead of "specific_word".

You are maybe looking for this:

Private Sub CheckBox1_Click()
  Dim r As Range, IsItThere As Range
  Dim tb As ListObject
  Dim i As Ingeter

  Set tb = ActiveSheet.ListObjects("Table1")
  Set r = tb.Range
  Set IsItThere = r.Find(What:="specific_word", After:=r(1))
 
  If Not IsItThere Is Nothing Then
    i = tb.ListColumns(Cells(IsItThere.ListObject.Range.Row, IsItThere.Column).Value).Index
  End If

  If CheckBox1.Value = True Then
    r.AutoFilter Field:=i, Criteria1:= _
    "<>"
  Else
    r.AutoFilter Field:=i
  End If
End Sub

 

[Alex Blakenburg]

@Flashbond, for your get Field Number line "i = ", try this:

 i = IsItThere.Column - r.Cells(1).Column + 1

 

[Legacy 143009]

@Flashbond, for your get Field Number line "i = ", try this:

 i = IsItThere.Column - r.Cells(1).Column + 1

Oh.. It gave always -1 when I tried it (without +1). I thought I was doing something wrong. Do ListColumn index start from 0? Is it the reason?
Then, maybe @baleshst needs the index number according to this index order...

 

[baleshst]

Sub getColumnHeaderIndex()
  Dim r As Range, IsItThere As Range
  Dim tb As ListObject
 
  Set tb = ActiveSheet.ListObjects("Table1")
  Set r = tb.Range
  Set IsItThere = r.Find(What:="specific_word", After:=r(1))
 
  If Not IsItThere Is Nothing Then
    MsgBox tb.ListColumns(Cells(IsItThere.ListObject.Range.Row, IsItThere.Column).Value).Index
  End If
End Sub

Ok this will give the column number according to specific word. I don't know what you are trying to achieve. Write anything instead of "specific_word".

You are maybe looking for this:

Private Sub CheckBox1_Click()
  Dim r As Range, IsItThere As Range
  Dim tb As ListObject
  Dim i As Ingeter

  Set tb = ActiveSheet.ListObjects("Table1")
  Set r = tb.Range
  Set IsItThere = r.Find(What:="specific_word", After:=r(1))
 
  If Not IsItThere Is Nothing Then
    i = tb.ListColumns(Cells(IsItThere.ListObject.Range.Row, IsItThere.Column).Value).Index
  End If

  If CheckBox1.Value = True Then
    r.AutoFilter Field:=i, Criteria1:= _
    "<>"
  Else
    r.AutoFilter Field:=i
  End If
End Sub

Ok this is what i am trying to do see jpeg below for reference

there is a table i have created along with some checkbox (unchecking would remove the filter) , I want the user to select a check box and it would automatically filter "x" in that column

 

[Alex Blakenburg]

It gave always -1 when I tried it (without +1). I thought I was doing something wrong. Do ListColumn index start from 0? Is it the reason?

You might want to check that, if your find string is in column 1 of the table and you leave the + 1 off it should return zero not -1. Column 1 of the table is column index 1 of the table.

 

[Legacy 143009]

This will work for you:

Private Sub CheckBox1_Click()
  Dim r As Range
  Dim tb As ListObject
  Dim i As Integer
  Dim ws As Worksheet
  Dim cb As CheckBox
  Dim searchString As String

  Set ws = ActiveSheet
  Set tb = ws.ListObjects("Table1")
  Set r = tb.Range

  For Each cb In ws.CheckBoxes
    searchString = cb.Caption
    i = tb.ListColumns(searchString).Index
    If cb.Value = 1 Then
      r.AutoFilter Field:=i, Criteria1:="<>"
    Else
      r.AutoFilter Field:=i
    End If
  Next
End Sub

 

[baleshst]

Thanks for your patience, really thankful for your help.

However, after pasting this code for Checkbox1, i clicked tick on the box.
Nothing happened, ideally it should filter 'x' and uncheck should remove the filter.