Applying a formula down a column, only as far as needed

[sobeitjedi]

Hi.

My table length will vary week to week. I want to put data in column A, only if there is data on the same row in column F.

My macro at the moment is...

Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(iserror(VLOOKUP(RC[5],Sheet1!C,1,FALSE)),""yes"",""no"")"

Columns("A:A").Select
Selection.FillDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "tp"
Range("A7").Select


But this is populating the data in the whole of column A, how do I fix?

 

[Fluff]

Really appreciate your help, but still no joy. Now I get a run time error 1004, range of object global failed with these two rows highlighted when I click debug ...

I'll get it right eventually (honest)
It should be

Range("A2:A"[COLOR=#ff0000] &[/COLOR] Range("F" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
   "=IF(iserror(VLOOKUP(RC[5],sheet1!C,1,FALSE)),""yes"",""no"")"

 

[sobeitjedi]

You said in your original post:

My table length will vary week to week. I want to put data in column A, only if there is data on the same row in column F.

A Table and a sheet are not the same.

A Table is a certain range on a Sheet.
Not sure what version of Excel your using.

But on most never versions even as far back as 2013 you can insert on your sheet a Table.
On Your ribbon you should see Insert Table.

So if you are not using a Table you should say my sheet column A length changes.
​

my sheet column A length changes

 

[sobeitjedi]

I'll get it right eventually (honest)
It should be

Range("A2:A"[COLOR=#ff0000] &[/COLOR] Range("F" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
   "=IF(iserror(VLOOKUP(RC[5],sheet1!C,1,FALSE)),""yes"",""no"")"

Perfect - thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback