Excel Vba - Comparing 2 ranges of data and displaying result in another worksheet
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 |
Excel Vba - Comparing 2 ranges of data and displaying result in another worksheet
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/ |
Excel Vba - Comparing 2 ranges of data and displaying result in another worksheet
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 |
Excel Vba - Comparing 2 ranges of data and displaying result in another worksheet
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/ |
Excel Vba - Comparing 2 ranges of data and displaying result in another worksheet
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 |
All times are GMT +1. The time now is 01:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com