Assigning to a variable a value from an Excel cell
Assigning to a variable a value from an Excel cell
(OP)
A little backgound. Many years ago I did some basic programming with VB, but haven't touched it for ~ 10 years. I am running a complicated macro in Excel which loops through over 10K separate calculations and have come across a snag. I'm using the GoalSeek imbedded function within Excel to calculate a "what if" scenario - what value of a given cell causes another cell to equate to 0. Unfortunately, in several instances the GoalSeek function does not converge and the result is garbage. So I thought about supplementing my VB macro with a Do Loop to increment my "changing" cell value until the value of my desired resultant cell = 0. In a test code to establish how I want it to work I can't get the variable CALC to recognize the value in cell C3. Can someone help me with a VB 101 resolution? CALC evaluates to -1 rather than C8-C3, takes on a fixed value of 501 and the loop becomes infinite. Here is my code:
Sub Macro1 ()
Dim INC, PRESS, TEST, STRESS As Integer
Dim CALC As Variant
PRESS = 100
INC = 1
STRESS = 500
Range("C8").Select
ActiveCell.FormulaR1C1 = STRESS
CALC = Range("C3").Select
TEST = STRESS - CALC
Range("B1").Select
ActiveCell.FormulaR1C1 = "99"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = PRESS
Range("C6").Select
ActiveCell.FormulaR1C1 = TEST
TEST = STRESS - CALC
Range("C6").Select
ActiveCell.FormulaR1C1 = TEST
Do While TEST > 0
PRESS = PRESS + INC
Range("B1").Select
ActiveCell.FormulaR1C1 = PRESS
Loop
End Sub
Thanks in Advance.
Sub Macro1 ()
Dim INC, PRESS, TEST, STRESS As Integer
Dim CALC As Variant
PRESS = 100
INC = 1
STRESS = 500
Range("C8").Select
ActiveCell.FormulaR1C1 = STRESS
CALC = Range("C3").Select
TEST = STRESS - CALC
Range("B1").Select
ActiveCell.FormulaR1C1 = "99"
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = PRESS
Range("C6").Select
ActiveCell.FormulaR1C1 = TEST
TEST = STRESS - CALC
Range("C6").Select
ActiveCell.FormulaR1C1 = TEST
Do While TEST > 0
PRESS = PRESS + INC
Range("B1").Select
ActiveCell.FormulaR1C1 = PRESS
Loop
End Sub
Thanks in Advance.





RE: Assigning to a variable a value from an Excel cell
Here are some ideas:
Idea 1:
DIM CALC as a REAL or INTEGER and try replacing:
CALC = Range("C3").select
with
CALC = RANGE("C3").VALUE
IDEA #2:
Or, you could
DIM CALC as OBJECT
CALC = RANGE("C3").SELECT
TEST = STRESS-CALC.VALUE
RE: Assigning to a variable a value from an Excel cell
Consider using the BISECTION method to find a solution. It is easy to implement in two nested loops and may run a little faster than the incremental approach you are using. In my experience it is a good VBA replacement for Excel's goalseek.
I referenced the following info as I setup the method in VBA:
http
You could also simplify your code by using
RANGE("B1").VALUE=99
RANGE("C6").VALUE=TEST
for example, instead of selecting the cell, then changing the value.
RE: Assigning to a variable a value from an Excel cell
RE: Assigning to a variable a value from an Excel cell
CODE
CODE
Dim PRESS As Integer
Dim TEST As Integer
Dim STRESS As Integer
in your version you create three variant and one integer
RE: Assigning to a variable a value from an Excel cell
CODE
ActiveCell.FormulaR1C1 = "99"
CODE
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Assigning to a variable a value from an Excel cell
Just a shorter version of your intent
CODE
Dim INC%
INC = 1
Range("C8").Value = 500 ' Stress
Range("B1").Value = 100 ' initial press
Do While Range("C8").Value - Range("C3").Value > 0
Range("B1").Value = Range("B1").Value + INC
Loop
End Sub