Auto fill formula from first row in a column to the last active row in the same column?

[robdav]

I'm trying to right a macro that will insert a formula into cell G2 then auto fill it down to the last active row in column G.

This is my closest attempt but it fails on the 'Selection.Autofill' can anyone help please?

    Range("G2").Select    ActiveCell.FormulaR1C1 = _        "=SUBSTITUTE(LEFT(RC[-1],SEARCH("" "",RC[-1])-1),""."",""/"")+0"    ActiveSheet.Range("G2", ActiveSheet.Range("G2").End(xlDown)).Select    Selection.AutoFill

Many thanks

Robert

 

[Joe4]

So, are you wanting to overwrite what currently is in column G?
If not (because column G is blank), how do you determine where the last active row in G resides (if there is no data in column G)?

 

[robdav]

Hi

The column is a newly inserted column into a sheet that has other data in it. There are 13 other columns all if which have the cells populated to the same last row which obviously changes on a daily basis.

I effectively need the last populated row in the sheet to use in the auto fill for column G.

Does that make sense?

Thanks, Rob

 

[Joe4]

OK, then I will just use column F to find where the last row of data occurs.
We don't actually have to use Autofill. We can apply this formula to the whole range at once, like this:

    Dim lastRow As Long
    
'   Find last row from column F
    lastRow = Cells(Rows.Count, "F").End(xlUp).Row

'   Populate formula
    Range("G2:G" & lastRow).FormulaR1C1 = "=SUBSTITUTE(LEFT(RC[-1],SEARCH("" "",RC[-1])-1),""."",""/"")+0"

 

[robdav]

Perfect, thank you very much for your help.

 

[Joe4]

You are welcome!

Because the formula uses relative references (R1C1), we are able to assign the formula to the whole range at once.