Paste to variable number of rows

[Guest]

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

 

[mudraker]

GetBottomRow = TheSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

range("a1:A" & getbottomrow).selec

 

[Doug Glancy]

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

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.

 

[Guest]

I finally got around to trying this and got a syntax error...any idea why?
Is 'TheSheet' the name of my worksheet? what does the underline do at end of first line of text?

thx

----- mudraker > wrote: -----

GetBottomRow = TheSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

range("a1:A" & getbottomrow).select