Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for iterations....
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for iterations....
Consider using Solver
-- Gary''s Student - gsnu200716 "Nick Krill" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for iterations....
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 Nick Krill wrote: 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? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro for iterations....
Thank you ever so much - this worked fabulously!
"Dave Peterson" wrote: 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 Nick Krill wrote: 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterations | New Users to Excel | |||
copying data during iterations | Excel Discussion (Misc queries) | |||
How do I turn iterations off permanently? | Excel Discussion (Misc queries) | |||
Is there a way where I can default iterations | Excel Discussion (Misc queries) | |||
How do i compute the average value of 100 iterations? | Excel Worksheet Functions |