If statement within For/Next loop

[smapple]

Hello,

I have a monthly process around a dataset that I'm trying to automate. The data comprises records so that each row is a unique record. I have a calculated field called % Pass Rate and I want to run a simple if statement for every record that says, 'if the score is 85% or more assign 1 otherwise assign 0 in the next column.' The columns are the same each month, but the number of rows will change.

I'm able to calculate the % Pass Rate in Column AE, but when I run the second part of the code, nothing seems to happen (I don't even get an error), whereas I would've expected that Column AF would be a series of 1s and 0s. Can anyone see what I'm missing?

Cheers,


So far I have:

' Calculate % Pass Rate
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, 31) = (YesAns + NAAns) / 25
Next

' Determines if file score is above 85% threshold
Dim PassFail As Long
Dim result As Integer

PassFail = Sheets("Data").Range("AE" & Rows.Count).End(xlUp).Row For ThisRow = 2 To LastRow
If PassFail >= 0.85 Then
result = 1
Else
result = 0
Sheets("Data").Cells(ThisRow, 32) = result
End If

Next

 

[Rick Rothstein]

Hello,

I have a monthly process around a dataset that I'm trying to automate. The data comprises records so that each row is a unique record. I have a calculated field called % Pass Rate and I want to run a simple if statement for every record that says, 'if the score is 85% or more assign 1 otherwise assign 0 in the next column.' The columns are the same each month, but the number of rows will change.

I'm able to calculate the % Pass Rate in Column AE, but when I run the second part of the code, nothing seems to happen (I don't even get an error), whereas I would've expected that Column AF would be a series of 1s and 0s. Can anyone see what I'm missing?

Cheers,


So far I have:

' Calculate % Pass Rate
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, 31) = (YesAns + NAAns) / 25
Next

' Determines if file score is above 85% threshold
Dim PassFail As Long
Dim result As Integer

PassFail = Sheets("Data").Range("AE" & Rows.Count).End(xlUp).Row For ThisRow = 2 To LastRow
If PassFail >= 0.85 Then
result = 1
Else
result = 0
Sheets("Data").Cells(ThisRow, 32) = result
End If

Next

You should be able to replace what I highlighted in red above with this single line of code...

Range("AF2:AF" & LastRow) = Evaluate("0+(AE2:AE" & LastRow & ">=0.85)")

 

[smapple]

Genius, thank you!