Consulting

Results 1 to 7 of 7

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

  1. #1

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

    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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    After

    iStartRowOfLastBlock = iStartRowOfLastBlock + iBlockRow


    put
    Msgbox iStartRowOfLastBlock


    and see what happens
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    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?
    Last edited by maraecole3; 07-08-2019 at 05:54 PM. Reason: wrong vocab

  4. #4
    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

  5. #5
    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.

  6. #6
    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.

  7. #7
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •