Selecting and Copying Data from Tab Delimited Excel Sheet

[Impartial Derivative]

I have data that is imported into Excel in tab delimited formatting. I've included a screen shot of the spreadsheet just for clarification purposes as well. The data is reported in a neat manner but I need to reformat the data in order to make other calculations easier, so I've chosen to copy certain aspects of the data to a new sheet. I was trying to use DataRange.End(xlDown) function to select the data for each trajectory( Block of data on spreadsheet) but in my code when I run the sub only the last cell of data is being copied to the new sheet.

Here is what I've written so far:

Option Explicit
Sub Format()

Dim RawData As Worksheet
Dim DataRange As Range
Dim cell As Range
Dim ws As Worksheet


Set RawData = Sheets("exe")
Set ws = Sheets.Add
ws.Name = "Clean"
Set DataRange = RawData.Range("B2")
Set DataRange = RawData.Range("B2:" & DataRange.End(xlDown).Address)
For Each cell In DataRange
copyRow RawData.Range(cell.Row & ":" & cell.Row), ws
Next cell

End Sub

Sub copyRow(rng As Range, Clean As Worksheet)
    Dim newRange As Range
    Set newRange = Clean.Range("A1")
    If newRange.Offset(2).Value <> "" Then
        Set newRange = newRange.End(xlDown).Offset(1)
        Else
        Set newRange = newRange.Offset(1)
    End If
    rng.Copy
    newRange.PasteSpecial (xlPasteAll)
End Sub

Also, is there an easy way to alter this code so it can copy all the data for columns B2 and C2 and display them in the newly created worksheet?

[JBeaucaire]

Edit your code to remove the TWO code lines of Set DataRange and replace them with this one line of code:

Set DataRange = RawData.Range("B2", DataRange.Range("B2").End(xlDown))

[Impartial Derivative]

When I altered my code as you suggested I now receive an error: Object variable or With Block variable not set.

[JBeaucaire]

Can you explain, without referring to the code itself, what this code does exactly?

It appears to me to simply copy the data in sheet "exe" column B from row 2 down to the bottom of the data into sheet "clean" at the bottom of the existing data in column A. Is this right?

[Impartial Derivative]

Can you explain, without referring to the code itself, what this code does exactly?

It appears to me to simply copy the data in sheet "exe" column B from row 2 down to the bottom of the data into sheet "clean" at the bottom of the existing data in column A. Is this right?

Essentially yes, however my project is just a tiny bit more complex. I was writing this simple sub as a kind of refresher to coding in VBA but in reality my whole project is writing macros to facilitate data analysis for my research. I use a Java image analysis tool which I have optimized to export data to excel ( an example of this output can be seen in the first attachment). Then in VBA I would like to write a macro to display the data on displaying the average and average net speed in a way where I can sort all the trajectories by their frame number and then do averages across all trajectories for that frame. The main problem is that the number of trajectories varies from experiment to experiment but I need the macro to report all of the trajectories regardless of the size of the experimental sample. (I've attached a brief example of what I'd like it to generally look like)

[JBeaucaire]

I can't see what's in the cells when you post a picture. Please post the sample data sheets in a BEFORE/AFTER workbook. Thanks.

[Impartial Derivative]

Sorry for kind of being all over the place with this project my graduate student I work under is crunched for time and keeps changing exactly what he wants done as far as macros are concerned but as of right now I am simply trying to write a Do loop that can calculate Average Speed and Average Net Speed for all trajectories reported in the Excel file. I've attached my excel file with a few more details and a visual on what I'm trying to accomplish.

[JBeaucaire]

You have a function in Excel 2010 I don't have in Excel03 which should be making this whole thing quite easy for you, a formula function called AVERAGEIF(). It's like a SUMIF() but for averaging.

Anyway, this macro takes advantage of this function and creates a simple table in columns Q:R based on the data in columns A:F.

Sub AverageSpeed()
Dim M   As Long
Dim LR  As Long
    
LR = Range("A" & Rows.Count).End(xlUp).Row
M = Application.WorksheetFunction.Max(Range("A:A"))

With Range("G2:G" & LR)
    .FormulaR1C1 = "=IF(ISNUMBER(RC1), (R[1]C3-RC3)/(R[1]C2-RC2), """")"
    .Value = .Value
End With

Range("Q1:R1").Value = [{"Frame","Average"}]
Range("Q2") = 0
Range("Q3").Resize(M).FormulaR1C1 = "=R[-1]C + 1"
Range("R2:R" & M + 2).FormulaR1C1 = "=AVERAGEIF(R1C1:R" & LR & "C1, RC[-1], R1C7:R" & LR & "C7)"

End Sub

[Impartial Derivative]

I just ran the macro and it worked beautifully. I had come across the AverageIf function earlier but wasn't sure how to apply it. So my next question would be if I wanted to have this macro display the average net speed ( same formula as average speed but each trajectory references the zero speed) would I implement it after the with statement? Also, would the syntax for that calculation be almost identical?

[JBeaucaire]

As I didn't fully grasp the question, I'm going to guess that once I do understand the goal, yes, it will be a simple application of same/similar formulas/techniques.

Update your sample "results" using my uploaded wb, show me what you mean exactly.

[Impartial Derivative]

Sorry I realize my last description was not very clear so hopefully my example spreadsheet will clear up what I'm trying to accomplish.

[JBeaucaire]

That's the exact formula you want to enter in H2 going down?

=($C3-$C$2)/($B3-$B$2)

I would recommend:

=IF(ISNUMBER($A2), ($C3-$C$2)/($B3-$B$2), "")


Your comment "so I'd like to be able to account for that if it is possible." doesn't tell me what you're talking about, how to solve it... your sample data seems to show one thing and you immediately start to discuss a problem you did not present in the data nor demonstrate the logic for resolving it.

[Impartial Derivative]

One of the difficulties I am having with this project are that as a relatively inexperienced VBA user I am not familiar with RC1 notation and converting between A1 and RC1 so just because I can actually do the calculations I want in the Excel spreadsheet doesn't mean I can fully code the macro to do what I need. Secondly, my graduate student that I work for keeps changing what he wants the macro to report so I'm sorry if I seem scattered brained, but my goals for this macro seem to change rather abruptly. What I'm looking to do is the following calculations under the empty headers on my spreadsheet. I understand using the ISNUMBER to account for the delimited form of the data but as I said earlier I don't know how to write the code for my Net calculations in RC1 forms. The main thing I don't know how to do for the net calculations is write the code so for each block of data the code references the first entry of that data as the zero point. Also any help with the general syntax of the AVERAGEIF function so I can apply it to my other calculations would be greatly appreciated.