CODEWITHSUNDEEP
vbaexcel
paulinhax
paulinhax

Reputation: 602

vba: using combobox value as autofilter criteria

I'm trying to use a value from a Combo Box with a list of names as criteria of an AutoFilter and then copy the results from my database (BASE_ACOMPANHAMENTOS) to my results sheet (BUSCA). My Combo Box name is busca_lista and this is what I've tried so far:

Private Sub OKBUSCA_Click()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim lstrw As Long

Set ws = Sheets("BASE_ACOMPANHAMENTOS")
Set ws1 = Sheets("BUSCA")

lstrw = ws.Cells(Rows.Count, 2).End(xlUp).Row

ws1.Range("C12:H100").Clear
ws1.Range("X5").Value = busca_lista.Value

ws.Range("A1:F" & lstrw).AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=ws1.Range("X5"), CopyToRange:=ws1.Range("C11:H11"), Unique:=False

End Sub

As you guys can see, I'm copying the value from my Combo Box to one cell and then using this value as a criteria, but it isn't working because it's showing all the names in my database, not the name that I've searched for. You can see the example below:

The form:

enter image description here

My database: enter image description here

The results: enter image description here

Upvotes: 0

Views: 824

Answers (2)

QHarr
QHarr

Reputation: 84465

You have given the value to filter on but not the column. You need to have a cell which also holds the value Nome in in X4 and have your criteriarange as X4:X5

Upvotes: 0

Lisa
Lisa

Reputation: 552

Your CriteriaRange needs to include both column heading and values. Based on your code, I've added the heading "Nome" in X4. Your criteria range is X4:X5.

Private Sub OKBUSCA_Click()

    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim lstrw As Long

    Set ws = Sheets("BASE_ACOMPANHAMENTOS")
    Set ws1 = Sheets("BUSCA")

    lstrw = ws.Cells(Rows.Count, 2).End(xlUp).Row

    ws1.Range("C12:H100").Clear
    ws1.Range("X4").Value = "Nome"
    ws1.Range("X5").Value = busca_lista.Value

    ws.Range("A1:F" & lstrw).AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=ws1.Range("X4:X5"), CopyToRange:=ws1.Range("C11:H11"), Unique:=False

End Sub

Upvotes: 1

Related Questions