How do I stop my for loop from erroring out

Question

Sub NewThing()

Dim Cell As Range

For Each Cell In Worksheets("Question 2").Range("B3:B11").Cells

    If Cell.Value >= 90000 Then
    
        Range("C3:C11").Value = Cell.Value * 1.5
    Else
        Range("C3:C11").Value = Cell.Value * 1.1
    
    End If

Next Cell

End Sub

When I step into in the vba editor, I can see the values cycling through in C3:C11 what am I doing wrong? It's taking the last value from B11 and using it for the entire column. Help please.

Answer 1

The issue here is that you're setting the entire range C3:C11 with a single value on each iteration, rather than setting the value for the corresponding cell in the loop.

Instead of updating the entire range, you should update the corresponding cell in column C. You can fix it by using the Offset method or by explicitly referencing the cell in column C.

Sub NewThing()

    Dim Cell As Range

    For Each Cell In Worksheets("Question 2").Range("B3:B11").Cells

        If Cell.Value >= 90000 Then
            ' Update the corresponding cell in column C using Offset
            Cell.Offset(0, 1).Value = Cell.Value * 1.5
        Else
            Cell.Offset(0, 1).Value = Cell.Value * 1.1
        End If

    Next Cell

End Sub