Copy/Paste Loop is only transferring rows 1,2, and 4, but not row 3?

[maraecole3]

Hi,

I am trying to troubleshoot this one kink so I can troubleshoot my major hiccup. I have a loop construct that works well (compliments to Paul) except out of the 4 rows it should copy and transfer, it is skipping number 3.

Here is the gist: 😊
Row 1 --> perfect, pastes in all the cells for the 0th loop
Row 2 --> perfect, pastes in all the cells for the 1st loop
Row 3 --> missing and not pasted?
Row 4 ---> lands on to the position of 2nd looped offset and leaves the 3rd looped offset empty.

I changed the loop line: For iBlockRow = 0 To 3 to For iBlockRow = 0 To 2 to see if I can catch Row 3's data and no whammy, still only copies Row 4.

Here is a synopsis of the code:

iOffset = 0
For iBlockRow = 0 To 3 ' <- 4 loops, 1 for each line
iStartRowOfLastBlock = iStartRowOfLastBlock + iBlockRow
With shtResult
.Cells(2, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 1).Value
.Cells(4, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 2).Value 'Way more code under here assigning more cells to multiple locations.
End With

iOffset = iOffset + 15 '<--All of the pasting cells are 15 columns apart on the "Result" sheet and are aligned correctly.
Next iBlockRow


Can someone help me find Row 3?
Thanks

[Paul_Hossler]

After



iStartRowOfLastBlock = iStartRowOfLastBlock + iBlockRow



put
Msgbox iStartRowOfLastBlock


and see what happens

[maraecole3]

Thanks and Hi Paul!

I tried that and the message box said "3, 4, 6, and 9", which I don't understand it. This is especially since when I opened the userform, it says 1A, 1B, 1C, and 1D. Then once I submitted the userform, it populated the Excel Rows 1-4 on the "Data" sheet as 2A, 2B, 2C, and 2D. None of which, match the msgbox response of above. But is it doing a difference? Like the difference between 3 and 4 is 1; 4 and 6 is 2, and 6 and 9 are 3? I will see if I change the loop to 0 to 4 again, if I will get a 4th difference?

[maraecole3]

I changed the loop from 0 to 3, to 0 to 4, erased the Excel entries and reran the userform. The msgbox gave, as expected, 3, 4, 6, 9, and 13. But only rows, 1, 2, and 4 populated the "Sample Result" sheet.

Marsha

[maraecole3]

I manipulated a few things and still got the same outcome. So my question is this:

Since I am adding 4 rows at a time, and the loop is: iStartRowOfLastBlock = shtData.Cells(Rows.count, 1).End(xlUp).Offset(-3, 0).row

Is it possible that after Row #1, it is counting rows in between loops? Could it be competing with the the Offset (-3,0)?

I tried to change it -4 just to see if I would squeeze out that last rows of data but it didn't work. If I take it off, it only transfers Row #1.

Changing it to-1 also copied two rows.

[maraecole3]

OK, so I'm closer. It is something to do with the iBlockRow term in
For iBlockRow = 0 To 3 ' <- 4 loops, 1 for each line iStartRowOfLastBlock = iStartRowOfLastBlock + iBlockRow

When I removed the iBlockRow, this is what happened:
1) Each multipage populated 1 line on "Data" sheet (Great!)
2) Each section on the "SampleResult" sheet was populated (FINALLY!!!) and the offset worked (YES!) BUT,
3) Each section only populated with the 1st Row of data (not what I wanted).

I will increment this by changing the iBlockRow from negatives to positives and see if it does something. I just wanted to post this approach so that if you have a remedy before I figure it out, this will help you help me.

[maraecole3]

EUREEKA!!!

Thank God I understand trends! Here is the thing, apparently when copying multiple rows over in a for-loop scenario, you may have an exponential change. So I had to break my loop into parts and divide by an integer to keep it linear.
This is because of this:



Loop

Row
Step
Step 1/integer


0

1
1
1.000


1

2
2
1.800


2

3
4
3.111


3

4
6
4.071




The step between row 1 and 2, are 1.25 each, Offset for me is 15 each. The step between row 2 and 3 is 1.8, the offset is 30. The step between 3 and 4 is 2.8 the offset is 45. I pray that if someone is as ambitious as me as a rookie, this might come to some help.


In terms of VBA, I have no idea why? But I guess that is my next learning objective.

Here is the Code!


Dim shtData As Worksheet, shtResult As Worksheet, shtAnalysis As Worksheet
Dim iOffset As Long

