Here's some sample code that enters the formula =left(a2,5) into cell B2 and drags it down to the last record in the recordset.
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],5)"
Dim rng As Range
Dim rngData As Range
Dim rngFormula As Range
Dim rowData As Long
Dim colData As Long
Set rng = ActiveCell
Set rngData = rng.CurrentRegion
rowData = rngData.CurrentRegion.Rows.Count
colData = rng.Column
Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
To get your formula in VBA format to insert in the code above, click to record macros, enter your formula into the table, hit enter then stop recording the macro. View your macro and copy the formula it comes up with. When I did for yours I came up with somethign like "=LEFT(R[-1]C[-2],SUM(MATCH(TRUE,ISNUMBER(1*MID(R[-1]C[-2],ROW(R[-1]:R),1)),0),COUNT(1*MID(R[-1]C[-2],ROW(R[-1]:R),1)))-1)"