Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Combine logical formulas "if", "and", "or" | Excel Discussion (Misc queries) | |||
Combining formulas, "and" & "or" to verify content of multiple cel | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |