Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert all formulas on a worksheet to aray formulas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert all formulas on a worksheet to aray formulas
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert all formulas on a worksheet to aray formulas
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
Convert Values to formulas | Excel Worksheet Functions | |||
I get my formulas like this RC[-1]*R[-2]C[6] how could I convert . | Excel Worksheet Functions | |||
convert a worksheet with formulas to a macro | Excel Programming |