SOLVED

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

Copper Contributor

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 

3 Replies
best response confirmed by AshKot1 (Copper Contributor)
Solution

@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

@Hans Vogelaar

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

@AshKot1 

Change the line

If Round(Range("N" & I).Value, 3) = 0 Then

to

If Abs(Range("N" & I).Value) < 0.01 Then
1 best response

Accepted Solutions
best response confirmed by AshKot1 (Copper Contributor)
Solution

@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

View solution in original post