Using an Array to Feed a Filter

[ret44]

Hi Guys,

I'm attempting to use an array with the autofilter method. It seems like it's possible, but I haven't had luck so far. Basically, I know the array is getting created correctly, but when the autofilter method uses it, I'm getting no results (despite the fact that I know they're there).


Here's how I've currently tried it...

arr = inter.Range("f2:f" & InterCount).Value

ws.Range("a1:c" & RowCount).AutoFilter field:=2, Criteria1:=arr _
Operator:=xlFilterValues

Any help is greatly appreciated!

 

[wigi]

You were wrong in saying that your array is correct.

ws.Range("a1:c" & RowCount).AutoFilter field:=2, Criteria1:=WorksheetFunction.Transpose(arr), Operator:=xlFilterValues

 

[ret44]

Wow - Thanks much! Very good to know.

Unfortunately, I'm still not getting results.

I can do what I'm trying to do by looping through my data to check for results but that had seemed inefficient.

In any case - thanks for the tip, I'll hopefully figure it out at some point.

 

[wigi]

This code works perfectly for me, on the active sheet and with simplified ranges:

Sub autofilter_Wigi()

    arr = Range("A8:A10").Value
    Range("A1:C6").AutoFilter field:=2, Criteria1:=WorksheetFunction.Transpose(arr), Operator:=xlFilterValues

End Sub

Range B2:B6 has cell contents a, b, c, d, d
Range A8:A10 has cell contents a, b, c

Then the code only filters rows containing either a, b, c.

Wigi

 

[wigi]

Shortened code will read:

Sub autofilter_Wigi()
    [A1:C6].AutoFilter 2, WorksheetFunction.Transpose([A8:A10]), 7
End Sub

Wigi

 

[ret44]

I did get your code to work in an example workbook - so I know I can get the method to work at least.

If anyone wants to take a crack, here's the macro, in somewhat shortened form. It ends right on the array part, since that's as far as I've gotten.

Sub trueinv()
'
'Takes stock of raw products processed into others
'

'Worksheet Variables
Dim insert As Worksheet
Dim bom As Worksheet
Dim output As Worksheet
Dim purch As Worksheet
Dim inter As Worksheet

'Rowcount Variables
Dim BomCount As Integer
Dim InsCount As Integer
Dim InterCount As Integer
Dim PurchCount As Integer

'Loop Variable
Dim i As Integer

'Find Variables
Dim FoundRange As Range
Dim ItemId As String

'Add Stock variable
Dim AddStock As Double
Dim CurrentStock As Double

'Set sheet variables
Set insert = Sheets("insert")
Set bom = Sheets("boms")
Set output = Sheets("outputs")
Set purch = Sheets("purchased")
Set inter = Sheets("intermediate")


'Capture eligible products into their own sheet
InsCount = insert.UsedRange.Rows.Count

insert.Range("a1:f" & InsCount).AutoFilter field:=4, Criteria1:="=*eligible*", Operator:=xlAnd
    insert.Range("a1:f" & InsCount).Copy
    purch.Range("a1").PasteSpecial
    PurchCount = purch.UsedRange.Rows.Count
    
    insert.Range("a1:f" & InsCount).AutoFilter field:=4


'First Loop of Bom Check: Determine if items are actually BOM'd
For i = 2 To PurchCount

ItemId = purch.Cells(i, 1).Value

Set FoundRange = bom.Range("b1:b" & BomCount).Find(what:=ItemId, LookIn:=xlFormulas, lookat:=xlWhole)

'Not a Bom
If FoundRange Is Nothing Then
    purch.Rows(i).Copy
    output.Range("a12000").End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False

Else

'Check First level Boms
purch.Rows(i).Copy
    output.Range("a12000").End(xlUp).Offset(1, 0).PasteSpecial

bom.Range("a1:c" & BomCount).AutoFilter field:=2, Criteria1:="=" & ItemId, Operator:=xlAnd
BomCount = bom.UsedRange.Rows.Count


bom.Range("a2:c" & BomCount).Copy
    
'Add stock of first level boms
inter.Range("a2").PasteSpecial
    InterCount = inter.UsedRange.Rows.Count

inter.Range("d2:d" & InterCount).FormulaR1C1 = "=vlookup(rc[-3],insert!r2c1:r12000c6,6,false)"
    inter.Range("e2:e" & InterCount).FormulaR1C1 = "=rc[-2]*rc[-1]"

AddStock = Application.WorksheetFunction.Sum(inter.Range("e2:e" & InterCount))

CurrentStock = output.Range("a12000").End(xlUp).Offset(0, 5).Value

output.Range("a12000").End(xlUp).Offset(0, 5).Value = CurrentStock + AddStock

End If

'Determine if there are second level BOMs

Dim arr As Variant

InterCount = inter.UsedRange.Rows.Count
arr = inter.Range("a2:a" & InterCount).Value

BomCount = bom.UsedRange.Rows.Count

bom.Range("a1:c" & BomCount).AutoFilter field:=2, Criteria1:=WorksheetFunction.Transpose(arr), _
Operator:=xlFilterValues
    
End Sub

 

[wigi]

Loop through the code with F8, and inevitably it will show you more.

Inspect the variables in the Locals window and / or use Msgbox or Debug.Print to write to the Immediate Window.

Indent your code properly to spot inconsistencies or errors more quickly.

 

[ret44]

Thanks much for the Guidance Wigi - I didn't know about the locals window, so that will be very helpful.

I changed the filter operator to xlOr and it seemed to work correctly - so I hope that was the scope of the problem.