Hi there,

I have not touched VB for a looooong time and hope someone could
refresh me on the codings.

I have 2 worksheets in Excel that looks like below:

Sheet 1
A.............B.............C
Name.......ID............Number of Status=Yes
John........1111
Julie........2222
Mary.......3333

Sheet 2
ID.............Status
1111..........Yes
2222..........No
1111..........Yes
3333..........No
1111..........No
2222..........Yes

try this code



Option Explicit

Sub CountMatches()
Dim wS1 As Worksheet
Dim wS2 As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim iCnt As Integer

Set wS1 = Sheets(1) 'set by sheet number = 1st sheet in workbook
Set wS2 = Sheets("Sheet2") 'set by sheet name
For Each Rng1 In wS1.Cells(wS1.Cells(Rows.Count, _
"a").End(xlUp).Row, "b")
iCnt = 0
If Rng1.Value < "" Then
For Each Rng2 In wS2.Cells(wS2.Cells(Rows.Count, _
"a").End(xlUp).Row, "b")
If Rng2.Value = Rng1.Value Then
If Rng2.Offset(0, 1).Value = "Yes" Then
iCnt = iCnt + 1
End If
End If
Next Rng2
End If
Rng1.Offset(0, 1).Value = iCnt
Next Rng1
End Sub


Note:-
If statements are case sensative

the _ allows VBA to have what is a single line of code entered over
several lines which makes it easy to read and avoids word wrap problems
when posting the code in forums

I have listed 2 different ways of setting worksheet variables with -
you can use either method


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=536676