vba to count

[crabtree]

I need a VBA Code that will make the answer in Sheet1 A:1
do the following:

I have a table of data in Sheet2 A1:F50. I have four
values in Sheet3 Cells A1, B1, C1 and D1. I need the VBA
code to look down Sheet2 A1 and find all rows that match
Sheet3 A1. I then need it to further refine and look down
Sheet 2 B1 and pick only the rows that match Sheet3 B1.
Then, it needs to further refine and only pic the rows
whose value in Sheet2 C1 match Sheet3 C1. I then need it
to look out all the rows that match these criteria and
count the number of cells that contain the value in Sheet3
D1. Please help.

 

[Ed]

The following macro will AutoFilter your Sheet2 by the values in Sheet3.
The count will show in the status bar.

Sub MatchMe()

Dim strA1 As String
Dim strB1 As String
Dim strC1 As String
Dim strD1 As String

Worksheets("Sheet2").Activate
Range("A1").Select

' Store values from Sheet 3
strA1 = Worksheets("Sheet3").Range("A1").Value
strB1 = Worksheets("Sheet3").Range("B1").Value
strC1 = Worksheets("Sheet3").Range("C1").Value
strD1 = Worksheets("Sheet3").Range("D1").Value

' Inserts header row if not there
If MsgBox("Does your first row contain data?", vbYesNo) = vbYes Then
Worksheets("Sheet2").Rows(1).Insert
Cells(1, 1).Value = "Col 1"
Cells(1, 2).Value = "Col 2"
Cells(1, 3).Value = "Col 3"
Cells(1, 4).Value = "Col 4"
Range("A1").Select
End If

' Sets AutoFilter to Sheet3 values
Selection.AutoFilter Field:=1, Criteria1:=strA1
Selection.AutoFilter Field:=2, Criteria1:=strB1
Selection.AutoFilter Field:=3, Criteria1:=strC1
Selection.AutoFilter Field:=4, Criteria1:=strD1

End Sub

HTH
Ed

 

[Guest]

It seems to me you could use the DCOUNTA function here, since all you are
really doing is counting all the rows in Sheet2 with columns A-D matching the
criteria in Sheet3 columns A-D.

You will need to add column headers. I will use the column titles COL1,
COL2, COL3, COL4 (since I don't know what the data represents). Put these
headers over columns A-D in both sheets 2 and 3, and use some distinct
formatting so Excel can recognize them as titles.

Now you can use DCOUNTA:
=DCOUNTA(Sheet2!A,"COL4",Sheet3!A12)

If I understood you correctly this will give the count you are looking for
without even needing any VBA.