Running Max in VBA

[davio565]

Hi there,

I'm having issues with a running max code as I'm not familiar with VBA. Essentially I have a cell with a formula in which goes up and down as I add values elsewhere. I need the VBA code to record and maintain the absolute max value this cell reaches (and not dip below 0).

So far I have go this to kind of work but only when the target cells value is input manually as a number. I need it to use the output of the formula of the target cell. Pretty sure this is a simple thing i'm missing but everything i try (such as inlaying the cells formula into the VBA code) doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$27" And IsNumeric(Output) Then
    If Target.Value > Target.Offset(-4, 1) Then
        Target.Offset(-4, 1) = Target.Value
    End If
End If
End Sub

The formula in C27 is: =(SUM(D30:D1011))+(SUM(C30:C1011))

I also need this to work in the same way across the sheet so also in G27, K27 etc. My half working code did this but i just added in more chunks of the above. As long as this doesn't cause it to slow massively I ok with that,

Any help much appreciated.

Thanks

 

[JLGWhiz]

I am not sure what the IsNumeric(Output) is doing, but I left it in for you to resolve. The Worksheet_Change event will not fire if the cell value changes as a result of formula calculation. This is by design. You can, however, use the Worksheet_Calculate event to trigger the macro. I re-wrote the macro to what I think you want to do, but you might need to do a little more work on it. This event does not use the Target variable to identify the changing cell on the sheet. BTW, it was being misused in the OP code. If the change was being made elsewhere to cause a change in C27, then C27 could not be the target. Give this a try and post back with problems.

Private Sub Worksheet_Calculate()
If  IsNumeric(Output) Then
    If Range("C27").Value > Range("C27").Offset(-4, 1) Then
        Range("C27").Offset(-4, 1) = Range("C27").Value
    End If
End If
End Sub

 

[davio565]

Yeh I was wondering what IsNumeric(Output) was adding to it too.

Well I'm happy it does exactly what I want it to do and runs much quicker than 25 pivot tables tricking excel into accepting a circular reference!!!

Thanks!

 

[JLGWhiz]

Yeh I was wondering what IsNumeric(Output) was adding to it too.

Well I'm happy it does exactly what I want it to do and runs much quicker than 25 pivot tables tricking excel into accepting a circular reference!!!

Thanks!

Glad you could use it,
regards, JLG