looping down 1 column then start agian at the top of the next column?
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 |
looping down 1 column then start agian at the top of the next column?
Hi
Try For Each c In Sheets("Staff Monday"). _ Range(cells(2, 7),cells(1000,7)).Offset(0,coffset) -- Regards Roger Govier "MJKelly" wrote in message ... 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 |
looping down 1 column then start agian at the top of the nextcolumn?
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 |
looping down 1 column then start agian at the top of the next column?
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 -- Regards Roger Govier "MJKelly" wrote in message ... 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 |
All times are GMT +1. The time now is 09:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com