Variables causing macro problems

[Aaron]

I'm fairly new to writing macros, so this may be easy. I have a macro
with defined variables from the excel worksheet. The variable changes
as the macro is run. For instance, when the macro begins the variable
may equal 1, but halfway through the macro (after actions have been
performed) the variable now equals 5. In the middle of my macro
(after the cell value has already changed to 5) I want the variable to
be read as 5, not the original 1, because the next portion of actions
is different based on the number of that variable. This does not
happen. Apparently Excel reads all variables before it runs the
macro, and does not change them during the run.

Does anyone know what I could do differently or use as a work-around?
Thanks in advance.

 

[Don Guillett]

Not necessarily.

x=variable
do stuff
x=variable
do more stuff

 

[Earl Kiosterud]

Aaron,

Don may have answered your question already.

You set the value of a variable whenever you want:

Dim X as Integer

X=10
Do stuff
X=15
Do more stuff

If this doesn't get it, post your code.

 

[Aaron]

Thanks for the help, but maybe I didn't explain it right. Here is a
truncated version of the code that may explain it better (don't laugh,
most of what I've learned is from recording macros and going from
there):

Sub()
var1 = sheets("data").range("a1").value
'before the macro is run, cell a1 is blank
range("d1").select
'cell d1 may or may not have a value in it
selection.copy
range("a1").select
selection.paste
'now cell a1 has a number in it, for this is example we'll use 5
If var1 = "" Then GoTo stopmacro:
If var1 = 1 Then GoTo 1:
'....(and so on)
If var1 = 5 Then GoTo 5:

1:
do stuff
GoTo stopmacro:

'....(and so on)

5:
do stuff
GoTo stopmacro:

stopmacro:
End Sub

My problem, is that when I run the macro, it recognizes the cell as ""
because there is no data in it before the macro is run. However, you
see the macro puts a number there. I would hope the macro would
recognize the new number (in this case 5) and do stuff accordingly.
Maybe that helps in explaining it better. Thanks again for the help -
much appreciated.

Aaron

 

[Earl Kiosterud]

Aaron,

Var1 is set to what WAS A1 (blank), then A1 is set to what's in D1. But
that doesn't change Var1. So it's still blank, even though cell A1 no
longer is. Setting Var1 = Range("A1") doesn't establish any kind of link.

Perhaps you need to test A1 in your IFs directly.

 

[Don Guillett]

A bit incomplete in that you don't say where this happens.
1. sub gottahaveaname()
2. you do not have to select cells

range("d1").select
'cell d1 may or may not have a value in it
selection.copy
range("a1").select
selection.paste

can be
range("a1").value=range("d1")
=====
You might like the use of Select instead of your IF's

Sub SelectVariable()
var1 = 2
Select Case var1
Case 1
MsgBox "1"
End
Case 2
MsgBox "2"
Case Else
End
End Select
End Sub

Sub dd()

var1 = sheets("data").range("a1").value
'before the macro is run, cell a1 is blank
range("d1").select
'cell d1 may or may not have a value in it
selection.copy
range("a1").select
selection.paste
'now cell a1 has a number in it, for this is example we'll use 5
If var1 = "" Then GoTo stopmacro:
If var1 = 1 Then GoTo 1:
'....(and so on)
If var1 = 5 Then GoTo 5:

1:
do stuff
GoTo stopmacro:

'....(and so on)

5:
do stuff
GoTo stopmacro:

stopmacro:
End Sub