Adding a new criteria to a filter

[beki_bt]

Hi,
I have a piece of code below. This filters my data sheet to find the number of "Original"s in Ragnge("K"). There are 3 different Values in Range(K):

1. REQUEST_ASSET_HOLDER
2. PREVIEW
3. ORIGINAL

I need the end result to check how many "Original"s and if In Range(I) same raw is equal to a specific Country(ie United Kingdom). In a nuttshell I need to filer through my data sheet to find if a specific raw contains "Original" and "United Kingdom"

Thank you in advance!

----------------------------------------------------------------------

Sub Filter1()

Range("K1").Activate

Linebegin:
ActiveCell.Offset(ROWOFFSET:=1).Activate

Dim count As Integer

Line0:
If ActiveCell.Value <> "PREVIEW" Then GoTo Line1
GoTo Linebegin

Line1:
If ActiveCell.Value <> "REQUEST_ASSET_HOLDER" Then GoTo Line2
GoTo Linebegin

Line2:
If ActiveCell.Value = "" Then GoTo Line3
count = count + 1
GoTo Linebegin

Line3:
Worksheets("BPM").Range("D25").Value = count

End Sub

[Bob Phillips]

I can't see that code does anything other than loop forever. There is absolutely no need to use Goto's there, but I cannot see what you are trying to do.

[beki_bt]

I can't see that code does anything other than loop forever. There is absolutely no need to use Goto's there, but I cannot see what you are trying to do.

Hi xID!

well it is looking for the values in Range(K) if they are not equal to ASSET_REQUEST_HOLDER and PREVIEW.
If so there is only one value left which is ORIGINAL hence, it will add 1 to the variable count.

So count will hold the number of occurences of ORIGINAL and assign it to a cell. It will loop that until the cell is empty "If ActiveCell.Value = "" Then GoTo Line3"

Line3 will end the Sub.

This might not be the most efficient way of doing it but Im new to VBA and can work only with the info I can find :).
Hope this clarifies.
Thanks!

[Bob Phillips]

Try this



Sub Filter1()
Dim rng As Range
Dim lastrow As Long
Dim count As Long

With ActiveSheet

lastrow = .Range("K1").End(xlDown).Row
count = Application.Evaluate("SUMPRODUCT(--(K1:K" & lastrow & "=""ORIGINAL""),--(R1:R" & lastrow & "=""United Kingdom""))")
End With

Worksheets("BPM").Range("D25").Value = count
End Sub