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.
my VBA code:
my Spreadsheet 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. takes pause 1 to 3 seconds
3. Find out value of 0 in range of cells 'N7:N16' or just check whether 'N7' become 0
4. If value is 0 in any cell in range of cells 'N7:N16' or just cell 'N7'
5. then copy value in cell O38 to another location lets say cell 'T3'
6. takes pause 1 to 3 seconds
7. Start performing second calculation with N8
8. repeat all the tasks 1 to 6
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
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.
my VBA code:
VBA Code:
' SolverAutomation macro
Sub SolverAutomation1()
Dim I As Integer
ActiveWorkbook.ActiveSheet.Activate
For I = 6 To 17
SolverReset
SolverOk SetCell:="$N$" & I, MaxMinVal:=3, ValueOf:=0, ByChange:="$O$38", Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve True
Next I
End Submy Spreadsheet code:
VBA Code:
'Private Sub Worksheet_Change(ByVal Target As Range)
'If Not Intersect(Target, Range("$O$38")) Is Nothing Then
'a = Sheets("Modeling").Cells(Rows.Count, "T").End(xlUp).Row + 1
'ActiveCell.Offset(-1, 0).Activate
'Sheets("Modeling").Range("T" & a).Value = ActiveCell.Value
'ActiveCell.Offset(1, 0).Select
'End If
'End Sub
Dim xRg As Range
Dim xChangeRg As Range
Dim xDependRg As Range
Dim xDic As New Dictionary
Private Sub WorkSheet_Change(ByVal Target As Range)
Dim I As Long
Dim xCell As Range
Dim xHeader As Range
Dim xCommText As String
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
xHeader = "PreviousValue:"
x = xDic.Keys
For I = 0 To UBound(xDic.Keys)
Set xCell = Range(xDic.Keys(I))
Set xDCell = Cells(xCell.Row, 20)
xDCell.Value = ""
xDCell.Value = xDic.Items(I)
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
Dim I, J As Long
Dim xRgArea As Range
On Error GoTo Label1
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set xDependRg = Target.Dependents
If xDependRg Is Nothing Then GoTo Label1
If Not xDependRg Is Nothing Then
Set xDependRg = Intersect(xDependRg, Range("$O$38"))
End If
Label1:
Set xRg = Intersect(Target, Range("$O$38"))
If (Not xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = Union(xRg, xDependRg)
ElseIf (xRg Is Nothing) And (Not xDependRg Is Nothing) Then
Set xChangeRg = xDependRg
ElseIf (Not xRg Is Nothing) And (xDependRg Is Nothing) Then
Set xChangeRg = xRg
Else
Application.EnableEvents = True
Exit Sub
End If
xDic.RemoveAll
For I = 1 To xChangeRg.Areas.Count
Set xRgArea = xChangeRg.Areas(I)
For J = 1 To xRgArea.Count
xDic.Add xRgArea(J).Address, xRgArea(J).Value
Next
Next
Set xChangeRg = Nothing
Set xRg = Nothing
Set xDependRg = Nothing
Application.EnableEvents = True
End SubWhat 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. takes pause 1 to 3 seconds
3. Find out value of 0 in range of cells 'N7:N16' or just check whether 'N7' become 0
4. If value is 0 in any cell in range of cells 'N7:N16' or just cell 'N7'
5. then copy value in cell O38 to another location lets say cell 'T3'
6. takes pause 1 to 3 seconds
7. Start performing second calculation with N8
8. repeat all the tasks 1 to 6
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
