Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
Hey
I have this string that is programatically inserted into a cell as an array formula: =1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIREC T("1:"&LEN(A2))), 1)), 0),LEN(A2)-SUM(1*ISERROR(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))) ,1)))) It returns a valid result when inserted as an array into a cell. But I would like to be able to evaluate this formula and get the result in VBA before I insert it into a cell. Bob Phillips posted the following yesterday but its resulting in runtime error 13 Type Mismatch: MsgBox Activesheet.Evaluate( _ "1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIREC T(" & _ """1:""&LEN(A2))),1)),0),LEN(A2)-SUM(1*ISERROR(1*MID(A2,ROW(" & _ "INDIRECT(""1:""&LEN(A2))),1))))") Thanks Todd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
Use the Evaluate function:
Dim F As String Dim V As Variant F = "=SUM(A1:A10)" V = Evaluate(F) If IsError(V) = True Then Debug.Print "ERROR IN FORMULA: " & F Else Debug.Print "RESULT: " & V End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) wrote in message ups.com... Hey I have this string that is programatically inserted into a cell as an array formula: =1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIREC T("1:"&LEN(A2))), 1)), 0),LEN(A2)-SUM(1*ISERROR(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))) ,1)))) It returns a valid result when inserted as an array into a cell. But I would like to be able to evaluate this formula and get the result in VBA before I insert it into a cell. Bob Phillips posted the following yesterday but its resulting in runtime error 13 Type Mismatch: MsgBox Activesheet.Evaluate( _ "1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIREC T(" & _ """1:""&LEN(A2))),1)),0),LEN(A2)-SUM(1*ISERROR(1*MID(A2,ROW(" & _ "INDIRECT(""1:""&LEN(A2))),1))))") Thanks Todd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
Its telling me I have an error in my formula but that cant be
correct. Something else is wrong. Here is the rest of my code: TargetFormula = "=1*MID(" & TargetCell & ",MATCH(FALSE,ISERROR(1*MID(" & TargetCell & ",ROW(INDIRECT(" & Chr(34) & "1:" & Chr(34) & "&LEN(" & TargetCell & "))),1)),0),LEN(" & TargetCell & ")-SUM(1*ISERROR(1*MID(" & TargetCell & ",ROW(INDIRECT(" & Chr(34) & "1:" & Chr(34) & "&LEN(" & TargetCell & "))),1))))" Range(RefEdit1.Value).FormulaArray = TargetFormula Then when the formula is entered into the cell -per the value in my rededit control - the value inserted into the cell is this array formula: =1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIREC T("1:"&LEN(A2))),1)), 0),LEN(A2)-SUM(1*ISERROR(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))) ,1)))) This formula gives me a valid result. I am wanting to know the result ever before I put the formula into the cell. How do I do this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
I gave up so I tried another approach... I found an unused cell in the
active worksheet and updated that empty cell with the formula and set the font to match the background so it would not appear to the user that a cell got changed. I was planning to then reference this cell and set the value of my control on the form equal to the value of the cell, BUT!!! as usual.... something else went wrong. I am using Refedit controls on a form and the refedit change events are not firing. I see this is YET another bug in the refedit control. After trying to be a bit creative with several other events, I could still not get around these refedit bugs. Now I am very frustrated... Anyway, assuming my above circumvention idea would have worked, I believe it would have been a bad programming practice to do because my code can be used from any workbook and if it used on a workbook that has code in certain worksheet events, then that could create many possible problems and thats not something I want to do. So now I am back to square one.... and that is to figure out how in the world to use the application.evaluate method to return my result on my formula stored in a large string with various variables comprising it. Any suggestions???? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
Some commands don't work well 'Evaluated.'
Here's a small section of your code... Sub Demo() Dim v 'Bad v = [Row(Indirect("1:3"))] v = Evaluate("Row(Indirect(""1:3""))") 'Good v = [Row(1:3)] End Sub Could you explain what the formula does? It appears to return the either the same number, or an error. -- HTH :) Dana DeLouis <snip |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
It looks at a string and extracts the numeric value from in between a
set of parenthesis. Also if there are 2 sets of parenthesis and one set contains alpha characters, then it will extract the numeric values and ignore the alpha. It will error out if the string contains 2 sets of parenthesis where both contain some sort of number. It may also error out in other scenarios that I have not tested, but it works for what I need it to do. So is there any hope for the eval function to work for this formula? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
Hi. Would this slight change work for you?
I like to use Replace when working with long strings. It sounds like there may be a shorter formula, but I'm not sure. This is set for Cell A2. Sub Demo() Dim s As String Dim n As Variant Const Fx As String = _ "1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(1:#),1) ),0),#-SUM(1*ISERROR(1*MID(A2,ROW(1:#),1))))" s = Replace(Fx, "#", Len([A2])) n = Evaluate(s) End Sub -- HTH :) Dana DeLouis wrote in message oups.com... It looks at a string and extracts the numeric value from in between a set of parenthesis. Also if there are 2 sets of parenthesis and one set contains alpha characters, then it will extract the numeric values and ignore the alpha. It will error out if the string contains 2 sets of parenthesis where both contain some sort of number. It may also error out in other scenarios that I have not tested, but it works for what I need it to do. So is there any hope for the eval function to work for this formula? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
Hey
I tried your code and I got an Error 2015. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
I get that error when there is nothing in A2.
The following returns 123. Sub Demo() Dim s As String Dim n As Variant Const Fx As String = _ "1*MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(1:#),1) ),0),#-SUM(1*ISERROR(1*MID(A2,ROW(1:#),1))))" [A2] = "abc(123def)" s = Replace(Fx, "#", Len([A2])) n = Evaluate(s) '<- 123 MsgBox n End Sub -- HTH :) Dana DeLouis Windows XP & Excel 2007 wrote in message oups.com... Hey I tried your code and I got an Error 2015. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate an Array Formula before inserting it in a cell
Hey
Now I am getting Runtime error 13 - Type mismatch. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Formula into Array | Excel Discussion (Misc queries) | |||
How to use/evaluate another Cell Formula in R1C1 style | Excel Programming | |||
Macro to evaluate a cell and replace one part of the formula | Excel Programming | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Array formula returning #N/A when inserting new rows. | Excel Programming |