CODEWITHSUNDEEP
vbaexcel
Amatya
Amatya

Reputation: 1243

Looping over a range, row by row

I have some data that looks like this:

enter image description here

For all rows labeled "Coupon" I want to add a number to the entries in that row if they are non-blank. If they are blank, I want to leave them alone. Also if the data in a cell happen to be a date, I want to not touch it.

Row by row I want to run through the entire range.

My current code is giving me a "for each may only iterate over a collection object or an array vba" error. Please help!

Sub CommandButton1_Click()


Dim rng As Range
Dim rw As Range
Dim cel As Range


Set rng = Range("E15:P464")

For Each rw In rng.Row
    If rw.Item(1, 1) = "coupon" Then
      For Each cel In rw.Cells
            If IsEmpty(cel.Value) = False Then
                   If Not IsDate(cel) Then
                       cel.Value = cel.Value + 0.0001
                   End If
            End If
      Next cel
    End If
Next rw



End Sub

Upvotes: 0

Views: 245

Answers (2)

user3598756
user3598756

Reputation: 29421

chris neilsen gave solution to fix the error

you may want to follow an alternative AutoFilter() approach, like follows:

Option Explicit

Sub main()
    Dim cel As Range

    With Worksheets("Coupons") '<--| reference "Coupons" worksheet (change "Coupons" to your actual worksheet name)
        With .Range("A1").CurrentRegion '<--| reference its range made of cells contiguous to "A1"
            .AutoFilter Field:=1, Criteria1:="Coupon" '<--| filter it on column "A" with "Coupon" criteria
            If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then '<--| if any "Coupon" in column A" has been filtered
                For Each cel In .Offset(1, 1).Resize(.rows.Count - 1, .Columns.Count - 1).SpecialCells(xlCellTypeVisible).SpecialCells(XlCellType.xlCellTypeConstants, xlNumbers) '<-- loop through filtered range cells containing numbers (and skipping column "A" and row 1)
                    If Not IsDate(cel) Then cel.Value = cel.Value + 0.0001 ' update non-date numbers
                Next cel
            End If
        End With
        .AutoFilterMode = False '<--| show all rows back
    End With
End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

Try the code below, it's a little different than the one you posted:

Sub CommandButton1_Click()

Dim rng         As Range
Dim rw          As Range
Dim Col         As Long
Dim CellStr     As String

Set rng = Range("E15:P464")

' loop through rows in Range
For Each rw In rng.Rows
    ' get the value of the first column and convert to String
    CellStr = rw.Columns(1).Value

    ' use StrComp to verify match between strings
    If StrComp(CellStr, "coupun") = 0 Then

        ' loop through all columns in current row (where there was a match with "coupun"
        For Col = rng.Columns(2).Column To rw.Columns.Count

            ' check if current cell is empty
            If Not IsEmpty(Cells(rw.Row, Col)) Then
                If Not IsDate(Cells(rw.Row, Col)) Then
                    Cells(rw.Row, Col).Value = Cells(rw.Row, Col).Value + 0.0001
                End If
            End If
        Next Col

    End If
Next rw

End Sub

Upvotes: 1

Related Questions