Copying Formula to bottom of column based on last cell in another coulum

[JTL9161]

I am trying to copy the formula in the active cell in column F to the bottom line of column F based on the last cell with data in column J. When I use the below code it copies it all the way down column F past the last cell in J.

Am I forgetting something in this code?

LastRowF = Range("F" & Rows.Count).End(xlUp).Row
LastRowJ = Range("J" & Rows.Count).End(xlUp).Row
Range("F" & LastRowF).AutoFill Destination:=Range("F" & LastRowF & ":F" & LastRowJ)

Thanks
James

 

[Fluff]

Do you have a formula in col J that returns ""

 

[JTL9161]

I have a formula in column F (active cell) I need to copy that formula down to the bottom of column F based on the last cell with data in column J. Column J has dates in it. The formula looks at the date in column J and depending on whether the date is before or after the current date puts either "retro" or "future" in column F. Hope that explains it better.

 

[Fluff]

If your code is copying the formula in col F further than you expect, then you have something in col J after what you think is the last row.
So you need to check what is in those cells.

 

[JTL9161]

Copy Down

I'm having a mental block.

I have selected 1 row 7 columns of data and copied. (Columns K to Q)

Range(ActiveCell, ActiveCell.Offset(0, 6)).Copy

I need to paste this to the next row down in column K and continue down till the last cell that has data in it from column D.

I get the copied part but cannot get the data to paste down to the bottom (bottom based on last cell in col. D)


James

 

[njimack]

Re: Copy Down

Does this work for you? (untested)

Sub x()
Dim LastRow As Long

LastRow = Cells(Rows.Count, 4).End(xlUp).Row
Range(ActiveCell, ActiveCell.Offset(, 6)).Copy Range(ActiveCell.Offset(1), Cells(LastRow, ActiveCell.Column + 6))
End Sub

 

[Joe4]

Re: Copy Down

Since your two questions are so similar, I have merged your two threads together.

 

[JTL9161]

Re: Copy Down

BINGO! Thanks.

 

[Sushiboy]

Re: Copy Down

Hello, im trying to make this code work.

in column A are fixed data from row 1 to say 10+. Column B,C,D are blank. I need to apply the formula in these columns based on the data in Column A. Please advise

lngLastRow = Range("A" & Rows.Count).End(xlUp).Row Range(Columns(ActiveCell.Column) & lngLastRow).FormulaR1C1 = _ "=IF(COUNTIF('Xceptor T'!C1,RC1), ""UPDATED"", ""FAILED"")"​

 

[Sushiboy]

Re: Copy Down

FYI I don't want the code to reference B, C or D.....can the code use activecell column so it will apply the formula when the activecell in these columns are active