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]

Try

Sub tp()
'
' tp Macro
'
Range("A1").Value = "tp"
Range("A2", Range("F" & Rows.Count).End(xlUp)).FormulaR1C1 = _
   "=IF(iserror(VLOOKUP(RC[5],Sheet1!C,1,FALSE)),""yes"",""no"")"
Range("A7").Select
End Sub

 

[sobeitjedi]

But I want the data to be filled in automatically when I run a macro. I'm not sure what alternative option you are suggesting? I mean, I'm using Excel, this is an Excel forum, so therefore I'm using a table.

 

[sobeitjedi]

Try

Sub tp()
'
' tp Macro
'
Range("A1").Value = "tp"
Range("A2", Range("F" & Rows.Count).End(xlUp)).FormulaR1C1 = _
   "=IF(iserror(VLOOKUP(RC[5],Sheet1!C,1,FALSE)),""yes"",""no"")"
Range("A7").Select
End Sub

This bring up a window asking for me to select a file to update values????

 

[Fluff]

In that case, I suspect that you don't have a sheet called "Sheet1" in that workbook.

 

[My Aswer Is This]

Any time your referring to a Table it's best to give us the Table name.
And refer to columns in the Table.

Like enter "Jane" into Table Named Me Column(1)

This would be column (1) of the Table not Column(1) of the sheet.

 

[sobeitjedi]

Ok, small progress ...

Sub tp()
'
' tp Macro
'
Range("A1").Value = "tp"
Range("A2", Range("F" & Rows.Count).End(xlUp)).FormulaR1C1 = _
"=IF(iserror(VLOOKUP(RC[5],Sheet1!C,1,FALSE)),""yes"",""no"")"
Range("A7").Select
End Sub

This is not erroring, but it has changed all the values in columns A to F to yes (even if they are meant to be no). I only want the data changed in column A.

 

[sobeitjedi]

Sorry, I don't understand this talk of tables. It's very confusing. I'm using Excel. It's a very basic sheet, that I just want to cross reference with another sheet. My data starts at A1

 

[Fluff]

Oops, it should be

Sub tp()
'
' tp Macro
'
Range("A1").Value = "tp"
Range("[COLOR=#ff0000]A2:A[/COLOR]", Range("F" & Rows.Count).End(xlUp)[COLOR=#ff0000].Row[/COLOR]).FormulaR1C1 = _
   "=IF(iserror(VLOOKUP(RC[5],Sheet1!C,1,FALSE)),""yes"",""no"")"
Range("A7").Select
End Sub

 

[sobeitjedi]

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 ...

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

 

[My Aswer Is This]

Sorry, I don't understand this talk of tables. It's very confusing. I'm using Excel. It's a very basic sheet, that I just want to cross reference with another sheet. My data starts at A1

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.
​