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

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

 

[JLGWhiz]

you can create a dynamic range by using a variable to define your last row with data.

Dim lr As Long
lr = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
Range("A1"),FormulaR1C1 = "=IF(iserror(VLOOKUP(RC[5],Sheet1!C,1,FALSE)),""yes"",""no"")"
Range("A1:A" & lr).Filldown

 

[My Aswer Is This]

Assuming you want the value "tp

Put in all cells in column A if there are values in column F

Try this:

Sub Fill_Down()
'Modified 10/10/2018 8:38 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Cells(1, 1).Resize(Lastrow).Value = "tp"
Application.ScreenUpdating = True
End Sub

 

[sobeitjedi]

Thanks for the replies, they are all causing errors so far.

 

[Norie]

Are you actually using a table?

If you were then the formula would get copied down to the last row of data automatically.

 

[sobeitjedi]

Yes, I'm using a table. Perhaps I need the code putting in my marco from my original post. Maybe I'm putting it in the wrong place?

 

[Fluff]

What errors do you get?

 

[sobeitjedi]

Ok, so this is the modified macro ...

Sub tp()
'
' tp Macro
'
Range("A1").Select
ActiveCell.FormulaR1C1 = _
Range("A1", Range("F" & Rows.Count).End(xlUp)).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
End Sub



And I get error 13 type mismatch. When I go to debug, these lines are highlighted ...

ActiveCell.FormulaR1C1 = _
Range("A1", Range("F" & Rows.Count).End(xlUp)).FormulaR1C1 = _
"=IF(iserror(VLOOKUP(RC[5],Sheet1!C,1,FALSE)),""yes"",""no"")"

 

[Norie]

If you are using a table you shouldn't need code to copy down the formula.