Excel VBA-use variable in active cell formula problem

[waveracerr]

Seems simple enough but I cannot find any help on the matter...

I cannot get the required syntax to allow me to use a variable, "i",
within the formula, "=RC[-1]&""/""&RC[&i]"

Sub Macro1()
Dim i As Integer
i = 2

Range("D2").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]&""/""&RC[&i]"
ActiveCell.Offset(0, 1).Select
i = -2 + i
Loop
End Sub

Column headings exist in every other column along row 2. I want the
formula to pick up the heading in C2 every time, add a division sign
and then add the heading from the preceding column.

For example if C2's heading reads "Animal", D2's heading reads "Plant"
and F2's heading reads "Fungi". In this example I want the formula to
print "Animal/Plant" in E2 and "Animal/Fungi" in G2.

Thanks for any help!

Ryan

 

[Frank Kabel]

Hi
try
ActiveCell.FormulaR1C1 = "=RC[-1]" & chr(38) & chr(34) & "/" & chr (34)
& chr(38) & "RC[" & i & "]"

Frank

 

[waveracerr]

I would have never "guessed" that answer! Thanks so much

 

[Frank Kabel]

Hi
thanks for the thanks
Frank

 

[Tom Ogilvy]

Another approach: (demo'd in the immediate window)

i = 10
? "=RC[-1]/" & "RC[" & i & "]"
=RC[-1]/RC[10]

 

[Frank Kabel]

Another approach: (demo'd in the immediate window)

i = 10
? "=RC[-1]/" & "RC[" & i & "]"
=RC[-1]/RC[10]

Hi Tom
I think this is a different result formt what the OP wants to achieve.
Your formula would result in a value (the division result). But if I
understood the OP correctly he wants a text consisting of both values
operands and the '/' as text delimiter. So he would see something like
"3/4" in his target cell

Frank

 

[Tom Ogilvy]

i = 10
? "=RC[-1]&""/""" & "&RC[" & i & "]"
=RC[-1]&"/"&RC[10]

Regards,
Tom Ogilvy

Another approach: (demo'd in the immediate window)

i = 10
? "=RC[-1]/" & "RC[" & i & "]"
=RC[-1]/RC[10]

Hi Tom
I think this is a different result formt what the OP wants to achieve.
Your formula would result in a value (the division result). But if I
understood the OP correctly he wants a text consisting of both values
operands and the '/' as text delimiter. So he would see something like
"3/4" in his target cell

Frank

 

[Tom Ogilvy]

or for comparison:

? "=RC[-1]&""/""" & "&RC[" & i & "]"
=RC[-1]&"/"&RC[10]
? "=RC[-1]" & chr(38) & chr(34) & "/" & chr (34) & chr(38) & "RC[" & i & "]"
=RC[-1]&"/"&RC[10]


--
Regards,
Tom Ogilvy

Another approach: (demo'd in the immediate window)

i = 10
? "=RC[-1]/" & "RC[" & i & "]"
=RC[-1]/RC[10]

Hi Tom
I think this is a different result formt what the OP wants to achieve.
Your formula would result in a value (the division result). But if I
understood the OP correctly he wants a text consisting of both values
operands and the '/' as text delimiter. So he would see something like
"3/4" in his target cell

Frank

 

[Frank Kabel]

or for comparison:

? "=RC[-1]&""/""" & "&RC[" & i & "]"
=RC[-1]&"/"&RC[10]
? "=RC[-1]" & chr(38) & chr(34) & "/" & chr (34) & chr(38) & "RC[" &
i & "]" =RC[-1]&"/"&RC[10]

Hi Tom
I lost the comparison
best regards
Frank

 

[Tom Ogilvy]

The comparison was intended to show both approaches produced the same
result.

I was just adding some other techniques to deal with building strings. So
no one can lose. <g>

--
Regards,
Tom Ogilvy

or for comparison:

? "=RC[-1]&""/""" & "&RC[" & i & "]"
=RC[-1]&"/"&RC[10]
? "=RC[-1]" & chr(38) & chr(34) & "/" & chr (34) & chr(38) & "RC[" &
i & "]" =RC[-1]&"/"&RC[10]

Hi Tom
I lost the comparison
best regards
Frank