USING THE "&" IN VBA FORMULAS !
 

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

USING THE "&" IN VBA FORMULAS !
 

& 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)"





In article ,
jay dean wrote:

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.

USING THE "&" IN VBA FORMULAS !
 

& 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" wrote in message
...
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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

USING THE "&" IN VBA FORMULAS !
 

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

USING THE "&" IN VBA FORMULAS !
 

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)"


In article ,
jay dean wrote:

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.

USING THE "&" IN VBA FORMULAS !
 

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.


In article ,
jay dean wrote:

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?

USING THE "&" IN VBA FORMULAS !
 

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" wrote in message
...

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

USING THE "&" IN VBA FORMULAS !
 

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

Jay Dean


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!