Need Macro VB Command to copy formula to blank cell

[Linda Berardo]

I need to start in a given cell and enter a formula, then copy that formula to the next cell up if it is blank. For example, I'll start in cell N33 and want to enter the formula N33-L33, then move to the next cell up N32 and enter the formula N32-L32. I want to continue this until the cell above is filled (which is my header). The starting cell will vary worksheet to worksheet, but I'll always place the cursor in the "starting blank cell", be it N33 or N100 or N12. Once I have the cursor in the starting cell, I'll run the macro.

 

[jim may]

starting in cell N33 and want to enter the formula =N33-L33 Causes a Circular Reference; Come Back,,,,

 

[Andrew Poulsom]

If you enter the formula N33-L33 in N33 you will create a circular reference. This shows you how to enter the formula M33-L33:

Sub Test()
    Dim FirstBlankRow As Long
    With ActiveCell
        If .Offset(-1, 0) <> "" Then
            FirstBlankRow = .Row
        Else
            FirstBlankRow = .End(xlUp).Offset(1).Row
        End If
        Range(Cells(.Row, .Column), Cells(FirstBlankRow, .Column)).FormulaR1C1 = "=RC[-1]-RC[-2]"
    End With
End Sub

 

[Linda Berardo]

OK; I made an error in my original post. I'll start in cell O33 and want the formula to be N33-L33 and move up to O32 and want that to be N32-L32 and so on until the cell up is not blank (my header row). Sorry for not thinking through my cells before my original post.

 

[jim may]

Here's another way

Sub Foo()
Nbr = Range("N" & Rows.Count).End(xlUp).Row + 1
Set Rng = Range(Range("N" & Nbr), ActiveCell)
Rng.Cells.FormulaR1C1 = "=RC[-1]-RC[-2]"
End Sub

 

[Linda Berardo]

Thank you. I was able to modify answer to fit my needs. I'll be posting a new challenge.

 

[VoG]

Re: Need Macro to copy formula even if starting cell different

Assuming that the cell you want to copy is the selected cell try

Sub test()
With ActiveCell
    .Copy .Offset(, 2)
    .Copy .Offset(, 4)
End With
End Sub

 

[Linda Berardo]

Re: Need Macro to copy formula even if starting cell different

VoG, thank you! That worked perfectly. I'm obviously new to this and appreciate your help.