CODEWITHSUNDEEP
vbaexcel
Mikz
Mikz

Reputation: 591

Look for an value in a sheet count it and paste the result in another sheet

I am having two Sheets sheet1 and sheet2

In sheet1 , in column AX I have my present week printed using an formula. I am looking for the sheet1, column T and U and Count the number of 1'S in both the columns.

the counted number of 1's of both the columns should be pasted in sheet2 looking into the week of sheet1 in column AX. if the column AX has week number 24, then it should run along the sheet2, column A for 24 and paste the Count value of T in column B and Count value of U in column C, and calculate the percentage for both and Paste in C and D.

I tried through a code, I am often getting it as 0,I am struck where I am wrong. Ist neither checking the Count nor weeks. 

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("BW")
Set sh2 = Sheets("Results")



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

Resembels sheet1, where i have my data

Resembels sheet2, where I have the column A already filled, Need to fill column B, C , D and E. B and C are the Count values from sheet1 column T and U, D and E are the percentage values from column b and c of sheet2. Clarification img: for eg, this is how the sheet2 Looks. I already made an evalaluation last before week.now i am running a formula in sheet1, which gives current week in AX. SO, the sheet2 runs through column a, identifies the same week as sheet1 and add the Count value only in the current week, it does not Change the value of earlier week or next week.currently the code Returns 0, in all the weeks and Returns the Count value for current week. i want it to return only for current week.

Upvotes: 2

Views: 209

Answers (1)

curious
curious

Reputation: 1500

It appears from the question that sheet "Results" for a given week indicated in column A, shows in column B & C count of 1's in column T & U of the other sheet respectively.

One approach to solve this is that for each row in "Results" sheet, the counter looks into all rows of "BW" sheet for that week indicated in column "AX" to get the count from column T & U.

This'll give some idea:

Sub test()
    Dim i As Integer, j As Integer, cntT As Integer, cntU As Integer, ws As Worksheet
    Set ws = Sheets("Results")
    Sheets("BW").Select
    For i = 2 To WorksheetFunction.CountA(ws.Columns(1))
        If ws.Range("A" & i) = Val(Format(Now, "ww")) Then Exit For
    Next i
    ws.Range("B" & i & ":" & "E" & i).ClearContents
    cntT = 0
    cntU = 0
    For j = 5 To WorksheetFunction.CountA(Columns(50))
        If ws.Range("A" & i) = Range("AX" & j) And Range("AA" & j) <> "" Then
            If Range("T" & j) = 1 Then cntT = cntT + 1
            If Range("U" & j) = 1 Then cntU = cntU + 1
        End If
    Next j
    If cntT <> 0 Then ws.Range("B" & i) = cntT
    If cntU <> 0 Then ws.Range("C" & i) = cntU
    If cntT + cntU <> 0 Then
        ws.Range("D" & i) = cntT / (cntT + cntU)
        ws.Range("E" & i) = cntU / (cntT + cntU)
    End If
    ws.Range("D" & i & ":E" & i).NumberFormat = "0%"
End Sub

Update: as per subsequent discussion with @Mikz below criteria added to the above updated code:

  1. it only overwrites current week, so for Jul 2, 2017 it'll only overwrite the current week of 27 if found in `column A` of `Results` sheet
  2. if the is output is 0, it will leave the target cells as blank
  3. if respective cell of the AA column of "BW" sheet is blank, that row won't be counted for 1s
  4. sheet "BW" data starts from row 5

Upvotes: 2

Related Questions