VBA to count a column value in a sheet and paste to another sheet.

Johnne

New Member
Joined
Jun 29, 2017
Messages
1
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.

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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top