PDA

View Full Version : Marco to Average, transpose, paste data into new work book



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

Chabu
05-17-2011, 01:01 PM
First avoid to select or activate cells or ranges. don't do

Rows("6:6").Select
Selection.Insert Shift:=xlDown but
Rows("6:6").Insert Shift:=xlDown

Better is to refer to sheet, ranges, cells by declaring objects
for instance
Dim insertRow as Range
Set insertRow = Rows("6:6")
insertRow.Insert Shift:=xlDown

I always start of any macro by defining objects for the workbook, all sheets ranges, rangeindexes,...
It is more work to start but it pays of later.

so continuing the example above I would write

Dim wb1 As Workbook
Set wb1 = Workbooks("samples.xls")
Dim mySheet As Worksheet
Set mySheet = wb1.Worksheets("sheet6")
Dim insertRowIndex As Integer
insertRowIndex = 6
Dim insertRow As Range
Set insertRow = mySheet.Rows(insertRowIndex)

insertRow.Insert Shift:=xlDown

Chabu
05-17-2011, 01:17 PM
for averages use excels worksheetfunctions

Application.WorksheetFunction.Average (averageRange)

Define the range as in the post above

Chabu
05-17-2011, 01:44 PM
If you want to move through column ranges alfanumerically, here are some functions to help you with that


Public Function nextCol(ByVal c As String) As String
c = UCase(c)
Select Case Right(c, 1)
Case ""
nextCol = "A"
Case "Z"
nextCol = nextCol(Left(c, Len(c) - 1)) & Chr(Asc("A"))
Case "A" To "Y"
nextCol = Left(c, Len(c) - 1) & Chr(Asc(Right(c, 1)) + 1)
Case Else
nextCol = ""
End Select
End Function

Public Function prevCol(ByVal c As String) As String
c = UCase(c)
Select Case Right(c, 1)
Case "A"
If Len(c) = 1 Then
prevCol = ""
Else
prevCol = prevCol(Left(c, Len(c) - 1)) & "Z"
End If
Case "B" To "Z"
prevCol = Left(c, Len(c) - 1) & Chr(Asc(Right(c, 1)) - 1)
Case Else
prevCol = ""
End Select
End Function

Public Function toColString(cn As Integer) As String
If cn = 0 Then
toColString = ""
Else
If (cn Mod 26) = 0 Then
toColString = toColString((cn \ 26) - 1) & "Z"
Else
toColString = toColString(cn \ 26) & Chr(Asc("A") + (cn Mod 26) - 1)
End If
End If
End Function

Public Function toColNum(cs As String) As Integer
cs = UCase(cs)
Select Case Right(cs, 1)
Case ""
toColNum = 0
Case "A" To "Z"
toColNum = 26 * toColNum(Left(cs, Len(cs) - 1)) + Asc(Right(cs, 1)) - Asc("A") + 1
Case Else
toColNum = 0
End Select
End Function

cmwoodward
05-19-2011, 07:29 AM
Thank you Chabu, I implemented your suggestions and used the provided functions and everything works great. Now that I have that section working, I now want to continue to transposing the calculated averages and then paste on a new worksheet. What I want it to add code to my macro so that when the averages are calculated and inserted into my worksheet, the averages are copied, transposed (row to column) and then the user must select a cell in a new worksheet. Once the cell is selected the copied and transpose averages are pasted as a new column. I have seen examples macros where it waits for a user to enter text into a cell, but not wait until a cell is selected. Is this possible?

Thank you again for your help with the first part of my macro! :friends:

Chabu
05-19-2011, 11:20 AM
Hello Catherine,

That might be possible with events (worksheet selectionchange) but I would not do that, much more complicated than defining that location before you start.

If I were you I would either create a new sheet in your sub and write the results in that sheet while you are calculating them or ask the user to specify a cell address before you start the macro. (e.g. in a "control" sheet let him enter "=" and select the target.

Greetings
Bruno