| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use of "formula"
I am bit confused while using the method "formula"
please see the code statements and result in running each of the code independently =============== Range("c1") = Range("a1") * Range("b1") '----ok range("c1").Formula="=range("a1")*range("b1")"----results in syntax error exected end of statement at a1 Range("c1").Formula = "=$A$1*$B$1" - -------ok Range("c1").Formula = "=cells(1,1)*cells(1,2)"---returns#NAME# Range("c1").Formula = "=cells(0,[-1])*cells(0,[-2])"---appln defined orobject defined error Range("c1").FormulaR1C1 = "=rc1*rc2" - --------ok ActiveCell = ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1) - ---ok ActiveCell.Formula = "=ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1)"--returns #NAME# =============== request for some logical ideas in using "formula" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
use of "formula"
Range("c1") = Range("a1") * Range("b1") '----ok
Uses default property .value - ok range("c1").Formula="=range("a1")*range("b1")"----results in syntax error You're embedding Range within a string, but not that well because " within a string should be "" Range("c1").Formula = "=$A$1*$B$1" - -------ok Standard formula write - ok Range("c1").Formula = "=cells(1,1)*cells(1,2)"---returns#NAME# It's looking at Cells( and expects that to be a function that Excel understands. Any time you attempt to use a function that Excel doesn't know about, it returns #NAME Range("c1").Formula = "=cells(0,[-1])*cells(0,[-2])"---appln defined square brackets mean something special, like a sheet name if I recall - it wont find a sheet named -1 or -2 Range("c1").FormulaR1C1 = "=rc1*rc2" - --------ok Standard formula write - ok ActiveCell = ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1) - ---ok I'm surprised this works. I thought the syntax was Set ActiveCell = ActiveCell .... Probably what's happening is a local variable called ActiveCell is being defined for storing result of your equation. (0, -2) * (0, -1) I doubt this is working as you expect. Try placing Option Explicit at the top of your code and re-run it. ActiveCell.Formula = "=ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1)"--returns #NAME# Same issue with #NAME as above. Excel is looking for a function called ActiveCell.Offset( and doesn't find it. You seem to be confused where VBA stops and Excel starts. ..Formula is a property, not a Method When you try to write to the Formula property, write a string. So your goal should be to compile a string. Examples: ActiveCell.Formula = "=" & ActiveCell.Offset(0, -2).Address & " * " & ActiveCell.Offset(0, -1).Address Range("c1").Formula = "=" & Cells(0, -1).Address & " * " & Cells(0, -2).Address Range("c1").Formula = "=" & Range("a1").Address(False, False) & "*" & Range("b1").Address(False, False) -- Rob van Gelder - http://www.vangelder.co.nz/excel "R.VENKATARAMAN" &&& wrote in message ... I am bit confused while using the method "formula" please see the code statements and result in running each of the code independently =============== Range("c1") = Range("a1") * Range("b1") '----ok range("c1").Formula="=range("a1")*range("b1")"----results in syntax error exected end of statement at a1 Range("c1").Formula = "=$A$1*$B$1" - -------ok Range("c1").Formula = "=cells(1,1)*cells(1,2)"---returns#NAME# Range("c1").Formula = "=cells(0,[-1])*cells(0,[-2])"---appln defined orobject defined error Range("c1").FormulaR1C1 = "=rc1*rc2" - --------ok ActiveCell = ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1) - ---ok ActiveCell.Formula = "=ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1)"--returns #NAME# =============== request for some logical ideas in using "formula" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
use of "formula"
please see the code statements and result in running each of the code
independently =============== range("c1").Formula="=range("a1")*range("b1")"----results in synta error use the following. This should work:- Cells(3, 1).Formula = "=" & Cells(1, 1).Address & "*" & Cells(2 1).Address You may want to work with the row and column indices rather than wit A1 notation when working with VBA rather than frontend worksheets. I is easier that way. Goodluck Deepak Agarwa -- Message posted from http://www.ExcelForum.com |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
use of "formula"
thanks . I shall study your remakrs carefully.
one more doubt . in your last example why that <false,false. clarification would be helpful sorry for the slip between property and method. Rob van Gelder wrote in message ... Range("c1") = Range("a1") * Range("b1") '----ok Uses default property .value - ok range("c1").Formula="=range("a1")*range("b1")"----results in syntax error You're embedding Range within a string, but not that well because " within a string should be "" Range("c1").Formula = "=$A$1*$B$1" - -------ok Standard formula write - ok Range("c1").Formula = "=cells(1,1)*cells(1,2)"---returns#NAME# It's looking at Cells( and expects that to be a function that Excel understands. Any time you attempt to use a function that Excel doesn't know about, it returns #NAME Range("c1").Formula = "=cells(0,[-1])*cells(0,[-2])"---appln defined square brackets mean something special, like a sheet name if I recall - it wont find a sheet named -1 or -2 Range("c1").FormulaR1C1 = "=rc1*rc2" - --------ok Standard formula write - ok ActiveCell = ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1) - ---ok I'm surprised this works. I thought the syntax was Set ActiveCell = ActiveCell .... Probably what's happening is a local variable called ActiveCell is being defined for storing result of your equation. (0, -2) * (0, -1) I doubt this is working as you expect. Try placing Option Explicit at the top of your code and re-run it. ActiveCell.Formula = "=ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1)"--returns #NAME# Same issue with #NAME as above. Excel is looking for a function called ActiveCell.Offset( and doesn't find it. You seem to be confused where VBA stops and Excel starts. .Formula is a property, not a Method When you try to write to the Formula property, write a string. So your goal should be to compile a string. Examples: ActiveCell.Formula = "=" & ActiveCell.Offset(0, -2).Address & " * " & ActiveCell.Offset(0, -1).Address Range("c1").Formula = "=" & Cells(0, -1).Address & " * " & Cells(0, -2).Address Range("c1").Formula = "=" & Range("a1").Address(False, False) & "*" & Range("b1").Address(False, False) -- Rob van Gelder - http://www.vangelder.co.nz/excel "R.VENKATARAMAN" &&& wrote in message ... I am bit confused while using the method "formula" please see the code statements and result in running each of the code independently =============== Range("c1") = Range("a1") * Range("b1") '----ok range("c1").Formula="=range("a1")*range("b1")"----results in syntax error exected end of statement at a1 Range("c1").Formula = "=$A$1*$B$1" - -------ok Range("c1").Formula = "=cells(1,1)*cells(1,2)"---returns#NAME# Range("c1").Formula = "=cells(0,[-1])*cells(0,[-2])"---appln defined orobject defined error Range("c1").FormulaR1C1 = "=rc1*rc2" - --------ok ActiveCell = ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1) - ---ok ActiveCell.Formula = "=ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1)"--returns #NAME# =============== request for some logical ideas in using "formula" |
| 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) | |||
| any formula to convert numbers in word form, e.g. "2" as "Two"? | Excel Worksheet Functions | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Linear Mode
