Re : Excel FormulaArray Copy-and-Paste via VBA

T

tkt_tang

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy formula 6
Copy Array Formula 4
Trouble selecting multiple sheets using RDB code 13
VBA Code Optimization for Array Formulas 0
copy and paste error 2
Search , then copy and paste from multiple worksheets. VBA help 1
Application.FormulaConvert 5
Copy & Paste Conditional Formatted duplicates w/vba 0

Top