Entering Formula into a Range 2

[truitt20]

when i use the following:

Range("I9").Value = "=(G9+H9)/2"
Range("I11").Value = "=(G11+H11)/2"
Range("I20").Value = "=(G20+H20)/2"
Range("I22").Value = "=(G22+H22)/2"
Range("I25").Value = "=(G25+H25)/2"

Range("I6").Value = "=((G6+H6)/2)-I$9"
Range("I7").Value = "=((G7+H7)/2)-I$9"
Range("I13").Value = "=((G13+H13)/2)-I$20"
Range("I14").Value = "=((G14+H14)/2)-I$20"
Range("I15").Value = "=((G15+H15)/2)-I$20"
Range("I16").Value = "=((G16+H16)/2)-I$20"
Range("I17").Value = "=((G17+H17)/2)-I$20"
Range("I18").Value = "=((G18+H18)/2)-I$20"

Only the formula shows up into the cell - not the actual value. Any idea why?

thanks

 

[Zathras]

Actually, it is precisely the "value" that is showing up in the cells.

Instead, try using Range("xx").Formula = "=....."

Although, interestingly enough, .Value works for me just as well using Office 2003 SP2. What version of Excel are you using?

 

[Shawn12]

You have to use this for Formulas:

Range("I9").Formula = "=$A$4+$A$10"

instead of .Value

 

[truitt20]

Tried the *.Formula and this still produced just the formula in the cell value. I am running on Office 2003(11.8033.8036) SP2.

 

[truitt20]

A-HA - all of the numbers are formatted as text.

 

[Shawn12]

That means you got it?

 

[truitt20]

Ya - got it. Just wrote another piece to convert all figures to "Numbers"!

thanks for the help guys & gals!

 

[pbrodsky]

When you are doing something like this:
Range("I9").Value = "=(G9+H9)/2"
Range("I11").Value = "=(G11+H11)/2"
Range("I20").Value = "=(G20+H20)/2"
Range("I22").Value = "=(G22+H22)/2"
Range("I25").Value = "=(G25+H25)/2"
it is a lot easier if you use something like:

range("I9:I25").formulaR1C1="=(RC[-2]+RC[-1])/2"

if you use R1C1 notation you can keep athe formula a constant rather than needing to change it every row.

 

[Gavona]

Great tip Pbrodsky, thanks.


Gavin

 

[xlbo]

pbrodsky - you can do that with A1 style notation as well:

Range("B2:B100").formula = "=A2*100"

will result in:

=A2*100
=A3*100
=A4*100

etc etc etc

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[pbrodsky]

Thanks xlbo - I had missed that.