USING THE "&" IN VBA FORMULAS !

[jay dean]

Could somebody please use simple examples to show me how to use the
ampersand "&" correctly in formulas?
I am sorry, I don't understand how the ampersand is used in especially
dynamic formulas. Eg. Range(A1).formula="SUM("&....")
Please *explain* each step of your examples for me to understand.
Thanks.

Jay Dean

 

[JE McGimpsey]

& is the concatenation operator - it joins strings. So for

Range("A1").Formula = "=SUM(" & rMyRange.Address(0, 0) & ")"


if rMyRange was set to, say, J1:K100, then rMyRange.Address(0, 0)
returns the string

J1:K100

which & stitches together with the two string "=SUM(" and ")", and the
whole thing would be equivalent to a hard-coded

Range("A1").Formula = "=SUM(J1:K100)"

 

[Bob Phillips]

& is used to concatenate two values, ,like

"abc" & "xyz"

Where have you seen it that prompts the question?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[jay dean]

Hi JE (and Bob Phillips)-
Your example is an example of *exactly* what I was referring to. I
understand the concatenation part.
Question1:
From Range("A1").Formula = "=SUM(" & rMyRange.Address(0, 0) & ")".
Suppose rMyrange was initially set to J1:K100 as you say.
So what exactly is the first ampersand (&) in the formula
concatenating?
What exactly is the second '&' concatenating?
These are what confuse me regarding the use of the ampersand in
formulas. Please break it (the explanation) down for me.

Question2:
If for each cell in Range C2:C200, I would like to input a formula
like cell.formula=DIVIDE cell.offset(0,-1).value by
cell.offset(0,-2).value (ie for each cell in C2:C200 enter formula to
divide the cell one column to the left by the cell two columns to the
left, how do I do that?

Thanks in advance for all your help.
Jay Dean

 

[JE McGimpsey]

Breaking

(1) Range("A1").Formula = "=SUM(" & rMyRange.Address(0, 0) & ")"

down, the right-hand argument of the assignment operator ("=") is
evaluated before assignment to the .Formula property of the range
Range("A1"). "&" operates on strings only (non-string arguments need to
be coerced, either explicitly - e.g., CStr(x) - or implicitly). Since
"=SUM(" and ")" are elemental strings, but

rMyRange.Address(0, 0)

is not, the latter needs to be evaluated first. Evaluation of the
address property returns the string

(2) "J1:K100"

The first "&" concatenates the string "=SUM(" and (2) to return the
string:

(3) "=SUM(J1:K100"

The second "&" concatenates the result in (3) with ")" to return the
string:

(4) "=SUM(J1:K100)"

So after both concatenations, the assignment in (1) is equivalent to:

(5) Range("A1").Formula = "=SUM(J1:K100)"

 

[JE McGimpsey]

One way:

It's most similar to your example if we use the FormulaR1C1 property:

Range("C2:C200").FormulaR1C1 = "=RC[-2]/RC[-1]"

but you can also use A1 references

Range("C2:C200").Formula = "=A2/B2"

XL adjusts the A1 reference appropriately.

 

[Bob Phillips]

The formula property is a string. You must assign a properly formed string.

"=SUM(" & rMyRange.Address(0, 0) ")"

would not be a properly formed string, and would throw a syntax error. You
have three separate parts, a text statement defining the function, a range
string being passed as a variable, and a function end. Three parts need 2
joiners, hence 2 &

"=SUM(" & rMyRange.Address(0, 0) & ")"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[jay dean]

Aah! It's now crystal clear !!
Thank you very much JE...This is great!
Thanks Bob also for your help!

Jay Dean