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

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.

--
Anant


"SteveC" wrote:

> 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

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...

"Anant Basant" wrote:

> 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.
>
> --
> Anant
>
>
> "SteveC" wrote:
>
> > 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