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

elprimerjero

New Member
Joined
Jan 7, 2014
Messages
1
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 TimeElapsedTime (h)TC1 (2cm)TC2 (3cm)TC3 (4cm)
11/03/2014 9:3200:00:00:000.0000026.37626.22426.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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Watch MrExcel Video

Forum statistics

Threads
1,129,349
Messages
5,635,774
Members
416,880
Latest member
kyleswanson

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top