|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Data sorting macro
Hello!
I am working on writing a macro to sort data from a research study. I have one column of data lables, one column of timing information and 8 columns of data. The macro I have right now takes the first two numbers in the timing information, finds all of the corresponding data for that time slot and puts it into a new spreadsheet named with reference to the data label. See code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 5/22/2006 by Laura Dim c As Range, r As Long, GetSht As Worksheet, PutSht As Worksheet Dim LstRow As Long Set GetSht = ActiveSheet ' Assigns a working name to the data/source sheet so it can be referenced easily LstRow = GetSht.Cells(Rows.Count, 3).End(xlUp).Row ' Finds the last used row in column C of the source sheet For Each c In Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row) ' This line defines the range from B1 to the last used cell in column B ' by tacking the row number of the first filled cell found looking upwards from ' the last cell in column B. Sheets.Add ActiveSheet.Name = c.Offset(0, -1).Value 'Sets the new sheet's new to a concatenation of the rang limits Set PutSht = ActiveSheet PutSht.Cells(1, 1) = "Time" PutSht.Cells(1, 2) = "Anterior Deltoid" PutSht.Cells(1, 3) = "Posterior Deltoid" PutSht.Cells(1, 4) = "Latissimus Dorsi" PutSht.Cells(1, 5) = "Pectoralis Major" PutSht.Cells(1, 6) = "Biceps" PutSht.Cells(1, 7) = "Triceps" PutSht.Cells(1, 8) = "Wrist Flexors" PutSht.Cells(1, 9) = "Wrist Extensors" ' Puts column headings in the new output sheet For r = 1 To LstRow ' Loops through all the cells in column B of the source sheet looking for range matches If GetSht.Cells(r, 3) >= c And GetSht.Cells(r, 3) <= c.Offset(3, 0) Then GetSht.Range(GetSht.Cells(r, 3), GetSht.Cells(r, 11)).Copy PutSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) ' If there is a range match, the cells in column B:J are copied and pasted into the ' the first emtpy cell in column A of the current target sheet. The extra 8 cells ' are automatically pasted to the cells to teh right of the cell specified. End If Next r Columns("A:J").AutoFit ' Auto Adjust the target sheet's columns A width. Next c End Sub I need to make a few changes to it, and I'm not exactly sure how . .. My timing information contains 3 events - a reaction time, movement time and a holding time. So instead of having a spreadsheet for each event, I would like to create one for the task containing the reaction, movement and holding time, and then use conditional formatting to highlight each section. So from my timing column I need it to find all of the data that fits between the first and fourth cell, then highlight all of the data between the 1st and 2nd one color, then data between the 2nd and 3rd another, and between the 3rd and 4th a third color. This needs to repeat then for cells 4-7, 7-10, etc. Any ideas?? Thanks! |
![]() |
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > Data sorting macro |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|