Find max value for each column and return the value on other sheet

[elprimerjero]

Hi all,

Could someone help me with the below question...I'm quite stuck....my knowledge in that matter are too basic.

I have "sheet 1" with raw data. The number of rows is variable and each column (E:AH) show different temperatures varying function of time. As is showed:
A B C D E F G .....

Scan	Time	Elapsed	Time (h)	TC1 (2cm)	TC2 (3cm)	TC3 (4cm)
1	1/03/2014 9:32	00:00:00:00	0.00000	26.376	26.224	26.196
...	....		...	...	...	...

<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

I would like to find the maximum value in each column with temperature and copy and paste them transpose on "sheet2", pasting also the first row, as is showed below:

A		B(max)
TC1 (2cm)		294.526
TC2( 3cm)		360.476

<colgroup><col><col><col></colgroup><tbody>
</tbody>

... ....

The point is that I'm using my own range...each time that I use the code the range is different and I just want to get the data from the range.

Public Sub run_CalcPeakTemp()



Dim myCalRange As Range
Dim iReply As Integer

On Error Resume Next




Set myCalcRange = Application.InputBox(Prompt:="Select first row and then Ctrl+Shift+down", Title:="Select Range", Type:=8)
myCalcRange.Select


If myCalcRange Is Nothing Then
iReply = MsgBox("Range not selected!")
Exit Sub


If myCalcRange Is notNothing Then
Call run_CalcPeakTemp
End If
End If

And here is where I'm stuck...I'm not able of doing it in a loop, I'm starting to learn now. I did poorly like that...step by step...

Dim VarMaxVal As Variant
VarMaxVal = 0


VarMaxVal = Application.WorksheetFunction.Max(Columns(1))

Sheets("Calc").Select
Range("A1").Select
ActiveCell.Offset(1, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = VarMaxVal

VarMaxVal = Application.WorksheetFunction.Max(myCalcRange.Columns(2))

Sheets("Calc").Select
Range("A1").Select
ActiveCell.Offset(2, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = VarMaxVal
And so on.

.....Nether I was able of copy the first row of my range selected.

I would appreciate some help.

Thanks in advance.