CODEWITHSUNDEEP
excelvba
Nick Fleetwood
Nick Fleetwood

Reputation: 531

Double For Loop Not Increasing

I just can't seem to get this for loop running correctly. I know that I missing something basic, but I just can't figure it.

I have 2 tables.

Table 1 : (Table starts at row 7, and columns i to q are hidden)

enter image description here

Table 2 :

enter image description here

My goal is to pull new rows from Table1 to Table2. My code rolls through Table 1, identifies the rows with an 'R' value, and fills them. Then I want to pull data from those same rows to Table2

The code that identifies and fills the 'R' value:

Dim iRow As Long
With Sheet12
    iRow = Application.Count(.ListObjects("Table1").ListColumns("KEY").DataBodyRange)
End With

'find last row with a date
Dim jRow As Long
With Sheet12
    jRow = Application.Count(.ListObjects("Table1").ListColumns("Date").DataBodyRange)
End With
'take the value from iRow and col 1, add 1, place in iRow+1,1
Dim q As Long
For q = iRow + 7 To jRow + 6
Sheet12.Cells(q, 18) = 1 + Sheet12.Cells(q - 1, 18)
Next q

Then this bit I'm having trouble with. My thought was try to run a double loop, where I loop through to fill each column and then each row.

Dim a As Long
Dim b As Long
Dim c As Long

c = jRow - iRow

For b = 11 To c + 11
    For a = iRow + 7 To jRow + 6
        ws15.Cells(b, 1).Value = "Plaid-" & Sheet12.Cells(a, 8).Value & "-" & Sheet12.Cells(a, 7).Value
        ws15.Cells(b, 2).Value = Sheet12.Cells(a, 18).Value
        ws15.Cells(b, 3).Value = Sheet12.Cells(a, 3).Value
        ws15.Cells(b, 4).Value = Sheet12.Cells(a, 4).Value
        ws15.Cells(b, 5).Value = Sheet12.Cells(a, 5).Value
        ws15.Cells(b, 6).Value = 1001
        ws15.Cells(b, 7).Value = "FILL IN"
        Next a
    Next b

Now the above code only copies the last row from Table1 into Table2 four times.

I know I'm close, and I'm sure I'm just tired, but I can't get it right. I appreciate everyone's time.

Upvotes: 0

Views: 52

Answers (1)

Mike67
Mike67

Reputation: 11342

The double loop is causing the problem. The inside loop fills in the same row 4 times. This explains why every row has the same data.

You want to iterate the rows together so you just need 1 loop. The b variable is not needed.

Try this code:

Dim a As Long
Dim c As Long

c = jRow - iRow + 7  'start row on new sheet

For a = iRow + 7 To jRow + 6  'source data rows
      ws15.Cells(c, 1).Value = "Plaid-" & Sheet12.Cells(a, 8).Value & "-" & Sheet12.Cells(a, 7).Value
      ws15.Cells(c, 2).Value = Sheet12.Cells(a, 18).Value
      ws15.Cells(c, 3).Value = Sheet12.Cells(a, 3).Value
      ws15.Cells(c, 4).Value = Sheet12.Cells(a, 4).Value
      ws15.Cells(c, 5).Value = Sheet12.Cells(a, 5).Value
      ws15.Cells(c, 6).Value = 1001
      ws15.Cells(c, 7).Value = "FILL IN"
      c = c + 1  'next row on new sheet
Next a

Upvotes: 2

Related Questions