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

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

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/

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

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

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


--
Regards
Frank Kabel
Frankfurt, Germany


LOL thats what everyone is saying but i got no choice as my boss
wants
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 of
data as the criteria as well as for the data table, meaning input box
would ask user for 2 ranges of cells to select the criteria and the
range of data to compare the criteria.
thanks.


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

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
--
Message posted from http://www.ExcelForum.com