Hi,
I have two Sheets , sheet1 and sheet2.
In sheet1 i Need to Count the 1's in column T and 1's in column U. ALso, I Need to check for column AX which Displays the week.
The Count total of Column T and U hast to be pasted in sheet2. The column A , has been already filled with Calendar week. It should look for the week and post the corresponding Count value in corresponding row.
I have tried a code, and pasted it below, i dont know where i am struck , because it Displays 0, in all my columns.
Thanking you in advance
Regards
Johnne
I have two Sheets , sheet1 and sheet2.
In sheet1 i Need to Count the 1's in column T and 1's in column U. ALso, I Need to check for column AX which Displays the week.
The Count total of Column T and U hast to be pasted in sheet2. The column A , has been already filled with Calendar week. It should look for the week and post the corresponding Count value in corresponding row.
I have tried a code, and pasted it below, i dont know where i am struck , because it Displays 0, in all my columns.
Code:
Sub test6()
Dim col As Range
Dim row As Range
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim T As Integer
Dim U As Integer
Dim wk As String
Set sh1 = Sheets("PSW_BW")
Set sh2 = Sheets("Results 2017")
For Each col In sh2.Columns 'This loops through all populated columns in row one
If sh2.Cells(1, col.Column).Value = "" Then
Exit For
End If
wk = sh2.Cells(1, col.Column).Value
For Each rw In sh1.Rows
If sh1.Cells(rw.row, 50).Value = "" Then
Exit For
End If
If sh1.Cells(rw.row, 50) = wk And sh1.Cells(rw.row, 20) = 1 Then
T = T + 1
End If
If sh1.Cells(rw.row, 50) = wk And sh1.Cells(rw.row, 21) = 0 Then
U = U + 1
End If
Next rw
sh2.Cells(2, col.Column) = T 'put counters into 2nd and 3rd row under each week, you can adjust this to put the number in a different cell.
sh2.Cells(3, col.Column) = U
T = 0 'reset counters to start looking at next week.
U = 0
Next col
End Sub
Thanking you in advance
Regards
Johnne