How can I loop trough multiple ranges of cells and return values with if,then,elseif?-CodePudding

I have hit a dead end with this problem(nearly one month). I need to compare three values from the sum columns with IF,then,elseif and then return one value from the columns according to this code.

    Private Sub CommandButton1_Click()
Dim score1 As Integer, score2 As Integer, score3 As Integer
Dim result As String
score1 = Range("H2").Value
score2 = Range("I2").Value
score3 = Range("J2").Value
If score1 < score2 And score1 > score3 Then
result = Range("E2").Value
ElseIf score3 > score1 And score3 < score2 Then
result = Range("G2").Value
Else
result = Range("F2")
End If
Range("s2") = result
End Sub

The problem that I face is that I have 589 cells that need to be verify with the statements written above and I don't know what loop code to write in the macro. I tried this code but it doesn't return values. All the values that I want to return must be on column S.

    Private if_Loop()
Dim A As Range
Dim B As Range
Dim C As Range
Dim E As Range
Dim G As Range
Dim F As Range
For Each A In Range("H2:H1177").Value
For Each B In Range("I2:I1177").Value
For Each C In Range("J2:J1177").Value
If A.Value > B.Value And A.Value > C.Value Then
result = E.Value
ElseIf C.Value > A.Value And C.Value < B.Value Then
result = G.Value
Else
result = F.Value
Next A
Next B
Next C
End Sub

CodePudding user response：

You can loop using a FOR NEXT:

For...Next statement

Dim score1 As Integer, score2 As Integer, score3 As Integer
Dim result As String
Dim i As Long
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row 'last non blank row in column A

For i = 2 To LR Step 1 'i=2 because data starts at row 2
    score1 = Range("H" & i).Value
    score2 = Range("I" & i).Value
    score3 = Range("J" & i).Value
    
    If score1 < score2 And score1 > score3 Then
        result = Range("E" & i).Value
    ElseIf score3 > score1 And score3 < score2 Then
        result = Range("G" & i).Value
    Else
        result = Range("F" & i)
    End If
    
    Range("s" & i) = result
Next i

Also, please, always indent your code because it makes easier to read code.