Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to use the active cell in an Excel formula | Excel Worksheet Functions | |||
Set a variable to the active cell | Excel Discussion (Misc queries) | |||
referring to formula in a non active cell from active cell | Excel Discussion (Misc queries) | |||
value of active cell into a variable | Excel Programming | |||
Problem with Active Cell Refence | Excel Programming |