Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba to count
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba to count
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 "crabtree" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba to count
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:D,"COL4",Sheet3!A1:D2) If I understood you correctly this will give the count you are looking for without even needing any VBA. "crabtree" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Count Duplicates | Excel Worksheet Functions | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |