Searching a document for multiple criteria using VBA Macro

[Peteor]

Ok I have been stuck on this for a while, and hope someone can help. I have an Excel Workbook with useful data in Column A and Column K. I would like to accomplish the following using a VBA macro on Worksheet 30:

Dim D04C_MMF_Count As Integer

D04C_MMF_Count = 0

If the contents of a cell in column A = D04C AND the contents of a cell in column K = MMF, Then

D04C_MMF_Count = D04C_MMF_Count +1

Else
End If


I have Googled every resource I can find, and reviewed several threads, and I can't find a solution. I plan to scale this up to many more examples, but can't seem to get the Logic/Syntax right. I would like to search all cells in each column. It was just pointed out to me Concatenating strings MAY help, but I would like to avoid that if at all possible. Any input would be helpful. Thank You.

 

[Fluff]

How about

Sub Peteor()
   Dim D04C_MMF_Count As Long
   Dim Cl As Range

   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value = "D04C" And Cl.Offset(, 10).Value = "MMF" Then
         D04C_MMF_Count = D04C_MMF_Count + 1
      End If
   Next Cl
   MsgBox D04C_MMF_Count
End Sub

 

[bsquad]

with the assumption you are looking for a row match/combination on the same row as a count

Option Explicit
Sub CrapCount()
    Dim D04C_MMF_Count As Long
        D04C_MMF_Count = 0
    Dim ER As Long
        ER = ThisWorkbook.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    Dim Cell As Range
    Dim CriteriaRange As Range
        Set CriteriaRange = ThisWorkbook.ActiveSheet.Range("A1:A" & ER)
            For Each Cell In CriteriaRange
                If Cell.Value = "D04C" And Cell.Offset(, 10).Value = "MMF" Then
                    D04C_MMF_Count = D04C_MMF_Count + 1
                End If
            Next Cell
        MsgBox (D04C_MMF_Count)
End Sub

 

[Peteor]

Holy Smokes!!! You both are awesome!!!!! I ended up using bsquad's code, and it worked absolutely perfectly! It actually showed some inconsistencies in my data-set! You both rock!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Eric W]

Why not just:

D04C_MMF_Count = WorksheetFunction.CountIfs(Sheets("Worksheet 30").Range("A:A"), "D04C", Sheets("Worksheet 30").Range("K:K"), "MMF")