Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter array formula with VBA
I need to programmatically enter an array formula into a cell after the ranges involved in the formula are determined. I also do not
want to use the R1C1 format. My "base" formula is =SUM(IF(RIGHT($M$1:$BC$1,3)="Alt",M2:BC2,0)). However, the $M$1:$BC$1 and the M2:BC2 will change each time I place this formula into a cell. I know that the general manner of entering an array formula is to use syntax which begins with the word "Evaluate". Can somebody help me on this one? -- RMC,CPA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter array formula with VBA
How do you determine the changed values.
The general format for array formulae is activecell.formulaarray="=SUM(IF(RIGHT($M$1:$BC$1, 3)=""Alt"",M2:BC2,0))" -- HTH RP (remove nothere from the email address if mailing direct) "R. Choate" wrote in message ... I need to programmatically enter an array formula into a cell after the ranges involved in the formula are determined. I also do not want to use the R1C1 format. My "base" formula is =SUM(IF(RIGHT($M$1:$BC$1,3)="Alt",M2:BC2,0)). However, the $M$1:$BC$1 and the M2:BC2 will change each time I place this formula into a cell. I know that the general manner of entering an array formula is to use syntax which begins with the word "Evaluate". Can somebody help me on this one? -- RMC,CPA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter array formula with VBA
I determine the changed values by counting the columns to the right from M1 and then making adjustments to convert that into the
proper cell address. Once this is done, I will copy the array formula down until the cell to the right is blank. -- RMC,CPA "Bob Phillips" wrote in message ... How do you determine the changed values. The general format for array formulae is activecell.formulaarray="=SUM(IF(RIGHT($M$1:$BC$1, 3)=""Alt"",M2:BC2,0))" -- HTH RP (remove nothere from the email address if mailing direct) "R. Choate" wrote in message ... I need to programmatically enter an array formula into a cell after the ranges involved in the formula are determined. I also do not want to use the R1C1 format. My "base" formula is =SUM(IF(RIGHT($M$1:$BC$1,3)="Alt",M2:BC2,0)). However, the $M$1:$BC$1 and the M2:BC2 will change each time I place this formula into a cell. I know that the general manner of entering an array formula is to use syntax which begins with the word "Evaluate". Can somebody help me on this one? -- RMC,CPA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter array formula with VBA
Using XL's macro recorder and leveraging its object model can lead to
wonders. First, use the macro recorder to enter what you call the base formula into a cell. The result: Selection.FormulaArray = _ "=SUM(IF(RIGHT(R1C13:R1C55,3)=""Alt"",R[-1]C[3]:R[-1]C[45],0))" Next, we replace the specific cell references with their generalized counterparts. Dim aRng As Range Set aRng = Range(Range("m1"), Range("m1").End(xlToRight)) Selection.FormulaArray = _ "=SUM(IF(RIGHT(" & aRng.Address _ & ",3)=""Alt""," _ & aRng.Offset(1).Address(False, False) & ",0))" Finally, suppose the formula should be in column L for all rows that contain information in M:whatever. Then, we replace the last line above with: Range("L2").FormulaArray = _ "=SUM(IF(RIGHT(" & aRng.Address _ & ",3)=""Alt""," _ & aRng.Offset(1).Address(False, False) & ",0))" Range(Range("m2"), Range("m2").End(xlDown)).Offset(, -1).FillDown -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I determine the changed values by counting the columns to the right from M1 and then making adjustments to convert that into the proper cell address. Once this is done, I will copy the array formula down until the cell to the right is blank. -- RMC,CPA "Bob Phillips" wrote in message ... How do you determine the changed values. The general format for array formulae is activecell.formulaarray="=SUM(IF(RIGHT($M$1:$BC$1, 3)=""Alt"",M2:BC2,0))" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enter array formula with VBA
Thank you for your help. I definitely agree with the wonders of using the macro recorder in many situations. At the same time, it
can occasionally yield some ridiculous code that is worthless. Everything is working now. -- RMC,CPA "Tushar Mehta" wrote in message ... Using XL's macro recorder and leveraging its object model can lead to wonders. First, use the macro recorder to enter what you call the base formula into a cell. The result: Selection.FormulaArray = _ "=SUM(IF(RIGHT(R1C13:R1C55,3)=""Alt"",R[-1]C[3]:R[-1]C[45],0))" Next, we replace the specific cell references with their generalized counterparts. Dim aRng As Range Set aRng = Range(Range("m1"), Range("m1").End(xlToRight)) Selection.FormulaArray = _ "=SUM(IF(RIGHT(" & aRng.Address _ & ",3)=""Alt""," _ & aRng.Offset(1).Address(False, False) & ",0))" Finally, suppose the formula should be in column L for all rows that contain information in M:whatever. Then, we replace the last line above with: Range("L2").FormulaArray = _ "=SUM(IF(RIGHT(" & aRng.Address _ & ",3)=""Alt""," _ & aRng.Offset(1).Address(False, False) & ",0))" Range(Range("m2"), Range("m2").End(xlDown)).Offset(, -1).FillDown -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I determine the changed values by counting the columns to the right from M1 and then making adjustments to convert that into the proper cell address. Once this is done, I will copy the array formula down until the cell to the right is blank. -- RMC,CPA "Bob Phillips" wrote in message ... How do you determine the changed values. The general format for array formulae is activecell.formulaarray="=SUM(IF(RIGHT($M$1:$BC$1, 3)=""Alt"",M2:BC2,0))" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif? array enter? | Excel Discussion (Misc queries) | |||
I can't enter an array in Excel 2007 | Excel Worksheet Functions | |||
Incorporating INDEX function into Array Formula (CNTRL-SHIFT-ENTER | Excel Worksheet Functions | |||
What does hitting Ctrl + Shift + Enter to enter a formula do??? Help a n00b out. | Excel Worksheet Functions | |||
Proper way to enter array formula | Excel Worksheet Functions |