Looping and RowIndex Over Variable Range

[bkatsma]

Hi, I am hoping some excel experts could help me with a problem. My data in general; columns 1-17 are general variables, Columns 18-29 are VBA driven IF Then statements based on values in 1-17. Each row can, and most likely will, have more than one 'true' If statement in columns 18-29.

What I am trying to do with the code: For each value in columns 18-29 which are not blank, insert a new row below, and enter the values from column 1-17 above into the new row. The code below does this to a point. The first IF statement, part 1, when ran alone, works exactly as I need. I assumed I could just copy the code and adjust the code for each column value in columns 18-29. Therefore, creating a new row for each value in column 18-29. But it isn't working.

I have done some trial and error to help narrow it down a little. The problem seems to occur when two values, columns 18-29, are present in the same row (see table 1). If the values are staggered (table 2) then it works great. My rows will have multiple values

Sub AddRow()

'Still in testing
Dim RowIndex As Long
Dim Delta As Long

RowIndex = 2

Do While Sheets("WeeklyReport").Cells(RowIndex, 1).Value <> ""
Delta = 0
        If Sheets("WeeklyReport").Cells(RowIndex, 19).Value = "Lead" Then
        ' Inserts new row part 1
        Sheets("WeeklyReport").Cells(RowIndex + Delta + 1, 1).EntireRow.Insert
        ' Takes cells value from row above and enters value in new row
        Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 1), Cells(RowIndex + 1, 17)).Value = Sheets("WeeklyReport").Range(Cells(RowIndex, 1), Cells(RowIndex, 17)).Value
        ' Puts rating value in last column
        Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 18), Cells(RowIndex + 1, 18)).Value = "Lead"
        Delta = Delta + 1
    End If
        If Sheets("WeeklyReport").Cells(RowIndex, 20).Value = "HP" Then
        ' Inserts new row part 2
        Sheets("WeeklyReport").Cells(RowIndex + Delta + 1, 1).EntireRow.Insert
        ' Takes cells value from row above and enters value in new row
        Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 1), Cells(RowIndex + 1, 17)).Value = Sheets("WeeklyReport").Range(Cells(RowIndex, 1), Cells(RowIndex, 17)).Value
        ' Puts rating value in last column
        Sheets("WeeklyReport").Range(Cells(RowIndex + 1, 18), Cells(RowIndex + 1, 18)).Value = "HP"
        Delta = Delta + 1
     End If
    RowIndex = RowIndex + Delta + 1
Loop

Table 1: D & E are my column 18-29 values
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]Large[/TD]
[TD]High[/TD]
[TD]Lead[/TD]
[TD]HP[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]Medium[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]No[/TD]
[TD]Small[/TD]
[TD]High[/TD]
[TD]Lead[/TD]
[TD]HP[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]No[/TD]
[TD]Large[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]Medium [/TD]
[TD]High[/TD]
[TD]Lead[/TD]
[TD]HP[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]No[/TD]
[TD]Small[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2: D & E are my column 18-29 values, but staggered
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]Large[/TD]
[TD]High[/TD]
[TD]Lead[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Yes[/TD]
[TD]Medium[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD]HP[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]No[/TD]
[TD]Small[/TD]
[TD]High[/TD]
[TD]Lead[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]No[/TD]
[TD]Large[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD]HP[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]Medium [/TD]
[TD]High[/TD]
[TD]Lead[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]No[/TD]
[TD]Small[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD]HP[/TD]
[/TR]
</tbody>[/TABLE]