CODEWITHSUNDEEP
excelvbafor-loop
Jino La Tilla
Jino La Tilla

Reputation: 23

For-Loop exiting at the first round

Clarification: Goal of the code is to update a cell, based on the last update coming from the concatenation of three cells on the same row. The "X" is used to capture the updates of these three cells. I'm aware that in the "range" I'm selecting one cell at each time.

I wrote the following code (commented also the previous tries), but I realized I get only one line updated. No matters how many of them are correctly filled.

If Hrg.Row = Irg.Row And Irg.Row = Jrg.Row Then
    For i = 2 To Hrg.Row
        If (Cells(i, 11).Value = "X") Then
            S(i - 1) = Cells(i, 5).Value & " " & Cells(i, 6).Value & " [" & Cells(i, 8).Value & "] " & Format(Now(), "yyyy-MM-dd hh:mm:ss") & vbLf & Cells(i, 9).Value
            cellname = "I" & i
            ' ws.Range(cellname) = S
            ' Cells(i, 9) = S
            ' Cells(i, 9).Value = S
        Else
            S(3 - i) = ""
        End If
    Next i

    cellname = "I2:I" & Hrg.Row

    For Each selectedCell In ws.Range(cellname)
        i = 1
        If (Not (S(i) = "")) Then
            selectedCell.Value = S(i)
            Cells(i, 9).Value = S(i)
            Cells(i, 11).ClearContents
            i = i + 1
        End If
    Next selectedCell

Is there any reason why is exiting the function as soon as I've a "value" assignment?

Any reason why?

Excel 2016 VBA

Upvotes: 2

Views: 44

Answers (1)

A.S.H
A.S.H

Reputation: 29332

I think there's a logical error in the second part of the code. You're resetting i=1 no matter what:

For Each selectedCell In ws.Range(cellname)
    i = 1 ' <-- you're updating the same cell over and over again
    If (Not (S(i) = "")) Then
        '...
        i = i + 1
    End If
Next selectedCell

It is likely that you wanted to inititalize i=1 before the loop and increment it after each iteration:

i = 1 ' <-- inititalize once, outside the loop
For Each selectedCell In ws.Range(cellname)
    '....
    i = i + 1 ' <-- increment i after each iteration
Next selectedCell

Upvotes: 2

Related Questions