why these two macros (about Solver) give different results?

[novice_2010]

Hello, All,

My first macro is:

Sub Worked_Solver_Macro()

Range("A5").FormulaR1C1 = "5"
Range("A6").FormulaR1C1 = "1"
Range("A7").FormulaR1C1 = "=(R[-2]C-R[-1]C)^2"
SolverOk SetCell:="$A$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$5"
SolverSolve

End Sub

And the result is:

My second macro is:

Sub Not_Worked_Solver_Macro()
Dim Variable_X as Long

Range("A1").FormulaR1C1 = "5"
Variable_X=Range("A1").value

Range("A5") = Variable_X

Range("A6").FormulaR1C1 = "1"

Range("A7").FormulaR1C1 = "=(R[-2]C-R[-1]C)^2"
SolverOk SetCell:="$A$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1"
SolverSolve

End Sub

And the result is:

I thought the Solver result would be the same. However, they are not.
What mistakes did i make?

 

[novice_2010]

for my second macro,

Sub Not_Worked_Solver_Macro()
Dim Variable_X as Long

Range("A1").FormulaR1C1 = "5"
Variable_X=Range("A1").value

Range("A5") = Variable_X

Range("A6").FormulaR1C1 = "1"

Range("A7").FormulaR1C1 = "=(R[-2]C-R[-1]C)^2"
SolverOk SetCell:="$A$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1"
SolverSolve

End Sub

The result I expect should be:

Why the macro doesn't run as I expected?

 

[shg]

First, you should put numbers in the cells, not text.

Second, MaxMinVal:=2 mean to set the target cell to a Minimum.

 

[novice_2010]

thank you, shg.

"First, you should put numbers in the cells, not text"
I have tried several methods, such as:

Sub Not_Worked_Solver_Macro()
Dim Variable_X as Long

Range("A1").FormulaR1C1 = "=5"
.....

But still not work. How should I change the code for my second macro?

 

[shg]

Range("A1").Value = 5

What are you trying to do?

 

[novice_2010]

thank you.

I changed code to the following:

Sub Not_Worked_Solver_Macro()
Dim Variable_X As Long

Range("A1").Value = 5
Variable_X = Range("A1").Value

Range("A5").Value = Variable_X

Range("A6").Value = 1

Range("A7").FormulaR1C1 = "=(R[-2]C-R[-1]C)^2"
SolverOk SetCell:="$A$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1"
SolverSolve

End Sub

However, I got the same result.

 

[shg]

I understand you're trying to learn Solver, but once more: What are you trying to do in this example?

 

[novice_2010]

shg, thank you for asking.

Here is my actual problem (hopefully I can explain it clearly):

1. Suppose Range ("A1:A3") contain some numbers that needs to be optimized so that Range ("C1")can be minimized.

2. To expedite the calculation, I pass Range ("A1:A3") to some variables so that calculation can be done entirely in computer memory. After calculation is done, the result will be passed from computer memory to Range ("C1")

3. I want to use Solver to make the above process.

Did I explain my question?

 

[novice_2010]

As you may have noticed, I included a variable Variable_X in my second macro

Sub Not_Worked_Solver_Macro()
Dim Variable_X as Long

I guess I got the wrong result only because of this Variable_X.

 

[shg]

Here is my actual problem (hopefully I can explain it clearly):

1. Suppose Range ("A1:A3") contain some numbers that needs to ...

That may be a later objective, but right now I'm just trying to find out exactly what you want the eight lines of code that doesn't do what you want to actually do.