programming of matrix formulas in vba

[Guest]

hi

I use the formula {=if(A2:A6<B11);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

 

[keepITcool]

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<B11,1,0)"
Range("g1:g10").FormulaArray = _
Application.ConvertFormula(sFml, xlA1, xlR1C1)
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


thokel wrote :

 

[Dave Peterson]

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<B11,1,0)"
Range("b2:d6").FormulaArray = sFml
End Sub

 

[keepITcool]

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<B11,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 :

 

[Dave Peterson]

Incorrect documentation is sometimes a PITA, huh?

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<B11,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 :