I have a rolling macro I have "inherited" and I need to modify it to ignore N/As.
It counts the latest 52 results and divides the positives by 52 but is also counting in the N/A's.
How can I get it to ignore the N/A's?
Sub Torn()
'
' Torn Macro
'
Dim Tests1 As Range 'setting a range for use in formula later
Range("I2").Select
Selection.End(xlDown).Select
Set Tests1 = Range(ActiveCell, ActiveCell.Offset(-52, 0))
Range("Q1").Value = Application.WorksheetFunction.CountIf(Tests1, "POS (+)") / 52 ' counting what is listed as "POS" and then dividing by 52 for avg
End Sub
It counts the latest 52 results and divides the positives by 52 but is also counting in the N/A's.
How can I get it to ignore the N/A's?
Sub Torn()
'
' Torn Macro
'
Dim Tests1 As Range 'setting a range for use in formula later
Range("I2").Select
Selection.End(xlDown).Select
Set Tests1 = Range(ActiveCell, ActiveCell.Offset(-52, 0))
Range("Q1").Value = Application.WorksheetFunction.CountIf(Tests1, "POS (+)") / 52 ' counting what is listed as "POS" and then dividing by 52 for avg
End Sub