Hi
not sure why you want VBA for this as a simple COUNTIF formula would do
or using a pivot table to create such a report. But if you want VBA you
may use something like the following

sub foo()
dim vres
dim rCrit as range
dim rData as range
dim lTargetRow as long
dim cell as range

lTargetRow =1
set rCrit=worksheets("sheet1").range("A1:A10")
set rData =worksheets("sheet2").range("A1:A1000")
for each cell in rCrit
if cell.value<"" then
vres=application.countif(rData,cell.value)
with worksheets("sheet3").cell(ltargetrow,1)
.value="No. of " & cell.value
.offset(0,1).value=vres
end with
ltargetrow=ltargetrow+1
end if
next
end sub


--
Regards
Frank Kabel
Frankfurt, Germany


Hi,
I need to count 2 sets of data whereby one of the worksheets contain
data which acts as the criteria and displaying the results of each
criteria individually using VBA coding.

Eg. Worksheet 1 (whereby, Tan, Lee, Who are the criterias)
A1: Criteria
A2: Tan
A3: Lee
A4: Who

Eg. Worksheet 2 (datasheet)
I need vba codes to compare the criterias in worksheet 1 against user
defined range in worksheet 2. After comparison, displaying the
results
in a new worksheet, for eg:

Worksheet 3:
No. of Tan: 5
No. of Lee: 10
No. of Who: 0
No. of rows matched: 15 (Sum of rows that match the criterias)
No. of rows not matched: 5 (Sum of rows that doesn't match)

Anyone can help???


---
Message posted from http://www.ExcelForum.com/