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