VBA For Next Loop through a range

Question

For the first row of my range data, everything works fine. When I execute "Next cell", it moves down to the second row of data, but the first value is from the second column of data.

Example: data is from columns K:N. The first data is in row 2. When it goes to "Next cell", the row changes to three, but cell.value = the value from column L row 3 instead of column K row 3. Is this normal? Shouldn't it start with the left-most column when I move to next C and the row changes? Thanks!

Function Modifer_Analysis()

Dim modRng As Range
Dim cell As Range
Dim i As Integer
Dim col As Integer
Dim rowTrack As Double
Dim modComp As String

Range("K2:N17914").Select
Set modRng = Selection

rowTrack = 2

For Each cell In modRng

    If rowTrack = cell.row Then

        If i = 0 And IsEmpty(cell.Value) = False Then
            modComp = cell.Value
        Else
        End If

        If i = 1 And IsEmpty(cell.Value) = False Then
            modComp = modComp & "," & cell.Value
        Else
        End If

        If i = 2 And IsEmpty(cell.Value) = False Then
            modComp = modComp & "," & cell.Value
        Else
        End If

        If i = 3 And IsEmpty(cell.Value) = False Then
            modComp = modComp & "," & cell.Value
        Else
        End If

        i = i + 1

    Else

        i = 0
        rowTrack = cell.row
        modComp = ""

    End If

Next cell


End Function

Side note: [Don't use `Integer`, use `Long`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). — BigBen, Feb 13 '20 at 18:53
What do you want to accomplish using this code? How would you use the resulted string `modComp`? Do you intend to split it in order to obtain an array of the non empty cells of the range? — FaneDuru, Feb 13 '20 at 19:04
I have four columns of MOD data per row. I want to concatenate the cells where there were values. In some rows, they will all be empty and in others all four will be populated. I use a "," to separate them and want to avoid concatenating blank values and ending up with a ",". — Phillip Kreighbaum, Feb 18 '20 at 14:27

Tim Williams · Accepted Answer · 2020-02-13T20:12:50.993

1

It's not clear exactly what you're trying to achieve, but maybe something more like this would be a good start:

Function Modifer_Analysis()

    Dim cell As Range, rw As Range
    Dim modComp As String, sep As String

    'loop over rows
    For Each rw In Range("K2:N17914").Rows 'needs a worksheet qualifier here
        modComp = ""
        sep = ""
        For Each cell In rw.Cells
            'loop over cells in row
            If Len(cell.Value) > 0 Then
                modComp = modComp & sep & cell.Value
                sep = ","
            End If
        Next cell
    Next rw

End Function

edited Feb 13 '20 at 20:12

answered Feb 13 '20 at 19:01

Tim Williams

154,628
8
97
125

Thanks for the help. My main problem was that I was looping to the next cell before handling the data. – Phillip Kreighbaum Feb 18 '20 at 14:25

VBA For Next Loop through a range

1 Answers1