Using active cell in a formula
Posted by chrisB on December 04, 2001 3:42 PM
I have a macro where I autofilter a column, and then use a loop (thanks to Olaf) to make the first cell in the filtered column active. I then enter a formula in that cell that I want to copy/fill down. Since the active cell can change from day to day depending on the results of the autofilter, I don't know how to name that cell in my copy formula.
I think this board is great - Thanks!
Posted by JAF on December 05, 2001 7:19 AM
Assuming the activecell is C2, then the following formula would return "=A2+B2" as the formula.
The (False, False) section of the code sets the RowAbsolute and ColumnAbsolute to False. If these were excluded, you'd get "=$A$2+$B$2" instead.
Hope this helps.
JAF
Sub reference_address_in_formula()
ActiveCell.Formula = "=" & ActiveCell.Offset(0, -2).Address(False, False) & "+" & ActiveCell.Offset(0, -1).Address(False, False)
End Sub
Posted by chrisB on December 06, 2001 9:15 AM
Thanks JAF, but I think I the question I asked wasn't worded well. I have the active cell formula already in place - what I really need to know is how to name that cell in the copy command. Here is an example of the code:
ActiveCell.FormulaR1C1 = "=RC[-1]"
'THIS NEXT LINE IS WHAT I CAN'T FIGURE OUT!
Range("ActiveCell:" & "U:" & LastRow).Select
With Selection
.FillDown
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
THANKS!
Assuming the activecell is C2, then the following formula would return "=A2+B2" as the formula.
