Divide by variable

D

DavidCotton

Ok, maybe I am not googling correctly (and by no means am I a VBA
guru) but I need help with what seems like a very basic problem.. I
am trying to pass a variable into a formula and it is interpreting it
as text, not the variable value.. See below for relevent code...

DIM VAR1 as integer

ActiveCell.FormulaR1C1 = "=(RC[-31]+RC[-27])/VAR1

VAR1 is being incremented in a loop and I have verified it has a value
but instead of dividing by 3 for instance it is trying to divide by
VAR1.

Can someone hold my hand and let me know what rookie mistake I am
making???
 
R

Rick Rothstein \(MVP - VB\)

Ok, maybe I am not googling correctly (and by no means am I a VBA
guru) but I need help with what seems like a very basic problem.. I
am trying to pass a variable into a formula and it is interpreting it
as text, not the variable value.. See below for relevent code...

DIM VAR1 as integer

ActiveCell.FormulaR1C1 = "=(RC[-31]+RC[-27])/VAR1

VAR1 is being incremented in a loop and I have verified it has a value
but instead of dividing by 3 for instance it is trying to divide by
VAR1.
Click to expand...

You didn't show your closing quote mark, but I am assuming it is at the end
of the string of text. That means you are not sending in the contents of
VAR1, but rather the text string "VAR1". Once VAR1 is passed that way, there
is no way to get to the value it **had** when your macro was running. Try it
this way, so that the contents of VAR1 are passed instead.

ActiveCell.FormulaR1C1 = "=(RC[-31]+RC[-27])/" & CStr(VAR1)

Rick
 
D

Dave Peterson

ActiveCell.FormulaR1C1 = "=(RC[-31]+RC[-27])/" & Var1

It's better to copy from the original code and paste into your post. Less
chance that typos arise in the posting that don't exist in the real code.

Ok, maybe I am not googling correctly (and by no means am I a VBA
guru) but I need help with what seems like a very basic problem.. I
am trying to pass a variable into a formula and it is interpreting it
as text, not the variable value.. See below for relevent code...

DIM VAR1 as integer

ActiveCell.FormulaR1C1 = "=(RC[-31]+RC[-27])/VAR1

VAR1 is being incremented in a loop and I have verified it has a value
but instead of dividing by 3 for instance it is trying to divide by
VAR1.

Can someone hold my hand and let me know what rookie mistake I am
making???
Click to expand...
 
D

DavidCotton

Just wanted to say thanks to both of you.. Worked like a treat.. I
have one more question for the moment, but that is another post
topic ;)

Thanks,

David
 
Top