Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
programming of matrix formulas in vba
hi
I use the formula {=if(A2:A6<B1:D1);1;0} to fill the cells b2:d6 with the information whether the values in column A are smaller than the values given in row 1. Now I want to program this formula in VBA (without running through "while - wend" or "for- next" loops. This would be advantegous if one have to handle with big data files. But - I don't how to do this. Of course I've started with introducing the arrays. But then ..? Thanks - Thomas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
programming of matrix formulas in vba
Thokel FormulaARRAY property needs an r1c1 reference. If you;re not familiar with r1c1 notation let Convertformula do the translation from a1 to r1c1 for you. PLEASE note you're braces were incorrect! and you'll have to use USenglish separators. Sub DoArrayFml() Dim sFml$ sFml = "=IF(A2:A6<B1:D1,1,0)" Range("g1:g10").FormulaArray = _ Application.ConvertFormula(sFml, xlA1, xlR1C1) End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam thokel wrote : if(A2:A6<B1:D1);1;0 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
programming of matrix formulas in vba
VBA's help isn't quite true with the R1C1 reference style for arrayformulas.
This version of KeepItCool's routine worked fine for me: Option Explicit Sub DoArrayFm2A() Dim sFml$ sFml = "=IF(A2:A6<B1:D1,1,0)" Range("b2:d6").FormulaArray = sFml End Sub thokel wrote: hi I use the formula {=if(A2:A6<B1:D1);1;0} to fill the cells b2:d6 with the information whether the values in column A are smaller than the values given in row 1. Now I want to program this formula in VBA (without running through "while - wend" or "for- next" loops. This would be advantegous if one have to handle with big data files. But - I don't how to do this. Of course I've started with introducing the arrays. But then ..? Thanks - Thomas -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
programming of matrix formulas in vba
Dave,
you are correct, but worse... my r1c1 conversion is lacking the relativeto argument, making it work only when a1 is activecell. Sub DoArrayFmlR1C1() Dim sFml$ sFml = "=IF(A2:A6<B1:D1,1,0)" Range("g1:g10").FormulaArray = _ Application.ConvertFormula(sFml, xlA1, xlR1C1, , Range("g1")) End Sub so I'll use a1 notation.. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Dave Peterson wrote : VBA's help isn't quite true with the R1C1 reference style for arrayformulas. This version of KeepItCool's routine worked fine for me: Option Explicit Sub DoArrayFm2A() Dim sFml$ sFml = "=IF(A2:A6<B1:D1,1,0)" Range("b2:d6").FormulaArray = sFml End Sub thokel wrote: hi I use the formula {=if(A2:A6<B1:D1);1;0} to fill the cells b2:d6 with the information whether the values in column A are smaller than the values given in row 1. Now I want to program this formula in VBA (without running through "while - wend" or "for- next" loops. This would be advantegous if one have to handle with big data files. But - I don't how to do this. Of course I've started with introducing the arrays. But then ..? Thanks - Thomas |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
programming of matrix formulas in vba
Incorrect documentation is sometimes a PITA, huh?
keepITcool wrote: Dave, you are correct, but worse... my r1c1 conversion is lacking the relativeto argument, making it work only when a1 is activecell. Sub DoArrayFmlR1C1() Dim sFml$ sFml = "=IF(A2:A6<B1:D1,1,0)" Range("g1:g10").FormulaArray = _ Application.ConvertFormula(sFml, xlA1, xlR1C1, , Range("g1")) End Sub so I'll use a1 notation.. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Dave Peterson wrote : VBA's help isn't quite true with the R1C1 reference style for arrayformulas. This version of KeepItCool's routine worked fine for me: Option Explicit Sub DoArrayFm2A() Dim sFml$ sFml = "=IF(A2:A6<B1:D1,1,0)" Range("b2:d6").FormulaArray = sFml End Sub thokel wrote: hi I use the formula {=if(A2:A6<B1:D1);1;0} to fill the cells b2:d6 with the information whether the values in column A are smaller than the values given in row 1. Now I want to program this formula in VBA (without running through "while - wend" or "for- next" loops. This would be advantegous if one have to handle with big data files. But - I don't how to do this. Of course I've started with introducing the arrays. But then ..? Thanks - Thomas -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I transpose nXm matrix to mXn Matrix | Excel Worksheet Functions | |||
strange behaviour with matrix formulas | Excel Worksheet Functions | |||
Programming Formulas | Excel Programming | |||
Programming Array Formulas in VBA - Can they be intermediate results in RAM? | Excel Programming | |||
Array formulas into VB programming | Excel Programming |