Assigning to a variable a value from an Excel cell

[PMarinshaw]

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.

 

[bltseattle]

I'm not sure but I think you have a type mismatch as TEST is an integer but CALC is an object.

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

 

[bltseattle]

A few more thoughts and tips from when I addressed a similar problem...
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://www.krellinst.org/UCES/archive/resources/roots/node2.html

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.

 

[PMarinshaw]

Thanks. It was a combination of both of your suggestions which straightened me up.

 

[cummings54]

Dim INC, PRESS, TEST, STRESS As Integer

should be replaced with

Dim INC, As Integer
Dim PRESS As Integer
Dim TEST As Integer
Dim STRESS As Integer

in your version you create three variant and one integer

 

[joerd]

I'm not sure if I understand your code, but one thing is that in VBA you don't have to select a cell and then modify its contents, like you do in Excel, and what gets recorded when you use the Macro recorder. So, instead of

Range("B1").Select
ActiveCell.FormulaR1C1 = "99"

you may write:

Range("B1").FormulaR1C1 = "99"

This saves a lot of screen updating since excel doesn't have to move the cell pointer around. Another statement that might be of use here, at the beginning of your code, is [tt]Application.ScreenUpdating=False[/tt]. This speeds things up a little, especially if you do 10000 iterations.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[yakpol]

PMarinshaw,

Just a shorter version of your intent

Sub Macro2()
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