How to add a filter to VBA

[Romano_odK]

Good afternoom,

I got the following VBA code.

Mag1LocAAP.Caption = "AAP" & Chr(10) & Application.CountIf(Range("Locatie[Locatie]"), "=AAP")

I got a a table with the columns H, I and J. The value of column H (Magazijn) has be 1, column I (Locatie) has to be "AAP" and column J (Voorraad) has to be <>0. When all those values match then the line has to be counted
Can this be done?

Thank you for your time and have a great day.

 

[RoryA]

Yes, you can use CountIfs rather than CountIf:

Application.CountIfs(Range("Locatie[Locatie]"), "=AAP",Range("Locatie[Magazijn]"),1,Range("Locatie[Voorraad]"),"<>0")

 

[Romano_odK]

Yes, you can use CountIfs rather than CountIf:

Application.CountIfs(Range("Locatie[Locatie]"), "=AAP",Range("Locatie[Magazijn]"),1,Range("Locatie[Voorraad]"),"<>0")

That is great, thank you. Have a great day.

Yes, you can use CountIfs rather than CountIf:

Application.CountIfs(Range("Locatie[Locatie]"), "=AAP",Range("Locatie[Magazijn]"),1,Range("Locatie[Voorraad]"),"<>0")

I tried to add this line to the rest but it turns red. What could be the error here?

Private Sub worksheet_change(ByVal target As Range)
Negatief.Caption = "voorraad < 0" & Chr(10) & Application.CountIf(Range("Locatie[Voorraad]"), "<0")
Mag1LocAAP.Caption = "AAP" & Chr(10) & Application.CountIf(Range("Locatie[Locatie]"), "=AAP")
Werkvloer.Caption = "werkvloer" & Chr(10) & Application.CountIf(Range("Locatie[Locatie]"), "=Werkvloer")
Zweg.Caption = "z-weg" & Chr(10) & Application.CountIf(Range("Locatie[Locatie]"), "=z-weg")
'Range("J1").Value = Application.CountIf(Range("Locatie[Voorraad]"), "<0")
Application.CountIfs(Range("Locatie[Locatie]"), "=AAP",Range("Locatie[Magazijn]"),1,Range("Locatie[Voorraad]"),"<>0")
End Sub

 

[RoryA]

You were supposed to replace just the Application.Countif part in the original code line you posted with that Application.Countifs version.

 

[Romano_odK]

You were supposed to replace just the Application.Countif part in the original code line you posted with that Application.Countifs version.

Bit late, but thank you for your reply and solution.