Excel Combobox to filter a querytable 1

[RomeERome]

Hello All,

I am currently trying to filter a querytable in Excel 2007 by using an activex combobox that is not on a userform.

Below is a copy of my code that is not working. I get an error that telling me I need an object, but when I create an object it still doesn't work.

Private Sub ComboBox1_Change()
    Dim Sheet As Object
    With Data
        Sheet.Range("Table_AP2_Accuracy.accdb").AutoFilter Field:=2 _
        , Criteria1:=ComboBox1.Value
    End With
End Sub

The syntax is what my issue is.

Your assistance is greatly appreciated.

 

[SkipVought]

"any row" meaing the first visible cell in that column regardless if it has data in that cell or not?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

I mean once the data is filtered, row 1 could range from 1 to the last possible row of the worksheet.

 

[SkipVought]

Is row 1 not the heading row?

Are the visible rows contiguous, else PASTE does not work. Try doing what you want to do MANUALLY, once you have filtered your table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RomeERome]

201146 is the column heading of where the data needs to be written. Remember I was trying to use the reference in cell B12 of the AP2 Simulator worksheet, but it seems as though Excel can't make that type of correlation based on that cell value.

I know that you can paste data to only the visible rows or columns. I just don't know how to tell Excel the column where the data needs to be posted or how to do the comparison of the column header to that data.

I've been trying to figure it out while you are trying to help me.

 

[SkipVought]

just don't know how to tell Excel the column where the data needs to be posted

dim rFound as range, sSearchVal as string

sSearchVal = Sheets("AP2 Simulator").[B12]

set rFound = Sheets("Sheet2").rows(1).find(sSearchVal)

if not rFound is nothing then
   'found the column
else
   'column not found
end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}