VBA Sum CounIf formulas and copy formula down

[smapple]

Hello,

I'm sure this is an easy one, but I've scoured the web and can't quite find what I'm after. I have a dataset that I pull monthly that contains a new record in each row. I would like to write a macro that adds up the number of "Yes" and "N/A" answers for each record and divides by 25 (the number of questions). I currently do this without VBA by computing:
=(countif(F2:AD2, "Yes")+countif(F2:AD2, "N/A"))/25 and copying down, however, I'm in the process of automating this work a bit more. Each time I pull this data, there is a different number of rows, but the columns will be the same.

I tried the below, but I'm sure you can see that this computes the 'Pass Rate' for the first record and then copies that same value all the way down. Any help you can offer me would be very appreciated.

' Calculate % Pass Rate
Dim YesAns As Long
Dim NAAns As Long
Dim LastRow As Long

YesAns = Application.WorksheetFunction.CountIf(Sheets("Data").Range("$F2:$AD2"), "Yes")
NAAns = Application.WorksheetFunction.CountIf(Sheets("Data").Range("$F2:$AD2"), "N/A")
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Sheets("Data").Range("AJ2:AJ" & LastRow) = (YesAns + NAAns) / 25

 

[JumpingCrab]

Try this:

Public Sub Calculate()
    Dim YesAns As Long
    Dim NAAns As Long
    Dim LastRow As Long
    Dim ThisRow As Long
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For ThisRow = 2 To LastRow
        YesAns = Application.WorksheetFunction.CountIf(Sheets("Data").Range(Cells(ThisRow, 6), Cells(ThisRow, 30)), "Yes")
        NAAns = Application.WorksheetFunction.CountIf(Sheets("Data").Range(Cells(ThisRow, 6), Cells(ThisRow, 30)), "N/A")
        
        Sheets("Data").Cells(ThisRow, 36) = (YesAns + NAAns) / 25
    Next
End Sub

You can make the column references more dynamic of course, but if the data is always in those columns it doesn't matter.
Hope this helps.
JL

 

[smapple]

Thanks a ton, JumpingCrab, that worked perfectly!

 

[JumpingCrab]

Thanks, you're welcome.
Glad to be of help