CODEWITHSUNDEEP
excelvbafor-loopcopyoffset
Rae Van Sandt
Rae Van Sandt

Reputation: 17

Using offset within a loop to copy and paste

I have biomechanics data (i.e. a person walking) that is collected with video recordings and stored as 3 columns of data for each frame number. In a separate data table in the same worksheet, I have data that tells me which excel row number each biomechanic event happens (i.e. the foot strikes the ground or comes up off of the ground). I am trying to take the biomechanics data that is stored in one spreadsheet in rows, and transpose it so that each step is in its own column. However, each biomechanic event (i.e. each step) is a different number of rows.

The biomechanics raw data can be seen here: biomechanics raw data

What I want it to do is copy individual events (steps) to a second worksheet like this: desired biomechanic event output

Instead, the code runs through the loop successfully for each biomechanic event, but places the data in the same place, "A4". This is because I do not know how to use offset within a loop and based on a variable: current biomechanic event output with undesired overlay of data

The code I have currently is here: 

Private Sub CommandButton1_Click()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")
Dim LeftStrike As Range, FrameLTD As Range, FrameLTDx As Range
Dim lrL As Long, LastFrame As Long
Dim LeftTD As Variant
Dim LeftTDx As Variant

lrL = ws.Range("H" & ws.Rows.Count).End(xlUp).Row
LastFrame = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

Set LeftStrike = ws.Range("H2:H" & lrL)

For Each FrameLTD In LeftStrike
   If InStr(FrameLTD, "Foot Strike") Then
           LeftTD = FrameLTD.Offset(0, 2)
          'COMMENT: Set LeftTDx = LeftTD + 1
         ws.Range("A" & LeftTD, "D" & LastFrame).Copy ws2.Range("A4")

        ' COMMENT: ws.Range("A" & LeftTDx, "D" & LastFrame).Copy ws2.Range("FrameLTD").Offset(0, 5)

    End If
Next FrameLTD
End Sub

What I want the program to do is: 1. Find the string "Foot Strike" and tell me the value 2 columns over and call it LeftTD (it does this). 2. Starting at the row number value of LeftTD, copy cells in columns A through D and past it into a new worksheet starting at A4 (it does this). 3. For the next "Foot Strike" and all remaining, do the same thing in steps 1 and 2, but then copy the cells with an offset(0,5) from the previous copy & pasted event (it does not do this). 4. Do this until column H is empty (it does do this).

My thought was that if I declare the variables as "variant" then I could tell it to count the next LeftTD as LeftTD +1, then tell it to offset the range of the range variable by 5, it would work. But if I un-comment those lines, then I get a type mismatch error. Also, interestingly, if I hit F8 and run through the program step by step, it only copies each biomechanic event (each step) on the 2nd, 5th, 7th, and 9th time through the program.

So my specific question is, how do I use a variable offset within a For loop that is looping through a range variable?

I thought about using .Find and .FindNext, but the information available on the web is really lacking for an example that is close to what I need to do. Indeed, finding information on copy and paste with both variable rows and variable offset columns is also difficult for me to find. Any offered insight would be greatly appreciated. Thank you!!!!

Upvotes: 0

Views: 240

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

Add a counter to identifying the destination 

Dim DestCol As Long
'...
DestCol = 1
For Each FrameLTD In LeftStrike
    If InStr(FrameLTD, "Foot Strike") Then
        LeftTD = FrameLTD.Offset(0, 2)
        ws.Range("A" & LeftTD, "D" & LastFrame).Copy ws2.Cells(4, DestCol)
        DestCol = DestCol + 5
    End If
Next FrameLTD

Upvotes: 1

Related Questions