Please read answers (and take the time to understand them)
before asking the same thing again.

The only adaptation I needed to make was to change the assumed [a12]
to [g13] from my earlier answer.

IMO the formula doesnt 'slightly' change, as it can be copied down.
(you've omitted to indicate if maybe the column reference should be
absolute) If you'd compare the formulaR1C1 properties of g14 and g13
you'd find that they are identical.

Range("g13:g2000").formular1c1 = "=R[-1]C+(RC[-5]*R5C[-3]*R8C[-2])/1000"
or if in fact the columns are absolute:
Range("g13:g2000").formular1c1 = "=R[-1]C7+(RC2*R5C4*R8C5)/1000"



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


OkieViking wrote :

Thanks for the responses. I need to expand on my application a
little. I am filling out a table, where this equation will be
entered in G13. I will then add rows to the table below row 13 and
will need to add the equation in a similar fashion to autofill.
However, occasionaly I will slightly change the equation, so autofill
won't work. In cell G13 I want
=G12+(B13*D$5*E$8)/1000
In cell G14 I want
=G13+(B14*D$5*E$8)/1000

I found a way of doing this by searching through other threads.
However, it seems clumsy. Y'all's approach seems a lot better than
my solution, if I can modify it to work. What I did was this:

ActiveCell.Formula = "=" & Cells(11 + i, "g").Address(0, 0) &
"+(" & Cells(12 + i, "b").Address(0, 0) & "*" & Cells(5, "d").Address
& "*" & Cells(8, "e").Address & ")/1000"

where i=1 when activecell is G13. Clumsy looking, and tedious to
program, and it will be a pain to come back to later to debug/change
if I need to.

Is there a better way? (I am sure it is.)

"keepITcool" wrote:

have a look at:

application.ConvertFormula

then maybe following would work?

ActiveCell.FormulaR1C1= _
application.ConvertFormula( _
"=G12+(B13*D$5*E$8)/1000",xla1,xlr1c1,,[a12])

however since the "formula" is hardcoded there's no need to convert
it everytime you run the code.
get the result once in the immediate window by typing
?application.ConvertFormula("=G12+(B13*D$5*E$8)/1000",xla1,xlr1c1,,[
a12] )


and then code:

Activecell.Formular1c1="=RC[6]+(R[1]C[1]*R5C[3]*R8C[4])/1000"




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


OkieViking wrote :

I figured out how to do this using R1C1 nomenclature. But now I
need to enter an equation with absolute and relative references
using VBA. Here is the equation:

=G12+(B13*D$5*E$8)/1000

How would I enter an equation like this?

"OkieViking" wrote:

I want to write a code that inserts an equation, using relative
addresses (i.e. the cell above minus the cell to the left of the
active cell). How do i do that