问题描述:

can we pass the value by applying autofilter to a particular coloumn and pass the filter value to a combox userform in excel vba

if it is possible can you please provide the generalized code for the same

EDIT: Code from OP's comment:

Selection.AutoFilter

Range("A:A").Select

ActiveSheet.Range("A1:AL1000").AutoFilter Field:=1, Criteria1:="" & TextBox1.Text & "", Operator:=xlAnd

Range("b1").Select

Range("B:B").Copy

'ActiveCell.CurrentRegion.Select

'ActiveCell.CurrentRegion.Copy

Sheets("Data").Select

Range("B2").Select

ActiveSheet.Paste

网友答案:

Is this what you are trying? I have commented the code so you shouldn't have a problem in understanding the code. But if you do then simply post back.

Option Explicit

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim copyFrom As Range, aCell As Range
    Dim lRow As Long
    Dim strSearch As String

    Set wb = ThisWorkbook
    '~~> Set this to the worksheet where the autofilter is applied
    Set ws = wb.Worksheets("yourSheetName")

    '~~> Filter Column on
    strSearch = TextBox1.Text

    With ws
        '~~> Remove any filters
        .AutoFilterMode = False

        With .Range("A1:AL1000")
            .AutoFilter Field:=1, Criteria1:="=" & strSearch

            '~~> Identify the filtered range
            Set copyFrom = .Offset(1, 1).SpecialCells(xlCellTypeVisible)

            '~~> Add filtered values from Column 2 i.e from
            '~~> Col B to Combobox
            For Each aCell In copyFrom
                If aCell.Column = 2 Then _
                ComboBox1.AddItem aCell.Value
            Next
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

Tip: You should avoid the use of .Select/.Activate You may wanna see THIS

网友答案:

Private Sub CommandButton2_Click()

Dim wb As Workbook
Dim ws As Worksheet
Dim copyFrom As Range, aCell As Range
Dim lRow As Long
Dim strSearch As String

Set wb = ThisWorkbook
'~~> Set this to the worksheet where the autofilter is applied
Set ws = wb.Worksheets("MT")



With ws
    '~~> Remove any filters
    .AutoFilterMode = False

    With .Range("A1:AL1000")
        .AutoFilter Field:=1, Criteria1:="*" & TextBox1.Text & "*", Operator:=xlAnd

        '~~> Identify the filtered range
        Set copyFrom = .Offset(1, 1).SpecialCells(xlCellTypeVisible)

        '~~> Add filtered values from Column 2 i.e from
        '~~> Col B to Combobox
        For Each aCell In copyFrom
            If aCell.Column = 2 Then _
            ComboBox1.AddItem aCell.Value
        Next
    End With

    '~~> Remove any filters
    .AutoFilterMode = False
End With

End Sub

This code is filtering the values but the filtered values are not coming in combo box .. ComboBox1.AddItem aCell.Value this line is giving error

相关阅读:
Top