Excel Vba - Comparing 2 ranges of data and displaying result in another worksheet

[wuming]

Hi,
I need to count 2 sets of data whereby one of the worksheets contai
data which acts as the criteria and displaying the results of eac
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 use
defined range in worksheet 2. After comparison, displaying the result
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??

 

[Frank Kabel]

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

 

[wuming]

LOL thats what everyone is saying but i got no choice as my boss want
it in VBA.

But thanks for the codes: Btw, with regards to:
set rCrit=worksheets("sheet1").range("A1:A10")
set rData =worksheets("sheet2").range("A1:A1000")

I want to create a input box for user to select the specific range o
data as the criteria as well as for the data table, meaning input bo
would ask user for 2 ranges of cells to select the criteria and th
range of data to compare the criteria.
thanks

 

[Frank Kabel]

Hi
use something like
set rCrit = application.inputbox ("select your range",type:=8)

 

[wuming]

ahh maybe if u can take a look at the attachment u can better help m
with my problem.
Thank

Attachment filename: criteria&results.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61706