CODEWITHSUNDEEP
excelvba
Alex
Alex

Reputation: 2800

Excel odd rows give value

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!

Upvotes: 0

Views: 6383

Answers (4)

Dan Donoghue
Dan Donoghue

Reputation: 6216

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

Upvotes: 0

Skip Intro
Skip Intro

Reputation: 860

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

Upvotes: 2

souldzin
souldzin

Reputation: 1476

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

Upvotes: 1

TheLaurens
TheLaurens

Reputation: 414

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

Upvotes: 0

Related Questions