looping down 1 column then start agian at the top of the next column?

[MJKelly]

Hi,

I am looping through a range in one ws and counting instances of a
variable in another ws.
I cant get thes second line to work? I want to start in column G and
move one column ata time to column ET. each r starts a new column.
I'm putting the count value in r and then moving to calculate r for
the next column.

For Each r In ThisWorkbook.Sheets("TallyDump").Range("B3:B146")
For Each c In Sheets("Staff Monday").Range(2, 7).Offset(0,
COffset), range(1000,7).Offset(0,coffset)
If c.Value = "PM" Then Count = Count + 1
Next c
Sheets("tallydump").Range("b3").Offset(Offset1, 0).Value = Count
Offset1 = Offset1 + 1
Count = 0
COffset = COffset + 1

Next r

I thought I would need to use offset so each r adds a column to the
offset?

Hope you can help?
Matt

 

[Roger Govier]

Hi

Try
For Each c In Sheets("Staff Monday"). _
Range(cells(2, 7),cells(1000,7)).Offset(0,coffset)

 

[MJKelly]

Hi Roger,

Worked a treat! Thanks!
I want to make better use of this code so I can use it for multiple
worksheets. I usually do this by moving the code to a new routine and
passing variables from another routine. Is there a way I can do this
within one routine?

Kind regards and thanks again,
Matt

 

[Roger Govier]

Hi Matt

Is this what you mean?
I am assuming that you have a list of differing sheets, which you would set
in myArray
This list would be processed in turn, and their results placed in successive
columns on your tally sheet.
Rather than looping through each item in the column, I have used Countif to
return the number of "BM"

Sub CountData()
Dim source As Worksheet, tally As Worksheet
Dim myArray, myRng As Range
Dim i As Long, j As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
myArray = Array("Staff Monday", _
"Staff Tuesday", "Staff Wednesday")
Set tally = ThisWorkbook.Sheets("TallyDump")
j = 2
For Each source In myArray
For i = 3 To 146
myArray = source.Range(Cells(2, 7), _
Cells(1000, 7)).Offset(0, i)
tally.Cells(i, j) = WorksheetFunction. _
CountIf(myRng, "PM")
Next i
j = j + 1
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub