title: Excel countif vba code with criteria resulting with values
body: <p>I am not sure if what I want to achieve is possible. So here it is:</p>
  
  <p>I got workbook with 2 sheets: </p>
  
  <p>First sheet cointains raw data with employees and trainings they did or did not take (they could not come to the the training). Sheets cointains few columns like: name, special ID (different for every employee), 2 blank columns, presence (yes/no), and few more... </p>
  
  <p>Second sheet will create report based on range and presence criteria.</p>
  
  <p>Technically it's something like that:</p>
  
  <p>Report sheet has list of employees, they will be filtered using autofilter. Those filtered employees will be checked if they were on 14 categories of trainings. Categories differs with range (ranges are known; with time ranges will be added or adjusted to newly added trainings).</p>
  
  <p><strong>My problem:</strong>
  Is it possible to create vba code that will check if employee was on certain trainings (countif in certain range with condition: not present = do not count) and paste the value to certain cells? If it is could you give some advice on how to do that? I am not asking for ready code.</p>
  
  <p><strong>Trying to make it work but I stuck at this. Error at line "if cells.find...".</strong></p>
  
  <pre><code>Sub Check()
  
  MyRange = Range("A1", Selection.End(xlDown))
  
  For Each MyCell In MyRange
  With Range("pp2dni2007")
      If Cells.Find(MyCell.Value) Is Nothing Then
  
      Else
          If ActiveCell.Value = ActiveCell.Offset(0, 3).Value Then
          MyCell.Offset(0, 6).Value = 1
  
          Else
          MyCell.Offset(0, 6).Value = 0
  
          End If
  
      End If
  
  End With
  Next
  
  End Sub
  </code></pre>
  
  <p><br>2nd edit, earlier code did infinite loop. Now I think if-statements reference to wrong range but not sure how to deal with it.</p>
  
  <pre><code>Sub Check()
  
  Dim MyRange As Range, MyCell As Variant
  
  Range("A1").Select
  
  Set MyRange = Range(Selection, Selection.End(xlDown)).Rows.SpecialCells(xlCellTypeVisible)
  
  For Each MyCell In MyRange.Cells
      With Range("pp2dni2007")
          If .Cells.Find(MyCell.Value) Is Nothing Then
  
          Else
              If .Cells.Find(MyCell.Value).Value = .Cells.Find(MyCell.Value).Offset(0, 3).Value Then
              MyCell.Offset(0, 6).Value = 1
  
              Else
              MyCell.Offset(0, 6).Value = 0
  
              End If
  
          End If
  
      End With
  Next
  
  End Sub
  </code></pre>
  
  <p>Sample workbook: <a href="https://dl.dropboxusercontent.com/u/7421442/sample%20workbook%20(1).xls" rel="nofollow">https://dl.dropboxusercontent.com/u/7421442/sample%20workbook%20(1).xls</a></p>