Macro for iterations....

[Guest]

I have a multisheet database that is affected by a parameter in cell C5 which
has a range of from -.4 to 1.25, and can be adjusted in increments of .05

The impact of changes to C1 are summarized in cell G5

Can I create a macro that will find the maximimum value displayed in G5 by
iterating C1 through the above ranges in .05 increments?

 

[Guest]

Consider using Solver

 

[Dave Peterson]

I'm confused about what cell you're changing--is it C5 or C1???

I guessed C1:

Option Explicit
Sub testme()

Dim myInc As Double

Dim myInputCell As Range
Dim myOutputCell As Range

Dim myMaxOut As Double
Dim myMaxIn As Double

Dim NewMax As Boolean

Dim myStart As Double
Dim myEnd As Double
Dim myStep As Double

myStart = -0.4
myEnd = 1.25
myStep = 0.05

With Worksheets("sheet1")
Set myInputCell = .Range("C1")
Set myOutputCell = .Range("G5")
For myInc = myStart To myEnd Step myStep
myInputCell.Value = myInc
If myInc = myStart Then
'gotta start somewhere
NewMax = True
ElseIf myOutputCell.Value > myMaxOut Then
'a real new max
NewMax = True
Else
'keep looking
NewMax = False
End If
If NewMax = True Then
myMaxOut = myOutputCell.Value
myMaxIn = myInputCell.Value
End If
Next myInc
End With

MsgBox "Max at: " & myMaxIn & vbLf & "Max of: " & myMaxOut

End Sub

 

[Guest]

Thank you ever so much - this worked fabulously!

I'm confused about what cell you're changing--is it C5 or C1???

I guessed C1:

Option Explicit
Sub testme()

Dim myInc As Double

Dim myInputCell As Range
Dim myOutputCell As Range

Dim myMaxOut As Double
Dim myMaxIn As Double

Dim NewMax As Boolean

Dim myStart As Double
Dim myEnd As Double
Dim myStep As Double

myStart = -0.4
myEnd = 1.25
myStep = 0.05

With Worksheets("sheet1")
Set myInputCell = .Range("C1")
Set myOutputCell = .Range("G5")
For myInc = myStart To myEnd Step myStep
myInputCell.Value = myInc
If myInc = myStart Then
'gotta start somewhere
NewMax = True
ElseIf myOutputCell.Value > myMaxOut Then
'a real new max
NewMax = True
Else
'keep looking
NewMax = False
End If
If NewMax = True Then
myMaxOut = myOutputCell.Value
myMaxIn = myInputCell.Value
End If
Next myInc
End With

MsgBox "Max at: " & myMaxIn & vbLf & "Max of: " & myMaxOut

End Sub