Copy the formula to the whole list

Question

I have a piece of code that copies a specific formula from one worksheet to another. I'm having trouble applying this formula to all rows in a particular column. Is there a way to copy the formula downwards? The formula I use is : =IFERROR(IF(SEARCH("100",D2),"YES",""),"") so the next cell(A3) should have this formula =IFERROR(IF(SEARCH("100",D3),"YES",""),"") and so on.


Dim x As Variant
Dim y As Variant

x = ThisWorkbook.Worksheets("Sample").Range("A2").formula 

ThisWorkbook.Sheets("Sample 1").Range("A2").Value = x

Dim numr As Integer
numr = Range("X2").End(xlDown).Row 'calculating all the rows

Darrell H Darrell H · Accepted Answer · 2021-09-28T14:35:37

No need to pass x around. This assumes that when you calculate numr that you are in the proper sheet. This will fill the range with the formula.

ThisWorkbook.Sheets("Sample 1").Range("A2") = ThisWorkbook.Worksheets("Sample").Range("A2")

Dim numr As Long
numr = ThisWorkbook.Sheets("Sample 1").Cells(Rows.Count, "X").End(xlUp).Row 'calculating all the rows
ThisWorkbook.Sheets("Sample 1").Range("A2:A" & numr).FillDown

Copy the formula to the whole list

1 Answers