cells.formula

[lezawang]

Hi
Why I need cells.formula when I can do it without cells.formula as following:

Sub cell_formula()
    Workbooks(1).Worksheets(1).Cells(1, 1).Value = "=sum(c1+c2)"
End Sub

Thank you so much.

 

[pgc01]

Re: cells.fromula

... Why I need cells.formula when I can do it without cells.formula ...

Hi

You need it to read the formula.

 

[DanteAmor]

Re: cells.fromula

Hi
Why I need cells.formula when I can do it without cells.formula as following:

Sub cell_formula()
    Workbooks(1).Worksheets(1).Cells(1, 1).Value = "=sum(c1+c2)"
End Sub

Thank you so much.

Excel interprets it as a formula, it can be:

Workbooks(1).Worksheets(1).Cells(1, 1).Formula = "=sum(c1+c2)"
Workbooks(1).Worksheets(1).Cells(1, 1).Value = "=sum(c1+c2)"
'Or
Workbooks(1).Worksheets(1).Cells(1, 1) = "=sum(c1+c2)"    'The default property of range is value

But can not be

Range("A1").FormulaR1C1  = "=sum(c1+c2)" 'Excel could interpret different

 

[MARK858]

@lezawang, as an example of what pgc01 stated see the link below from John Walkenbach

http://spreadsheetpage.com/index.php/tip/the_value_formula_and_text_properties/

 

[MARK858]

As an example (as I have requested the link be removed from the previous post as a possible issue with the site).

Put the below in a spreadsheet

Excel 2010
	A	B	C	D
1				910.63
2			125.65
3			784.98
Sheet3

Cell Formulas
Range	Formula
D1	=SUM(C2:C3)

then run the code below and see the result in the Immediate window

Sub TestFormVal()
Debug.Print Range("D1").Value
Debug.Print Range("D1").Formula
End Sub

 

[Fluff]

@MARRK858
I'm not getting any problems or warnings regarding John's site, so have left the link.

 

[shg]

Re: cells.fromula

Hi

You need it to read the formula.

Can you clarify, pgc? This does exactly what I'd expect

  With Range("A2")
    .Value = "=sum(b2,c2)"
    Debug.Print .Formula
  End With

 

[Rick Rothstein]

Re: cells.fromula

Can you clarify, pgc? This does exactly what I'd expect

  With Range("A2")
    .Value = "=sum(b2,c2)"
    [B][COLOR="#FF0000"]Debug.Print .Formula[/COLOR][/B]
  End With

I think that is what pgc was saying... if you want to read (back) the formula that is in the cell, you need to use the Format property to get the text representation of the formula.

 

[shg]

Ah, thank you.

 

[pgc01]

Hi

Sorry, I guess I was not very clear.

lezawang asked why do we need the .Formula property in the Range object.
It seemed in the example in post #1 that the .Value was enough for writing both values and formulas.

What I answered is that, in fact, you don't need the .Formula property to write a formula (you can use .Value), but you need it to read the formula back.

Writing with .Value and .Formula is equivalent.
Reading with .Value and .Formula is not equivalent. The latter allows you to get the formula in the cell while the former reads the cell value (the result of the evaluation of the formula).

You need the .Formula property to read back the formula in the cell.

Hope it's clear.