Sep 19 2021 09:08 PM
Hello All,
Based upon resources found on the internet (various forums, websites, communities) I did able to create one excel solver macro which automates the solver calculations. Each solver calculation involves setting target cell to zero by changing variable cell. both are single cell entities.
And like that, it runs solver function 11 times and each time the variable cell changes.
For example, during the solver calculations, In the first calculation target cell is N7, and its changed to 0 by changing variable cell O38, now the first calculation produced one value in O38 cell.
Solver immediately starts calculating second calculation, and now its target cell is N8 while the variable cell is O38, and the cell is holding value generated after first calculation (it is the same cell from first calculation, with result from calculation 1). Now, second calculation completed, and O38 cell holds new value, while the previous value is erased.
What I did is I added spreadsheet code by right clicking the spreadsheet, the code basically does copy previous value in the cell to new location, but its not working with VBA macro. That means whatever automated calculations are performed after that the spreadsheet code copies the previous value to new location.
I did not able to do it correctly.
I have attached excel file involving all the code
What I have in my mind is,
How can create VBA macro, which is:
1. Performing first calculation in N7 with whatever value in cell 'O38'
2. Find out value of 0 in range of cells 'N7:N16' or just check whether 'N7' become 0
3. If value is 0 in any cell in range of cells 'N7:N16' or just cell 'N7'
4. then copy value in cell O38 to another location lets say cell 'T3'
5. takes pause 1 to 3 seconds
5. Start performing second calculation with N8
6. repeat all the tasks 1 to 5
like that complete calculations for range of cells 'N7:N16'.
At the end of macro I shall have multiple values captured from cell O38 and stored in multiple cells in T column one after another.
Please help me, I'm in desperate need, or assist me with the resources. from which I can develop something.
Thank you!
Regards,
Ash.K
Sep 20 2021 02:39 AM
SolutionTry this:
Dim I As Long
' SolverAutomation macro
Sub SolverAutomation1()
Application.EnableEvents = False
' Clear the output range
Range("T6:T17").ClearContents
' Initialize I
I = 6
' Perform solver
DoIt
Application.EnableEvents = True
End Sub
Sub DoIt(Optional Dummy)
' Solver steps
SolverReset
SolverOk SetCell:="$N$" & I, MaxMinVal:=3, ValueOf:=0, ByChange:="$O$38", Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve True
' Check whether cell in column N is near enough to 0
If Round(Range("N" & I).Value, 3) = 0 Then
' If so, copy O38 to column T
Range("T" & I).Value = Range("O38").Value
End If
' Increment I
I = I + 1
' Stop if I > 17
If I <= 17 Then
' Call DoIt with a short delay
Application.OnTime Now + TimeSerial(0, 0, 2), "DoIt"
End If
End Sub
Sep 20 2021 07:54 PM - edited Sep 20 2021 08:13 PM
Thank you for the response
In the line 21 of the solver, is it possible to put something like this:
If -0.01 < Round(Range("N" & I).Value, 3) < 0.01 Then
Essentially, What I want to mention is that, the 'If statement' I'm relaxing between range -0.01 to 0.01, and not strictly as 0
That means, if the cell shows value -0.009 then also the value in cell O38 will get copied to new spot in T column.
Something like that is it possible?
I'm beginner in VBA and trying to understand...
Sep 21 2021 03:13 AM
Change the line
If Round(Range("N" & I).Value, 3) = 0 Then
to
If Abs(Range("N" & I).Value) < 0.01 Then
Sep 20 2021 02:39 AM
SolutionTry this:
Dim I As Long
' SolverAutomation macro
Sub SolverAutomation1()
Application.EnableEvents = False
' Clear the output range
Range("T6:T17").ClearContents
' Initialize I
I = 6
' Perform solver
DoIt
Application.EnableEvents = True
End Sub
Sub DoIt(Optional Dummy)
' Solver steps
SolverReset
SolverOk SetCell:="$N$" & I, MaxMinVal:=3, ValueOf:=0, ByChange:="$O$38", Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve True
' Check whether cell in column N is near enough to 0
If Round(Range("N" & I).Value, 3) = 0 Then
' If so, copy O38 to column T
Range("T" & I).Value = Range("O38").Value
End If
' Increment I
I = I + 1
' Stop if I > 17
If I <= 17 Then
' Call DoIt with a short delay
Application.OnTime Now + TimeSerial(0, 0, 2), "DoIt"
End If
End Sub