First, Let me tell you the script that i want to achieve. I need a script that will count the values within a date range the range of date is 3 months, I have a source file which contains 3 months of data now i need to count the data by months if the data is within the months(3) tagged it as selected..(at least one value per month(up to 3))
Sample:
`Header A|Header B |Header C|
white | 1/1/2016 | |
white | 2/2/2016 | |
white | 3/3/2016 | |
black | 1/1/2016 | |
black | 2/2/2016 | |
grey | 3/3/2016 | |
grey | 3/3/2016 | |
grey | 4/4/2016 | |
brown | 4/4/2016 | |
brown | 4/4/2016 | |
brown | 5/5/2016 | |
brown | 6/6/2016 | |
Sample Output:
`Header A|Header B |Header C|
white | 1/1/2016 | |
white | 2/2/2016 | |
white | 3/3/2016 |selected|
black | 1/1/2016 | |
black | 2/2/2016 | |
grey | 3/3/2016 | |
grey | 3/3/2016 | |
grey | 4/4/2016 | |
brown | 4/4/2016 | |
brown | 4/4/2016 | |
brown | 5/5/2016 | |
brown | 6/6/2016 |selected|
In the sample above. The data white has been tagged as selected because it meets the required criteria, let's say the criteria needed is "at least one color per month" we have 3 month of data so it needs to count 1 color per month. The other color in the ex. didnt meet the criteria like the color black it only have data for 2 months what we need is for 3 months. The color grey have 3 data if you count it will only return 2 months because there is 2 data in a month. The color brown meets the criteria because there is a data for 3 months duplicate value in a month is fine as long it has a data every months(3) for..
Now here's my code:
'iterate all rows for 3 months to check their dates then create an arbitrary column(lastcolumn +1) to store the month value
For rownum = 2 To lastrow_masterfile
varDatesValue = masterfileWKsht.Range("B" & rownum).Value
masterfileWKsht.Range("D" & rownum).Value = Month(varDatesValue)
Next
'column range for color
Set myRangeColor = ThisWorkbook.Sheets("masterfile").Range("A2:A" & lastrow_masterfile)
'column range for (arbitrary column)monthvalue
Set myRangeMonthValue = ThisWorkbook.Sheets("masterfile").Range("D2:D" & lastrow_masterfile)
'loop for weekly data
For rownum_weekly = startingrow_of_weekly To lastRow
varColors = masterfileWKsht.Range("B" & rownum_weekly).Value
varCOMMonth = Month(masterfileWKsht.Range("A" & rownum_weekly).Value)
'CountIfs 1:
varMonth1 = WorksheetFunction.CountIfs(myRangeColor, varColor, myRangeMonthValue, varDatesValue)
'CountIfs 2:
'month value of varDates per row -1 for previous month(range of this is the new column which store the monthvalue)
varMonth2 = WorksheetFunction.CountIfs(myRangeColor, varColor, myRangeMonthValue, varDatesValue - 1)
'CountIfs 3:
'month value of varDates per row -2 for 2months ago(range of this is the new column which store the monthvalue)
varMOnth3 = WorksheetFunction.CountIfs(myRangeColor, varColor, myRangeMonthValue, varDatesValue - 2)
'if value of the 3 countifs is atleast 1 then tagged it as selected
If varMonth1 >= 1 And varMonth2 >= 1 And varMOnth3 >= 1 Then
'insert code here(i still dont khow how to write code here)
End If
Next
please help me about this....