VBA to Count the value in column and fill the table in another sheet

Question

I am having two Sheets , sheet1 and sheet2.

I have a table with the weekno, delay , Ok, percenatage of Delay and Percentage of OK.

In sheet 1, i am looking for the column T, and Count the number of '1' in the column. Similarly i look for column U and Count the number of '0' in the column.

the Count value has to be filled in sheet2 of the table looking into the week. I have my week in the column AX of sheet1.

I used formula like Countif for calculating the number of 1 in both the column. could someone guide me how we can do it in VBA, to Count the value in column and pasting the result in a table in another sheet.I am struck how to start with coding. This is my sheet1, i have to Count the number of 1 in column t and u

Jarom Jarom · Accepted Answer · 2017-06-28T14:51:17

To solve this problem, we have to use a couple of loops. First, we have to do an outer loop to go through the week numbers in sheet2, then imbedded in that loop we have to look at each row in sheet1 to see if it matches the week from sheet2 and to see if column T = 1 and if column U = 0. The code creates a counter that increase T by one every time a row in T is equal to 1 and does the same for U every time a row in U = 0.

Sub test()
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("Sheet1")
Set sh2 = Sheets("Sheet2")



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

For some reason I wasn't able to view the images that you just uploaded. You may have to adjust my code to adapt to the specifics of your excel file. It will be good VBA practice for you. My code assumes the sheet2 has the week numbers going across the columns without any blank cells in between them. The vba drops the code in the 2nd and 3rd row under the corresponding week number. You can change this by changing the code that is indicated in the comments.

VBA to Count the value in column and fill the table in another sheet

1 Answers