CODEWITHSUNDEEP
excelvba
Phillip Kreighbaum
Phillip Kreighbaum

Reputation: 21

VBA For Next Loop through a range

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

Upvotes: 0

Views: 537

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

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

Upvotes: 1

Related Questions