Splitting Formula - Better way?

[spydey]

So I have a number of formulas that are currently in-cell.

I am converting the workbook to VBA and so would like to use the .FormulaArray and .FormulaR1C1 to place the formula in the columns.
However, I have found that those two pieces of code have a 255 character limit of what they can place in the cell.

I did some research and found that I could input a simple formula, kind of like the "shell" of the formula, with "place holders" in it, then do a .replace and replace each place holder with the correct section of the formula.

The issue I am having is that I need to follow syntax and it gets kind of confusing when my formula is massive.

Is there a better way to do this?

Would actually coding out the formula programmatically be better than simply inserting the formula into the cell?

What kind of performance gain/loss might I have?

Any input is very help and much appreciated!!

Thanks!!

-Spydey

 

[Jeffrey Mahoney]

There are a couple things I've done in the past. One is to have a hidden row of formulas that I copy down. The other is to create the formulas from scratch in one cell and then copy it down using something like this. You can specify multiple columns in the copy process. Does that help

Sub Formulas()
  Dim Cel As Range
  
  Set Cel = Range("A1")
  Cel.Value = "=C1+B1"
  Range("A1:A1000").Formula = Cel.Formula


End Sub

 

[Jeffrey Mahoney]

Make sure you:

Application.Calculation = xlCalculationManual

at the beginning and

Application.Calculation = xlCalculationAutomatic

at the end