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
.
.
.
.


Therefore I want to have a button on the excel sheet which upon clicking the button the code will match the ID from Sheet1 with the ID in Sheet 2. It will then count the number of Status=Yes for each ID.

In the example above the results expected should be as below:

1111 = 3
2222 = 1
3333 = 0

It must be dynamic in nature as well as the number of cells may increase.

Hope to get a reply soon as ill need to come up with this function by today..thanks a bunch

[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