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

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

Hi,
In the equation you have below G12 and B13 are not absolute, but we don't
know where this equation is being entered. You can record this in a macro and
have your answer. Go to the cell where your equation is located, start your
macrorecorder and press F2, then hit enter and finally turn off the recorder.
Hope that helps.

Thanks,


"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

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

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

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

Thank you for the help!

"keepITcool" wrote:


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