Home Counting the columns in sheet1 and pasting their result in a table in sheet 2
Reply: 0

Counting the columns in sheet1 and pasting their result in a table in sheet 2

Mikz
1#
Mikz Published in 2018-01-12 12:36:53Z

I have two sheets. sheet1 is Evaluation and sheet2 is the result sheet.

From the Evaluation sheet1, I would like to count the number of "OK" in Column S and T and count the number of "Invalid" in the column R.

These counted values are to be entered In the sheet2 result , in their corresponding columns.

I was working with this code and It worked completely fine.

But now, when I run the code, number of OK in the column S and T and number of Invalid in the column R are not counted.

Here is the Code I am trying to work with

Sub result()
Dim i As Integer
Dim j As Integer
Dim cnt As Integer
Dim cntU, CntS, cntT As Integer
Dim sht As Worksheet
Dim totalrows, n As Long
Set sht = Sheets("Result")
Sheets("Evaluation").Select
totalrows = Range("A5").End(xlDown).Row
n = Worksheets("FC_SCR Evaluation").Range("A5:A" & totalrows).Cells.SpecialCells(xlCellTypeConstants).count
For i = 2 To WorksheetFunction.count(sht.Columns(1))
cntT = 0
cntU = 0
CntS = 0
' get the current week of column A of result sheet
If sht.Range("A" & i) = Val(Format(Now, "WW")) Then Exit For
Next i
' if column A of result sheet and Column x of fc sheet are same, then count the mentioned parameters
 For j = 5 To WorksheetFunction.CountA(Columns(23))
 If sht.Range("A" & i) = Range("X" & j) And Range("T" & j) = "OK" Then cntT = cntT + 1
 If sht.Range("A" & i) = Range("X" & j) And Range("S" & j) = "OK" Then cntU = cntU + 1
 If sht.Range("A" & i) = Range("X" & j) And Range("R" & j) = "Invalid" Then CntS = CntS + 1
 ' print the counted value in corresponding column of result sheet
If cntT <> 0 Then sht.Range("D" & i) = cntT
If cntU <> 0 Then sht.Range("E" & i) = cntU
If CntS <> 0 Then sht.Range("C" & i) = CntS
If n <> 0 Then sht.Range("B" & i) = n
Next j
If cntT + cntU <> 0 Then
sht.Range("F" & i) = CntS / n
sht.Range("g" & i) = cntT / n
sht.Range("h" & i) = cntU / n
End If
End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.332038 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO