Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum values in a column depending on start time in another column | New Users to Excel | |||
copy a range with known start column to variable end column | Excel Programming | |||
Macro to border row from start column to end column | Excel Programming | |||
Determine start column/ end column of Merged Cell | Excel Programming | |||
looping from one column to the next | Excel Programming |