Appropriate Excel VBA loop function?

Question

apologies as I'm quite new with this. I have this VBA macro:

 Sub ScoreTest()
Dim score As Integer, result As String
score = Range("D2").Value

If score >= Range("E2").Value Then
    result = "pass"
Else
    result = "fail"
End If
Range("F2").Value = result

End Sub

Of course, this only works for Row 2. I edited the macro to apply to rows 2 til 16, but that was just me copy pasting so it looked like so:

Sub ScoreTest()
Dim score As Integer, result As String
score = Range("D2").Value

If score >= Range("E2").Value Then
    result = "achieved"
Else
    result = "failed"
End If
Range("F2").Value = result

score = Range("D3").Value

If score >= Range("E3").Value Then
    result = "achieved"
Else
    result = "failed"
End If
Range("F3").Value = result

score = Range("D4").Value

If score >= Range("E4").Value Then
    result = "achieved"
Else
    result = "failed"
End If
Range("F4").Value = result

...

End Sub

Which loop function would I use in this scenario instead of copy+pasting the macro for each row?

if row number are not fixed you can use "DO WHILE" loop or else "FOR" loop will also work — Srijan
– Srijan, Commented Sep 6, 2018 at 10:32

QHarr · Accepted Answer · 2018-09-06 10:29:47Z

1

Something like

Option Explicit
Sub ScoreTest()
    Dim score As Long, result As String, i As Long

    With ThisWorkbook.Worksheets("Sheet1")
        For i = 2 To 16
            score = .Cells(i, "D")

            If score >= .Cells(i, "E") Then
                result = "achieved"
            Else
                result = "failed"
            End If
            .Cells(i, "F") = result
        Next
    End With
End Sub

answered Sep 6, 2018 at 10:29

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Appropriate Excel VBA loop function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related