Excel odd rows give value

Question

I am trying to assign a value to all the odd cells in a particular column/range. So far I have the following code taken from another question, but it isnt working:

Sub changeClass()

    Dim r As Range
    Set r = Range("B16").End(xlDown)  'set the range the data resides in


    For i = 1 To r.Rows.Count  'merge step
        If i Mod 2 = 1 Then   'this checkes to see if i is odd
            r.Cells.Value = "value"
        End If
        Else 
            r.Cells.Value = "value2"
    Next i


End Sub

Basically I need it to add in a value for every cell in the B column from cell 16 down to the last cell i nthe column which has data in. On the even rows the value will be one thing, on the odd it will be another.

Many thanks!

Answer 1

You don't need a loop for this:

Sub OddRowAlert()
With Range("B16:B100")
    .Formula = "=IF((MOD(ROW(B16),2)),""Odd"",""Even"")"
    .Formula = .Value
End With
End Sub

Just replace odd and even in the formula with what you want

Answer 2

Sub changeClass()

    Dim r As Range
    Dim i As Integer

For Each r In Range("B16:B24") 'Change this range

i = r.Row
    If i Mod 2 = 1 Then   'this checks to see if i is odd
        r.Cells.Value = "ODD"

    Else
        r.Cells.Value = "EVEN"
    End If

Next r

End Sub

Answer 3

Try this out, I believe it is not working, because you are not acessing each individual cell inside your loop. In the following macro i use 'rng' to represent the entire range of cells, and 'r' to represent a single cell in each increment of the loop.

Sub changeClass()

    Dim rng As Range 
    Dim r As Range 
    Set rng = Range(Cells(16,2),Cells(16,2).End(xlDown))

    For i = 1 To rng.Rows.Count
        Set r = rng.Cells(i)
        If i Mod 2 = 1 Then ' You may want to test if it is odd based on the row number (depends on your problem...)
            r.Value = "Odd Value"
        Else
            r.Value = "Even Value"
        End If

    Next i

End Sub

Answer 4

you've messed up your if statement, don't close it off before else close it only once you are completely done with it! ;) here:

Sub changeClass()

Dim r As Range
Set r = Range("B16").End(xlDown)  'set the range the data resides in


For i = 1 To r.Rows.Count  'merge step
    If i Mod 2 = 1 Then   'this checkes to see if i is odd
        r.Cells.Value = "value"
    Else 
        r.Cells.Value = "value2"
    End if 
Next i

End Sub