Copying down a formula in a variable length range?

[parodytx]

I have a spreadsheet that builds from existing data. The number of rows may vary depending on the data required, but all rows are filled and consecutive.

I need to copy down a formula in a column from the top cell, but I can't get the logic to work in getting the Range().Select or ActiveCell.Offset() to work.

So, for clarity, I have columns A & B that have data say from cells A1:B20, and I need to copy the Formula +A1 + B1 to Cell C1, then copy it down for as long as there are rows with data in columns A & B, without overtly looping to a fixed count of 20.

All help is much appreciated.

Excel 2010 Windows 7 32 Bit

 

[jolivanes]

Like this you mean?

Sub Maybe()
    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    With Range("A1:A" & lr).Offset(, 2)
        .Formula = "=RC[-2]+RC[-1]"
    End With
End Sub

 

[kevin lazell]

or another method

Sub plus()
Dim a, b As Variant
Do
a = a + 1
b = Range("a" & Rows.Count).End(xlUp).Row
Range("c" & a) = Application.WorksheetFunction.Sum(Range("a" & a) + Range("b" & a))
If a = b Then Exit Sub
Loop
End Sub

 

[parodytx]

or another method

Sub plus()
Dim a, b As Variant
Do
a = a + 1
b = Range("a" & Rows.Count).End(xlUp).Row
Range("c" & a) = Application.WorksheetFunction.Sum(Range("a" & a) + Range("b" & a))
If a = b Then Exit Sub
Loop
End Sub

Thanks so much - this did not work exactly for me (My formula example was too simplistic - my fault) but it got me to see the place I needed to be in my logic. Thanks again.