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

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

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

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

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