Dynamic Maximums

B

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
 
H

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
 
G

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)
 
B

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)
 
H

Harald Staff

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

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

Best wishes Harald
 
C

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
 
H

Harald Staff

Craig said:
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.
Click to expand...

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
 
B

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)

Craig said:
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 said:
That's silly. Post your one-and-only macro then, so we have a chance to
modify it.

Best wishes Harald
Click to expand...
Click to expand...
 
Top