cmwoodward
05-16-2011, 09:40 AM
Good Day Everyone,
I have just started writing macros for my work to be used with data collection. I have a bunch of signals that are sample and recorded. Each signal is in its own column. The number of signals recorded are variable and the number of samples are also varible.
I want is macro that will find the averages of the data in each column, paste the averages into a new row. I have written code that does this, however it only works if data is in a certain format. Format example:
A B C ........
1
2
3 signal1 signal2
4 sample1 sample2
5 sample1 sample2
.
.
Data to be averaged starts on $B:$4. This has been hard coded. What I want is to be able to select the cell where the data starts (sample1) with the mouse, then run the macro which averages the samples in each column starting with the one selected untill there are no more columns with data. The following is the code I have written (note this is my first macro coding):
Sub AVERAGE_EDL_DATA()
'
' AVERAGE_EDL_DATA Macro
' Keyboard Shortcut: Ctrl+Shift+A
'
'THIS MACRO IS DESIGNED TO BE USED WITH THE EDL DATA FOR IMCS SIGNALS
'INITIALIZE VARIABLES
colNum = 66 'THIS IS THE CORRESPONDING NUMBER FOR COLUMN LETTER EX. 66 = "B"
colCount = 2 'COUNTS THE NUMBER OF COLUMNS
rollOverCount = 0 'COUNTS THE NUMBER OF TIMES THROUGH THE ALPHABET
'colNum = 0 'THIS IS THE CORRESPONDING NUMBER FOR COLUMN LETTER EX. 65 = "A"
colNumTwo = 65 'THIS IS THE CORRESPONDING NUMBER FOR SECOND COLUMN NUMBER EX. "A" IN "AB"
XYFFlag = 0 'FLAG USED TO DETERMINE IF "A" OR "AA" FORMAT IS TO BE USED
loopCount = 2
'SEARCH FOR A CELL WITH "N/A" AND REPLACE WITH "999999" FOR AVERAGING
Cells.Replace What:="N/A", Replacement:="999999", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'INSERT NEW ROW FOR AVERAGES
'AVERAGE ROW WILL BE INSERTED IN ROW 6
Rows("6:6").Select
Selection.Insert Shift:=xlDown
'LABEL NEW ROW AS "AVERAGES"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Averages"
'FORMAT NEW ROW LABEL
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
'FIRST FIND THE LAST ROW AND COLUMN
'THIS ASSUMES DATA TO BE AVERAGED STARTS IN COLUMN "B"
RwLast = Range("B65536").End(xlUp).Row
ClLast = Range("IV" & (RwLast)).End(xlToLeft).Column
'DEBUGGING PURPOSES
'Range("IV" & (RwLast)).End(xlToLeft).Select
'Range("H4") = RwLast
'Range("H5") = ClLast
'AVERAGE DATA IN EACH COLUMN USING A LOOP
For loopCount = 2 To ClLast
'TEST IF STILL IN FIRST CYCLE OF THE ALPHABET
If XYFFlag = 0 Then
colLetter = Chr(colNum)
'NOT IN THE FIRST CYCLE OF THE ALPHABET
Else
'COMBINE THE CHARACTERS TO "XY" FORMAT
colLetter = Chr(colNumTwo) & Chr(colNum)
End If
'INSERT CALCULATED AVERAGE IN ROW "6" IN CURRENT COLUMN
Range(colLetter & "6") = Application.WorksheetFunction.Average(Range(colLetter & "8", colLetter & RwLast))
'TEST IF COLUMNS "A - Z" HAS BEEN AVERAGED
If colCount = 26 Then
'COUNT HOW MANY TIMES IT HAS GONE THROUGH THE ALPHABET
rollOverCount = rollOverCount + 1
'TEST IF IT IS TIME TO ROLL OVER TO "XY" FORMAT
If rollOverCount = 1 Then
colNumTwo = 65 'ROLL OVER "X" TO "A"
XYFFlag = 1 'SET FLAG AS FOR "XY" FORMAT
'ALREADY IN "XY" FORMAT, INCREMENT "X"
Else
colNumTwo = colNumTwo + 1
End If
colNum = 65 'ROLL OVER "Y" TO "A" FOR NEXT CYCLE OF ALPHABET
'XYFFlag = 1 'SET FLAG AS FOR "XY" FORMAT
colCount = 0 ' RESET THE COUNT FOR NEXT CYCLE OF ALPHABET
'COLUMNS "A - Z" HAVE NOT BEEN AVERAGED YET, CONTINUE IN "X" FORMAT
Else
colNum = colNum + 1 'INCREMENT "X"
End If
'INCREMENT THE COLUMN COUNT FOR DETERMINING IF COLUMNS "A-Z" HAS BEEN AVERAGED
colCount = colCount + 1
'INCREMET LOOP VARIABLE
Next loopCount
'ROTATE ROW "6"(SIGNAL IDs) BY 70 DEGREES FOR BETTER VIEWING
Rows("5:5").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 70
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
'BOLD THE AVERAGES
Rows("6:6").Select
Selection.Font.Bold = True
'SEARCH FOR A CELL WITH "999999" AND REPLACE WITH "N/A"
Cells.Replace What:="999999", Replacement:="N/A", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
The method I used is very messy and there is alot of formatting, however the main part I want to change is instead of setting variables to start averaging at column B row 6, I want that to be varibale. I also would like to avoid my messy loops I used to average the data through all the columns (remember number of columns vary for each set of data samples).
I have no idea where to begin.
Thank you,
Catherine
I have just started writing macros for my work to be used with data collection. I have a bunch of signals that are sample and recorded. Each signal is in its own column. The number of signals recorded are variable and the number of samples are also varible.
I want is macro that will find the averages of the data in each column, paste the averages into a new row. I have written code that does this, however it only works if data is in a certain format. Format example:
A B C ........
1
2
3 signal1 signal2
4 sample1 sample2
5 sample1 sample2
.
.
Data to be averaged starts on $B:$4. This has been hard coded. What I want is to be able to select the cell where the data starts (sample1) with the mouse, then run the macro which averages the samples in each column starting with the one selected untill there are no more columns with data. The following is the code I have written (note this is my first macro coding):
Sub AVERAGE_EDL_DATA()
'
' AVERAGE_EDL_DATA Macro
' Keyboard Shortcut: Ctrl+Shift+A
'
'THIS MACRO IS DESIGNED TO BE USED WITH THE EDL DATA FOR IMCS SIGNALS
'INITIALIZE VARIABLES
colNum = 66 'THIS IS THE CORRESPONDING NUMBER FOR COLUMN LETTER EX. 66 = "B"
colCount = 2 'COUNTS THE NUMBER OF COLUMNS
rollOverCount = 0 'COUNTS THE NUMBER OF TIMES THROUGH THE ALPHABET
'colNum = 0 'THIS IS THE CORRESPONDING NUMBER FOR COLUMN LETTER EX. 65 = "A"
colNumTwo = 65 'THIS IS THE CORRESPONDING NUMBER FOR SECOND COLUMN NUMBER EX. "A" IN "AB"
XYFFlag = 0 'FLAG USED TO DETERMINE IF "A" OR "AA" FORMAT IS TO BE USED
loopCount = 2
'SEARCH FOR A CELL WITH "N/A" AND REPLACE WITH "999999" FOR AVERAGING
Cells.Replace What:="N/A", Replacement:="999999", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'INSERT NEW ROW FOR AVERAGES
'AVERAGE ROW WILL BE INSERTED IN ROW 6
Rows("6:6").Select
Selection.Insert Shift:=xlDown
'LABEL NEW ROW AS "AVERAGES"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Averages"
'FORMAT NEW ROW LABEL
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
'FIRST FIND THE LAST ROW AND COLUMN
'THIS ASSUMES DATA TO BE AVERAGED STARTS IN COLUMN "B"
RwLast = Range("B65536").End(xlUp).Row
ClLast = Range("IV" & (RwLast)).End(xlToLeft).Column
'DEBUGGING PURPOSES
'Range("IV" & (RwLast)).End(xlToLeft).Select
'Range("H4") = RwLast
'Range("H5") = ClLast
'AVERAGE DATA IN EACH COLUMN USING A LOOP
For loopCount = 2 To ClLast
'TEST IF STILL IN FIRST CYCLE OF THE ALPHABET
If XYFFlag = 0 Then
colLetter = Chr(colNum)
'NOT IN THE FIRST CYCLE OF THE ALPHABET
Else
'COMBINE THE CHARACTERS TO "XY" FORMAT
colLetter = Chr(colNumTwo) & Chr(colNum)
End If
'INSERT CALCULATED AVERAGE IN ROW "6" IN CURRENT COLUMN
Range(colLetter & "6") = Application.WorksheetFunction.Average(Range(colLetter & "8", colLetter & RwLast))
'TEST IF COLUMNS "A - Z" HAS BEEN AVERAGED
If colCount = 26 Then
'COUNT HOW MANY TIMES IT HAS GONE THROUGH THE ALPHABET
rollOverCount = rollOverCount + 1
'TEST IF IT IS TIME TO ROLL OVER TO "XY" FORMAT
If rollOverCount = 1 Then
colNumTwo = 65 'ROLL OVER "X" TO "A"
XYFFlag = 1 'SET FLAG AS FOR "XY" FORMAT
'ALREADY IN "XY" FORMAT, INCREMENT "X"
Else
colNumTwo = colNumTwo + 1
End If
colNum = 65 'ROLL OVER "Y" TO "A" FOR NEXT CYCLE OF ALPHABET
'XYFFlag = 1 'SET FLAG AS FOR "XY" FORMAT
colCount = 0 ' RESET THE COUNT FOR NEXT CYCLE OF ALPHABET
'COLUMNS "A - Z" HAVE NOT BEEN AVERAGED YET, CONTINUE IN "X" FORMAT
Else
colNum = colNum + 1 'INCREMENT "X"
End If
'INCREMENT THE COLUMN COUNT FOR DETERMINING IF COLUMNS "A-Z" HAS BEEN AVERAGED
colCount = colCount + 1
'INCREMET LOOP VARIABLE
Next loopCount
'ROTATE ROW "6"(SIGNAL IDs) BY 70 DEGREES FOR BETTER VIEWING
Rows("5:5").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 70
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
'BOLD THE AVERAGES
Rows("6:6").Select
Selection.Font.Bold = True
'SEARCH FOR A CELL WITH "999999" AND REPLACE WITH "N/A"
Cells.Replace What:="999999", Replacement:="N/A", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
The method I used is very messy and there is alot of formatting, however the main part I want to change is instead of setting variables to start averaging at column B row 6, I want that to be varibale. I also would like to avoid my messy loops I used to average the data through all the columns (remember number of columns vary for each set of data samples).
I have no idea where to begin.
Thank you,
Catherine