convert all formulas on a worksheet to aray formulas

[Guest]

Hi, I have text throughout a worksheet that look like this, for exampe:

%&%a1

I can run a macro to replace %&% with =, and that "turns on" the formulas.

However, I the formulas in question are much more complicated, and they need
an aray input -- control shift enter.

I tried recording a macro that would replace all regular formulas with aray
formulas -- but it wouldn't record.

Does anyone have any code that would replace all the regular formulas on a
worksheet with Aray formulas?

and since I"m already asking the question, out of curiosity, what code would
convert to aray formulas for just a section of the worksheet?

Separately, I have a follow up nonessential question for anyone interested
-- all of these aray formulas slow up my workbook -- any direction as to
creating a macro that would take the place of these aray formulas -- that way
I run the macro and it refreshes the data?

those were a lot of questions. thanks very much for thinking about it.



SteveC

 

[Guest]

Hi Steve,

I will take an example. I have a string %&%A1 in cell A2 in a worksheet.
The following code can change it to an array formula.

Sub Repl()
ActiveCell.FormulaArray = Replace(ActiveCell.Value, "%&%", "=")
End Sub

If you want to apply this macro on different ranges then probably you would
want to create a range object and loop through cells to change it to array
formulae.

I must warn you though that doing so would slow down your project a lot.
Also array formulae are not the solution for every problem in the world. I am
not sure why you want change all the formulae to array formulae.

 

[Guest]

thanks, I will give this a shot.

i couldn't record the macro because there were more than 255 characters in
the cell. So I used the define name feature to shorten the characters in the
cell.

I need to find a macro or udf to replace the aray formulas, because now the
thing is just way too slow. I'll try something else and post back. Thanks
for the code...