Re : Excel FormulaArray Copy-and-Paste via VBA
 

Re : Excel FormulaArray Copy-and-Paste via VBA

1. Enters an Excel worksheet.

2. It shows a formula such as,

=IF(SUM(IF(ISERROR(SEARCH("-",TagNmbr))+ISERROR(SEARCH("-",RC[-1])),0,
IF(MID(TagNmbr,1,SEARCH("-",TagNmbr)-1)=MID(RC[-1],
1,SEARCH("-",RC[-1])-1),1,0)))=1,"@UniQue",
IF(ISERROR(SEARCH("-",RC[-1])),"@VoID",MID(RC[-1],
1,SEARCH("-",RC[-1])-1)&" x "&TEXT(
SUM(IF(ISERROR(SEARCH("-",TagNmbr))+ISERROR(SEARCH("-",RC[-1])),0,
IF(MID(TagNmbr,1,SEARCH("-",TagNmbr)-1)=MID(RC[-1],
1,SEARCH("-",RC[-1])-1),1,0))),"00")))

3. The following VBA coding was emplaced to copy and paste the given
formula :-

ColHeadFormula =
..Offset(1, ColumnOffset).Cells(1, 1)
..FormulaArray
'ColHeadFormula is Dim'd as String.

With .Offset(1, ColumnOffset).Cells(1, 1)
.FormulaArray = ColHeadFormula
End With

4. However, the above coding runs into an exception.

5. Note that the given formula is already rendered,
Application.ReferenceStyle = xlR1C1.

6. Please comment and share your experience.

7. Please enlighten with viable alternatives.

8. Regards.