Simple program to calculate max and min in a column

[petercool9]

Hi guys,

I'm new in this fórum, and I need a bit of help with my programming.
I'm trying to build a program in VBA that calculate maximum and minimum of a column of numbers.
I've tried some stuff but I didn't get it yet.
For example this: I need to know the max and the min in column C

A B C
01/01/11 1 1,237.0
01/01/11 2 1,083.2
01/01/11 3 917.0
01/01/11 4 880.8
01/01/11 5 886.2
01/01/11 6 894.8
01/01/11 7 697.0
01/01/11 8 740.9
01/01/11 9 830.4

Private Sub CommandButton1_Click()
Dim teste As Integer

WorksheetFunction.Max (Range("C1:C9"))

MsgBox teste

End Sub


And I have another problem beside this one, I also need to show the numbers that correpond a range of values. For instance, I need all the prices between 0€ and 10€, I need that the program shows me that cells.

I leave in attach my excel file.

I appreciate all your help,
Thanks

Daniel Duarte

[nodrog100]

sounds like you want to see the maximum for each day, otherwise just use the normal =Max(...) function for the whole column?

You can nest if's inside max if you use array formulae
{ = MAX ( IF(A4:A7000=<criteria>, c1:c7000) ) }

Use "Ctrl+Sft + enter" to enter the formula as an array, which gives the {} brackets, have each day in a row in seperate sheet, refer the <criteria> refer to that cell, and copy the formula down?

Can you just use filters for 2nd option
Or add a column =IF(C...>criteria,"Y","N"), and then use {MAX} as above, or a combination of match/index to get what you need?

[petercool9]

Yes, but I want the maximum and minimum for the all year, so it's between Cell C3 and C8787.

I think its easier ot have all in one sheet, that's the reason I didn't divide for multiple sheets.

In the answer you gave me: { = MAX ( IF(A4:A7000=<criteria>, c1:c7000) ) } the 'criteria' you refer is what ?

[petercool9]

I have come up with this:

Private Sub CommandButton1_Click()

Dim teste As Variant

teste = WorksheetFunction.Max(Range("C4:C8787"))

Application.ScreenUpdating = True
MsgBox teste
End Sub


I tried to put a few numbers in Cell Z12 to cell Z15 and it worked fine.. but for the all column ( or even for a few numbers in column C ) and get all the time the answer '0' .

I dont know why this is happening.

[petercool9]

I have figured out . I have my excel with decimal numbers like 894.3 and the excel don't recognize this number, it has to have a comma, like 894,3.. No I can find with the code above the max and min number.. Thank you nodrog100 for your help, probably I will have some more doubts in my program.

Thanks
Daniel Duarte

[petercool9]

I said I will need more help :S

I'm having trouble in my sheet, because this data is from the Danish power Market, and they put the one thousand numbers like this: 2.493,00 for example.

But to find the max and min values the numbers have to be like this: 2493,00 -> without the point in the two thousand.

I have searched everything and I cannot change the numbers to the format I want.

Could you help me guys ?