Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help
Marcus,
I offer the following as a start (if I have understood your requirement properly): Sub CountS() Dim Weekrng As Range, ToRng As Range Dim iLastRow As Long, ns As Long, r As Long, nwk As Integer Dim srow As Integer, m As Integer iLastRow = Cells(Rows.Count, "A").End(xlUp).Row ' Output starting in A2 (week 1) Set ToRng = Worksheets("Sheet2").Range("a2") srow = ToRng.Row ' Record starting roiw of input data - assumes header in row 1 Worksheets("Sheet1").Activate For nwk = 1 To 5 ' Loop through 5 weeks For r = 2 To iLastRow ' Loop through all employees Set Weekrng = Cells(r, (nwk - 1) * 7 + 1).Resize(1, 5) ' Select 5 days ToRng = Application.CountIf(Weekrng, "S") ' Count Ss and store in Sheet2 Set ToRng = ToRng.Offset(1, 0) ' Move output range to next row Next r m = iLastRow - srow + 1 Set ToRng = ToRng.Offset(-m, 1) ' Reset output range to next column Next nwk End Sub You will see it loops for only 5 weeks. I am not sure how your data is organised but you cannot get 365/366 days in columns as there are only 256 in Excel. However, I have assumed cols 1 to 7 are week 1, 8 to 14 are week 2 etc. HTH "marcus" wrote: Tom I want to count any row with "S" in it. but I am counting the "S"s so I know how many days people have been in off in a week period (on another spreadsheet). So in the above case 3 days in five. That is why I need it only to count the first five columns in 7. I wish to ignore the weekends and then continue to count the following week and so on for the next 52 weeks. At the end I hope to have 52 weeks all with totals on one spreadsheet and daily analysis on the other. Do you have any suggestions Marcus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|