Dynamic MAX formula

[Ludo]

Hi,

I'm looking for a way to have the MAX & MIN values of a dynamic range.

This one gives me a compile error

Range("A5").Select
ActiveCell.Formula ="=MAX("A7:A" &
selection.currentregion.rows.count)"

any help welcome
Regards,
Ludo

 

[Andrew Taylor]

In the second line you don't need the quote before A7: it should be

ActiveCell.Formula = "=MAX(A7:A" & Selection.CurrentRegion.Rows.Count
& ")"

I suspect the formula still isn't quite doing what you want though
- maybe it should be something like:

ActiveCell.Formula = "=MAX(A7:A" & (Selection.CurrentRegion.Rows.Count
+5) & ")"

 

[Luke M]

Quotation marks were in the wrong place:

Range("A5").Select
ActiveCell.Formula = "=MAX(A7:A" & _
Selection.CurrentRegion.Rows.Count & ")"