Copying a formula down through x number of rows

Question

I'm at a loss on this and need some help. I've lurked around at answers and have Frankensteined together some code for a macro but it just isn't working.

Here is part of what I have so far:

With ActiveSheet
Firstrow = 1
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For lrow = Lastrow To Firstrow Step -1
With .Cells(lrow, "G")
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[1]),RC[1],RC[-1])"
End With
Next lrow
End With

I have a very similar block of code before this that deletes crap from the text files I'm importing and it works perfectly through all the number of rows. When I run the same thing with this formula, it only puts the formula in G1 and doesn't cycle through the rest of the sheet. I've tried this and it works, but copies down through all million plus rows:

ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[1]),RC[1],RC[-1])"
Selection.AutoFill Destination:=Range("G:G")

I've tried this and then run the same code that gets rid of the text file crap but I get an error "End If without block If".

Your formula specifically says Range("G1").Select - that's where it selects the cell, and you've hardcoded it to "G1". That being said, I can't tell what you're trying to do - you want every single cell to have that formula in it? Like, 1 million cells? Define what you're trying to do here. — Grade 'Eh' Bacon, Oct 08 '15 at 13:39
Well, I tried to not use Range("G1") but when I left it out it put the formula in column B. What I need for it to do is copy this formula only through however many number of rows there are in the sheet. The second set of code I put in my original post works, but it copies the formula through all million plus rows because I have Destination:=Range("G:G") — cmag79, Oct 08 '15 at 14:14

score 1 · Accepted Answer · edited May 23 '17 at 10:26

To fill the formula in one cell at a time you need to cycle through them; don't keep relying on the ActiveCell property.

With ActiveSheet
    Firstrow = 1
    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For lrow = Lastrow To Firstrow Step -1
        .Cells(lrow, "G").FormulaR1C1 = "=IF(ISNUMBER(RC[1]),RC[1],RC[-1])"
    Next lrow
End With

But you can speed things up by putting the formula into all of the cells at once.

With ActiveSheet
    Firstrow = 1
    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    With .Range(.Cells(Firstrow, "G"), .Cells(Lastrow, "G"))
        .FormulaR1C1 = "=IF(ISNUMBER(RC[1]),RC[1],RC[-1])"
    End With
End With

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

@cmag79 This is a particularly good response because you're already using .FormulaR1C1 - one of the big benefits of which is applying formulas across long ranges, exactly like this. — Grade 'Eh' Bacon, Oct 08 '15 at 14:16

score 0 · Answer 2 · answered Oct 08 '15 at 13:55

Another version, to dynamically select the columns based on their titles. Comments included.

Dim row As Range
Dim cell As Range
Static value As Integer

'Set row numbers

'Find the starting row. Located using Title of column "Start" plus whatever number of rows.
Dim RowStart As Long
Set FindRow = Range("A:A").Find(What:="Start", LookIn:=xlValues)
RowStart = FindRow.row + 1

'End of the range. Located using a "finished" cell
Dim RowFinish As Long
Set FindRow = Range("A:A").Find(What:="Finished", LookIn:=xlValues)
 RowFinish = FindRow.row - 1

'Set range - Goes Cells(Rownumber, Columnnumber)
'Simply ammend RowStart and RowFinish to change which rows you want.
' In your case you need to change the second column number to paste in horizontally.
Set rng = Range(Cells(RowStart, 1), Cells(RowFinish, 1))
'Start the counter from the starting row.
value = RowStart

For Each row In rng.Rows
  For Each cell In row.Cells

'Insert relevant formula into each cell in range.
cell.Formula = _
"=IF(ISNUMBER(RC[1]),RC[1],RC[-1])"

   'Increment row variable.
    value = value + 1
  Next cell
Next row

Copying a formula down through x number of rows

2 Answers2