Help with Excel Macro, Data Sorter

August 20, 2010 at 12:32:39
Specs: Windows XP
Hello,

I'm trying to create a macro to do a variety of functions. The data set I'm working with is generate from a Fuel Injection ECU. Once a test run is done, the data is exported into Excel. I end up with four columns of data, Time Stamp, MAP, RPM, and O2 Reading.

The Time Stamp column is deleted, then the MAP value is rounded to the nearest multiple of 10 using MRound, RPM is rounded to the nearest multiple of 100 using the same function, and the O2 Reading is rounded to the nearest 2 decimal places.

I then swap columns A (MAP) and B (RPM) so RPM is in column A, followed by MAP and then O2 readings.

I then pull off each set of RPM data and insert it into a new worksheet labeled with the RPM data.

Once the data set for a certain RPM range is in the new worksheet I then sort the data according to the MAP data and separate out the initial columns into the different MAP ranges. Finally I average the O2 reading for each MAP range at that RPM.

The data sets are very large with anywhere from 1000-5000 entries so as you can imagine it takes awhile to sort through this by hand.

Any help, sample codes that I could piece together, etc would be appreciated.


See More: Help with Excel Macro, Data Sorter

Report •

#1
August 22, 2010 at 19:11:29
re: sample codes that I could piece together

The Time Stamp column is deleted

Columns(1).Delete
or
Columns("A").Delete

the MAP value is rounded to the nearest multiple of 10 using MRound

For rwMAP = 1 To 1000
 Range("A" & rwMAP) = _
   Application.WorksheetFunction.Round(Range("A" & rwMAP) / 10, 0) * 10
Next

Note: MROUND can be used in VBA if you have the Analysis ToolPak installed and you set a reference to atpvbaen.xls in the VBA editor.

O2 Reading is rounded to the nearest 2 decimal places

For rwMAP = 1 To 1000
 Range("A" & rwMAP) = _
   Application.WorksheetFunction.Round(Range("A" & rwMAP), 2)
Next

swap columns A (MAP) and B (RPM)

Columns("B:B").Cut
Columns("A:A").Insert Shift:=xlToRight

Everything after that is going to need a little more explanation. I'm not sure what you mean by each set of RPM data or MAP range at that RPM.


Report •
Related Solutions


Ask Question