adding formula to vba code

[Guest]

I am trying to use vba to automate some stuff. Part of it includes
autocopying a formula in a row to a row which is identified by a number
calculated in cell AC18.
Currently the only reason for AC18 is for that calculation. I would like to
delete it and add it to the code. However I am not sure how to do this.
Do I need to make a dim statement? Does it need to have a name according to
some vba rules or can I make up a name?

Currently the AC18 formula is =counta(k:k)

Any help would be greatly appreciated.
TIA

 

[Ron de Bruin]

Try something like this


Sub test()
Dim rw As Long

With Sheets("Sheet1")
rw = Application.WorksheetFunction.CountA(.Columns("K:K"))
' copy the formula in B1 to B?
..Range("B1").Copy .Cells(rw, "B")
End With

End Sub