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

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

Collectives™ on Stack Overflow

Appropriate Excel VBA loop function?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related