excel 2007 Macro to copy to different lenths

[mpoulin816]

I am trying to create in my macro a way to paste to varying row lenths.
This is what I have, but it keeps putting a cell address instead of say over 2 cells. You can see it puts a range of E451. This will change every time. Range("E451").Select

Your help is greatly appreciated. I will use this formula a lot once I get the answer as most of my worksheets have variable lenth and I have yet to figure out how to do this.

Thanks Mark Poulin.

Application.Goto Reference:="R8C5"
Range("E8").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP((MINUTE(TEXT(LEFT(RC[-4],5),""hh:mm:""))),R1C27:R4C28,2,FALSE)"
Range("E8").Select
Selection.Copy
Range("D8").Select
Selection.End(xlDown).Select
Range("E451").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Range("E7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Filter"

 

[jmthompson]

So you want to populate E8 through the last cell in column E with your formula?

Assuming your data in column A is contiguous, with no blanks:

Dim lastRow as Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("E8:E" & lastRow).FormulaR1C1 = =VLOOKUP((MINUTE(TEXT(LEFT(RC[-4],5),""hh:mm:""))),R1C27:R4C28,2,FALSE)"
Range("E7").FormulaR1C1 = "Filter"

 

[mpoulin816]

Jen, Thanks so much, this is really great! it took me a little head scratching to get it to work, (my fault), but works great!!
Thanks again!
Mark