Excel VBA-use variable in active cell formula problem
 

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


---
Message posted from http://www.ExcelForum.com/

Excel VBA-use variable in active cell formula problem
 

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

Frank




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


---
Message posted from http://www.ExcelForum.com/

Excel VBA-use variable in active cell formula problem
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA-use variable in active cell formula problem
 

Hi
thanks for the thanks
Frank
I would have never "guessed" that answer! Thanks so much!


---
Message posted from http://www.ExcelForum.com/

Excel VBA-use variable in active cell formula problem
 

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

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


--
Regards,
Tom Ogilvy

"waveracerr " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

Excel VBA-use variable in active cell formula problem
 

Tom Ogilvy wrote:
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

Excel VBA-use variable in active cell formula problem
 

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

Regards,
Tom Ogilvy


"Frank Kabel" wrote in message
...
Tom Ogilvy wrote:
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

Excel VBA-use variable in active cell formula problem
 

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

"Frank Kabel" wrote in message
...
Tom Ogilvy wrote:
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

Excel VBA-use variable in active cell formula problem
 

Tom Ogilvy wrote:
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

Excel VBA-use variable in active cell formula problem
 

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

"Frank Kabel" wrote in message
...
Tom Ogilvy wrote:
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