• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Ignore N/As in Macro

Collywood

New Member
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
 
Collywood, there is a dedicated forum for VBA questions. You'd might consider to use it ;-)

I believe you can move it yourself within the first hour you created the thread. Otherwise a ninja needs to intervene.

Also as per forum rules, please upload a file with the macro (no need for data), or use the correct syntax to enter code. like "CODE" between [] to start the block and "/CODE" also between [] to end the block.

And welcome to this wonderful :awesome:world.
 
Back
Top