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
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