Filter Table and Insert Value by Userform VBA

Question

First post, so feedback on format appreciated. I want to add a value to a cell in a Table using a Userform. Combobox Value 'filters' Col 1, Textbox 1 Value 'filters Col 2 and the resulting Col 3 is the target cell for my value to input from textbox2.

Code below Shows a Compile Error: Sub or Function not defined ("Where" is highlighted).

Private Sub CommandButton1_Click()

Dim tbl As ListObject
Sheets("Sheet5").Activate
Set tbl = ActiveSheet.ListObject("Table1")
    With tbl.DataBodyRange.Cells(12, tbl.ListColumns("Cost").Index)
         .Value = TebxtBox2
            Where tbl.DataBodyRange.Cells(0, tbl.ListColumns("Friendly Name").Index) = ComboBox.Value _
                And tbl.DataBodyRange.Cells(11, tbl.ListColumns("Height").Index) = TextBox1.Value
    End With



End Sub

Thanks.....

Please add details of the compile errors, and try to cut this down to a minimal example: stackoverflow.com/help/mcve — GS - Apologise to Monica

RR33 RR33 · Accepted Answer · 2016-11-13T07:36:26

Got it! Code below filters the table and inserts the value. Macro is attached to a Command Button. The double 'Autofilter' lines clear table filter then add the filter arrows back.

Private Sub CommandButton1_Click()

    Dim skn As ListObject
    Set skn = ActiveSheet.ListObjects("Table1")
        With skn
        .Range.AutoFilter Field:=1, Criteria1:="Item 1"
        .Range.AutoFilter Field:=12, Criteria1:="80.5"
        .DataBodyRange.Columns(13).SpecialCells(xlCellTypeVisible).Select
         Selection.Value = 12
        .Range.AutoFilter
        .Range.AutoFilter


    End With
End Sub

Filter Table and Insert Value by Userform VBA

1 Answers