Home > Software design >  Copy the formula to the whole list
Copy the formula to the whole list

Time:09-29

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

CodePudding user response:

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
  • Related