Excel - copy formula in one field across X columns and down to the last row in VBA

0
votes

Let's say I had this formula in cell B2

=IFERROR(MID($A2,FIND("|",SUBSTITUTE($A2,"@","|",B$1)),IFERROR(FIND(" ",$A2,FIND("|",SUBSTITUTE($A2,"@","|",B$1))),LEN($A2)+1)-FIND("|",SUBSTITUTE($A2,"@","|",B$1))),"")

In VBA< how would I copy this down to the last row and across to (and including column P)

I've seen examples where it shows a single column but not multiple e.g. VBA to fill formula down till last row in column

1
vbaexcelexcel-formula
Try ThisWorkbook.Worksheet("SheetName").Range("B2","P" & nmbRows).Formula = "Your formula here" where nmbRows is the number of the last used row. – AntiDrondert

1 Answers

1
votes

Assuming the number of rows is determined by data in column A, then try:

Dim lRow As Long
With ActiveSheet
    lRow = .Cells(Rows.Count, 1).End(xlUp).Row
    .Range("B2:P" & lRow).FormulaR1C1 = "=IFERROR(MID(RC1,FIND(""|"",SUBSTITUTE(RC1,""@"",""|"",R1C)),IFERROR(FIND("" "",RC1,FIND(""|"",SUBSTITUTE(RC1,""@"",""|"",R1C))),LEN(RC1)+1)-FIND(""|"",SUBSTITUTE(RC1,""@"",""|"",R1C))),"""")"
End With