Dynamic Maximums

[Barry]

A macro runs until a certain cell = *x*.
For each loop a new numeric value is calculated in A1.
I need to know the maximum value that A1 was ever at during these
calculations i.e. during all these loops.
Is there any way to do this WITHOUT using another macro?
Everything I've tried so far just leads to *circular cell reference* errors.

TIA
Barry

 

[Harald Staff]

Hi Barry

Sub test()
Dim Dmax As Double, L As Long
Randomize
With Range("A1")
For L = 1 To 5000
.Value = Rnd() * 10000
If .Value > Dmax Then Dmax = .Value
Next
End With

MsgBox "Max value was " & Dmax

End Sub

HTH. Best wishes Harald

 

[Gigglefritz]

Try this:

In your declarations:

Dim A1LoopMax As Double

and as the first line in your loop:

A1LoopMax = Application.Max(A1LoopMax, Range("A1").Value)

 

[Bob Phillips]

Just store it in B1 if it is greater than the value already in B1 (or some
other cell).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Barry]

Thanks for your responses but I need to do this without using another macro.
Barry

 

[Harald Staff]

Thanks for your responses but I need to do this without using another macro.
Barry

That's silly. Post your one-and-only macro then, so we have a chance to
modify it.

Best wishes Harald

 

[Craig]

Why get all uptight with the guy [Barry] - all he's asking is *Is it
possible WITHOUT using another macro*; just as he said in his original post.
If it's not then say so.

Craig

 

[Harald Staff]

Why get all uptight with the guy [Barry] - all he's asking is *Is it
possible WITHOUT using another macro*; just as he said in his original post.
If it's not then say so.

Hi Craig.

The suggestions provided show how to do it within the existing macro. But if
yes/no are the only acceptable answers then yes.

Best wishes Harald

 

[Bob Phillips]

Craig,

We (and I include Harald here, hopefully with his agreement) do not haunt
these NGs simply to answer a question posed. We feel we have a right, even
a duty in some ways, to question what we consider to be an inappropriate
approach, or even question a 'silly' comment. Harald was correct, if it
wasn't a silly statement, kin seeking a reason before expending his energy
on seeking a solution .

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Why get all uptight with the guy [Barry] - all he's asking is *Is it
possible WITHOUT using another macro*; just as he said in his original post.
If it's not then say so.

Craig

That's silly. Post your one-and-only macro then, so we have a chance to
modify it.

Best wishes Harald