CODEWITHSUNDEEP
vbaexcel
art123456
art123456

Reputation: 137

VBA Why is my macro reading column 5 instead of 3

I wrote a macro to insert a row between cells when their values are different. I have 9 columns in my data and the data starts at row 2. I want my macro to check all the values down column 3 (also known as column "C") and as it goes through, if the value changes (i.e. 2, 2, 2, 3, 3) it will insert a row between the changed value (i.e. 2, 2, 2, INSERT ROW, 3, 3). The problem is, my macro is reading column 5(E) not 3(C). What is wrong with it, I can't figure it out! The reason I know this too is because I placed a msgbox to spit the value of the cell and it matches everything in column 5 but not 3. Here is my code:

Sub Dividers()

Dim DividerRange As Range, lastrow As Long, k As Integer, counter As Integer

lastrow = Range("C2").End(xlDown).Row

Set DividerRange = Range(Cells(2, 3), Cells(lastrow, 3))
counter = 0

For k = 2 To DividerRange.Count
MsgBox DividerRange(k + counter, 3).Value
    If DividerRange(k + counter, 3).Value = DividerRange(k + counter - 1, 3).Value Then
    DividerRange(k + counter, 3).EntireRow.Insert
    counter = counter + 1
    Else
End If
Next k

End Sub

Upvotes: 0

Views: 102

Answers (2)

TessellatingHeckler
TessellatingHeckler

Reputation: 29048

You can simplify it quite a lot, there's no need for the Range or Range count, or counter:

Sub Dividers()

    Dim lastrow As Long, k As Integer

    lastrow = Range("C2").End(xlDown).Row

    For k = 2 To lastrow
        If Cells(k, 3).Value <> Cells(k - 1, 3).Value Then
            Cells(k, 3).EntireRow.Insert

            'Now skip a row so we don't compare against the new empty row
            k = k + 1
        End If
    Next k

End Sub

Upvotes: 1

JustinJDavies
JustinJDavies

Reputation: 2693

DividerRange(k + counter, 3).Value is a relative reference. DividerRange is a range starting at C2, so when you ask for the (i,j)th cell, i.e. (i,3) you get something from column E where jth columns would be: (C = 1, D = 2, E = 3)

Upvotes: 2

Related Questions