Excel Macro: Matching and calculating 2 worksheets

[mayanair]

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

 

[mudraker]

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