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

elprimerjero

New Member
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

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
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.

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,154
Messages
5,857,680
Members
431,891
Latest member
shirazx3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back