Need cell I7 to equal the last cell in column I in a Macro

[JB0903]

I need cell I7 to equal the last cell in column I within a macro. I tried to do "=CTRL+END" & got this:

TestI7 Macro
Range("I7").Select
ActiveCell.FormulaR1C1 = "=R[18]C"
Range("I8").Select
End Sub

However, this is giving me the last cell that was in the spreadsheet it was created in, I8. This spreadsheet is very long & the last cell can range from I2000 to I4999. How can I tell the macro I want cell I7 to equal the very last cell no matter what it is each time?

 

[akmatz]

Try this. Assumes that there is continuous data in all of column I beginning with I1 and ending with I (lastrow).
Always test macros on a COPY of your data, not the original.

Sub TestI7()
Range("I1").Select
Selection.End(xlDown).Select
ActiveCell.Copy
Range("I7").Select
ActiveSheet.Paste
End Sub

 

[Scott Huish]

No need for all that Selecting:

Sub test()
Range("I7") = Range("I" & Rows.Count).End(xlUp)
End Sub

 

[JB0903]

Neither seem to be working. @akmatz, the return on that is cell F8. @ Scott, that code is returning nothing. My dept uses a VERY large & extremely complocated macro for reporting job costs. With that one, we much pull the raw data report in 2007 & save in 2003. However, we all have 2010! SO...let me see if that might be the problem. I'm not thinking so because I have 15 other macro that are working in the same workbook. It's something small that I am overlooking, I know it!

Thanks guys!

 

[Scott Huish]

My code doesn't return nothing, it does exactly what you asked for:

If I put the number 15 in I15, and then run my code, it will put 15 in I7.

 

[akmatz]

Can you post the first 20 rows or so of your data? TY.

 

[JB0903]

It was a merged cell, that's why it didn't work. The merged cell was actually H7 instead of I7. Something small I knew it! Thank you so very much for your help!

I do have one concern. It is giving me something like a special paste values. The ending cell that will be referenced has a formula. Would this update or should this macro be one of the last ones ran?