Set shtData = Sheets("Data")
Set shtResult = Sheets("SampleResult")
Set shtAnalysis = Sheets("USRMResultsAnalysis")
iStartRowOfLastBlock = shtData.Cells(Rows.count, 1).End(xlUp).Offset(-3, 0).row






iOffset = 0
For iBlockRow = 0 To 1 ' <- 4 loops, 1 for each line
iStartRowOfLastBlock = iStartRowOfLastBlock + (iBlockRow / 1.25)

MsgBox iStartRowOfLastBlock
With shtResult
.Cells(2, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 1).Value
.Cells(4, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 2).Value
.Cells(5, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 3).Value
.Cells(1, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 4).Value
.Cells(1, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 5).Value
.Cells(3, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 6).Value
.Cells(6, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 7).Value
.Cells(7, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 8).Value
.Cells(4, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 9).Value
.Cells(4, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 10).Value
.Cells(4, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 11).Value
.Cells(12, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 12).Value
.Cells(13, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 13).Value
.Cells(14, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 14).Value
.Cells(12, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 15).Value
.Cells(13, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 16).Value
.Cells(14, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 17).Value
.Cells(20, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 18).Value
.Cells(20, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 19).Value
.Cells(21, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
.Cells(21, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
.Cells(20, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 21).Value
.Cells(22, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 22).Value
.Cells(20, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 23).Value
.Cells(20, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 24).Value
.Cells(21, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
.Cells(21, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
.Cells(20, 6 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 26).Value
.Cells(22, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 27).Value
.Cells(5, 11 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 28).Value
End With
iOffset = iOffset + 15
Next iBlockRow


iOffset = 30
For iBlockRow = 1 To 1 ' <- 4 loops, 1 for each line
iStartRowOfLastBlock = iStartRowOfLastBlock + (iBlockRow / 1.8)

MsgBox iStartRowOfLastBlock
With shtResult
.Cells(2, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 1).Value
.Cells(4, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 2).Value
.Cells(5, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 3).Value
.Cells(1, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 4).Value
.Cells(1, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 5).Value
.Cells(3, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 6).Value
.Cells(6, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 7).Value
.Cells(7, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 8).Value
.Cells(4, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 9).Value
.Cells(4, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 10).Value
.Cells(4, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 11).Value
.Cells(12, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 12).Value
.Cells(13, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 13).Value
.Cells(14, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 14).Value
.Cells(12, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 15).Value
.Cells(13, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 16).Value
.Cells(14, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 17).Value
.Cells(20, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 18).Value
.Cells(20, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 19).Value
.Cells(21, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
.Cells(21, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
.Cells(20, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 21).Value
.Cells(22, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 22).Value
.Cells(20, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 23).Value
.Cells(20, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 24).Value
.Cells(21, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
.Cells(21, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
.Cells(20, 6 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 26).Value
.Cells(22, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 27).Value
.Cells(5, 11 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 28).Value
End With
Next iBlockRow


iOffset = 45
For iBlockRow = 2 To 2 ' <- 4 loops, 1 for each line
iStartRowOfLastBlock = iStartRowOfLastBlock + (iBlockRow / 2.8)

MsgBox iStartRowOfLastBlock
With shtResult
.Cells(2, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 1).Value
.Cells(4, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 2).Value
.Cells(5, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 3).Value
.Cells(1, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 4).Value
.Cells(1, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 5).Value
.Cells(3, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 6).Value
.Cells(6, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 7).Value
.Cells(7, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 8).Value
.Cells(4, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 9).Value
.Cells(4, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 10).Value
.Cells(4, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 11).Value
.Cells(12, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 12).Value
.Cells(13, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 13).Value
.Cells(14, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 14).Value
.Cells(12, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 15).Value
.Cells(13, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 16).Value
.Cells(14, 9 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 17).Value
.Cells(20, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 18).Value
.Cells(20, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 19).Value
.Cells(21, 5 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
.Cells(21, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 20).Value
.Cells(20, 3 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 21).Value
.Cells(22, 4 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 22).Value
.Cells(20, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 23).Value
.Cells(20, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 24).Value
.Cells(21, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
.Cells(21, 8 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 25).Value
.Cells(20, 6 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 26).Value
.Cells(22, 7 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 27).Value
.Cells(5, 11 + iOffset).Value = shtData.Cells(iStartRowOfLastBlock, 28).Value
End With
Next iBlockRow

THE REST OF MY EXUBERANT CODE GOES AFTER THIS! SUPER EXCITED AND RELIEVED! NOW TIME FOR TEQUILA.