pasting formulae to entire column in macro

[Colin Vicary]

Apologies to all if this is really easy! I'm a fairly new user who ha
been thrown in at the deep end.

I am trying to create a macro in which I will add a new column, write
formula in one cell and then copy that formula to every cell in tha
column.

The spreadsheet in which the macro runs varies in the number of lines
All I seem to be able to do is paste the formula to all 65,000 cells o
to a fixed amount of cells.

Can anyone help please?

Thanks

Coli

 

[Frank Kabel]

Hi
use something like the following
Sub insert_formulas()
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
with activesheet.range("B1:B" & lastrow)
.formulaR1C1="=R[0]C[5]*2"
end with
End Sub

 

[Colin Vicary]

Frank

That worked perfectly, thank you. It's made my life so much easier!

Thanks again

Coli