spydey

Active Member
Joined
Sep 19, 2017
Messages
306
Office Version
  1. 2013
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,766
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

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


End Sub
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,766
Make sure you:

Code:
Application.Calculation = xlCalculationManual
at the beginning and
Code:
Application.Calculation = xlCalculationAutomatic
at the end
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,966
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top