×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Assigning to a variable a value from an Excel cell

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.

RE: Assigning to a variable a value from an Excel cell

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

RE: Assigning to a variable a value from an Excel cell

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.

RE: Assigning to a variable a value from an Excel cell

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

RE: Assigning to a variable a value from an Excel cell

CODE

Dim INC, PRESS, TEST, STRESS As Integer
should be replaced with

CODE

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

RE: Assigning to a variable a value from an Excel cell

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

CODE

Range("B1").Select
ActiveCell.FormulaR1C1 = "99"
you may write:

CODE

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 Application.ScreenUpdating=False. 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.

RE: Assigning to a variable a value from an Excel cell

PMarinshaw,

Just a shorter version of your intent

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources