use of "formula"

[R.VENKATARAMAN]

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"

 

[Rob van Gelder]

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

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"

 

[agarwaldvk]

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

 

[R.VENKATARAMAN]

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.

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

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"