Search for column name in table, replace visible values only in the column

[Somewhere]

Hi

This is what I am trying to achieve:

1. Enter the column name to find into cell B2
2. Enter the value to populate into this column

into cell B3, however the table will be filtered so only visible cells are to be updated.

Column_Name 1 will always have a value present in each row so this can be used to set the range?

Thanks in advance.

Example Image:

https://www.dropbox.com/s/xbl9g7c2c6rjnps/IMG_6368.PNG?dl=0

 

[Fluff]

How about

Sub Somewhere()
   With ActiveSheet.ListObjects("[COLOR=#ff0000]Table1[/COLOR]").ListColumns(Range("B2").Value)
      .DataBodyRange.SpecialCells(xlVisible).Value = Range("B3").Value
   End With
End Sub

change table name to suit

 

[gallen]

Hello,

I can't view files (work firewall)

You don't give the name of the Table but maybe this little code can help.

It searches for a column the same as that in B2 and replaces all visible rows in that column with the data in B3
You should be able to amend this code to suit

Sub FindAndReplace()
    Dim tbl As ListObject 'variable to store the table
    Dim lc As ListColumn
    Dim FindColumn, ReplaceValue As String
    Dim Found As Boolean
    
    
    FindColumn = Range("B2")
    ReplaceValue = Range("B3")
    
    'assume we are searching the only table on the sheet. If not change accordingly
    Set tbl = Me.ListObjects(1)
    
    For Each lc In tbl.ListColumns
        If lc.Name = FindColumn Then
            lc.DataBodyRange.SpecialCells(xlCellTypeVisible) = ReplaceValue
            Found = True
            Exit Sub
        End If
    Next lc
    If Not Found Then MsgBox ("Column Not Found")
End Sub

 

[Somewhere]

Thanks guys for the help...both solutions work...

 

[Fluff]

Glad we could help & thanks for the feedback