Forum Discussion

Copper Contributor

Sep 20, 2021

Solved

copy previous value of variable cell to another cell location in same spreadsheet

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 calcula...

VBAandSpreadSheetProblem.xlsm29 KB

Formulas and Functions

Macros and VBA

HansVogelaar

Sep 20, 2021

AshKot1

Try 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

HansVogelaar

MVP

Sep 20, 2021

AshKot1

Try 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

AshKot1
Copper Contributor
Sep 21, 2021
HansVogelaar
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...
- HansVogelaar
  MVP
  Sep 21, 2021
  AshKot1
  
  Change the line
  
  If Round(Range("N" & I).Value, 3) = 0 Then
  
  to
  
  If Abs(Range("N" & I).Value) < 0.01 Then