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
