This will put the Mid formula in column B from row 2 to the last used row in
column A. For example, if you have part numbers in column A from row 2 to
row 45 it will put the formula in B2:B45.

Sub test()

Dim last_row As Long

last_row = Range("A" & Rows.Count).End(xlUp).Row
Sheet1.Range("B2:B" & last_row).FormulaR1C1 = "=MID(RC[-1],4,99)"

End Sub

hth,

Doug

"brook6" wrote in message
...
I have a spreadsheet of part numbers where number of rows will vary month
to month. Each month, I need to extract 2 digits from the part number and
store in another column for some other analysis. I use MID function to do
the extraction.

I have been entering the formula in the top cell of a blank column and
then copying it to the remaining rows. How do I get a macro to do this?
When I used 'macro recorder', it picked up the exact cell range for this
month's spreadsheet...which means it could crash next month if number of
rows is different.