tiehaze
asked on
Arrays & .formulaR1C1 in excel vba
I am not real familiar with arrays, and the formulas are not working. Any ideas why?
With Array(Range(rST.Offset(0, 11).Address), Range(rLT.Offset(0, 11).Address), Range(rTI.Offset(0, 11).Address))
.FormulaR1C1 = "=RC4/" & NetAssets
.FormulaR1C1 = "=ROUND(RC[-1],3)"
.FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
End with
With Array(Range(rST.Offset(0, 11).Address), Range(rLT.Offset(0, 11).Address), Range(rTI.Offset(0, 11).Address))
.FormulaR1C1 = "=RC4/" & NetAssets
.FormulaR1C1 = "=ROUND(RC[-1],3)"
.FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
End with
ASKER
Sorry, I want this:
rST.Offset(0, 11).FormulaR1C1 = "=RC4/" & NetAssets
rLT.Offset(0, 11).FormulaR1C1 = "=RC4/" & NetAssets
rTI.Offset(0, 11).FormulaR1C1 = "=RC4/" & NetAssets
rST.Offset(0, 12).FormulaR1C1 = "=ROUND(RC[-1],3)"
rLT.Offset(0, 12).FormulaR1C1 = "=ROUND(RC[-1],3)"
rTI.Offset(0, 12).FormulaR1C1 = "=ROUND(RC[-1],3)"
rST.Offset(0, 13).FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
rLT.Offset(0, 13).FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
rTI.Offset(0, 13).FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
... there are actually more formulas, but I excluded them for the simplicity of the question
rST.Offset(0, 11).FormulaR1C1 = "=RC4/" & NetAssets
rLT.Offset(0, 11).FormulaR1C1 = "=RC4/" & NetAssets
rTI.Offset(0, 11).FormulaR1C1 = "=RC4/" & NetAssets
rST.Offset(0, 12).FormulaR1C1 = "=ROUND(RC[-1],3)"
rLT.Offset(0, 12).FormulaR1C1 = "=ROUND(RC[-1],3)"
rTI.Offset(0, 12).FormulaR1C1 = "=ROUND(RC[-1],3)"
rST.Offset(0, 13).FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
rLT.Offset(0, 13).FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
rTI.Offset(0, 13).FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
... there are actually more formulas, but I excluded them for the simplicity of the question
You can't work with arrays like that. Try this:
Dim Target As Range
For Each Target In Array(Range(rST.Offset(0, 11).Address), Range(rLT.Offset(0, 11).Address), Range(rTI.Offset(0, 11).Address))
With Target
.FormulaR1C1 = "=RC4/" & NetAssets
.FormulaR1C1 = "=ROUND(RC[-1],3)"
.FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
End with
Next Target
Kevin
Dim Target As Range
For Each Target In Array(Range(rST.Offset(0, 11).Address), Range(rLT.Offset(0, 11).Address), Range(rTI.Offset(0, 11).Address))
With Target
.FormulaR1C1 = "=RC4/" & NetAssets
.FormulaR1C1 = "=ROUND(RC[-1],3)"
.FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
End with
Next Target
Kevin
I think you need two nested loops:
Dim frmla As Variant, Target As Variant
Dim i As Long
For Each frmla In Array("=RC4/" & NetAssets, "=ROUND(RC[-1],3)", "=ROUNDDOWN(RC[-2],3)")
For Each Target In Array(rST.Offset(0, 11 + i).Address, rLT.Offset(0, 11 + i).Address, rTI.Offset(0, 11 + i).Address)
Range(Target).FormulaR1C1 = frmla
Next Target
i = i + 1
Next frmla
Dim frmla As Variant, Target As Variant
Dim i As Long
For Each frmla In Array("=RC4/" & NetAssets, "=ROUND(RC[-1],3)", "=ROUNDDOWN(RC[-2],3)")
For Each Target In Array(rST.Offset(0, 11 + i).Address, rLT.Offset(0, 11 + i).Address, rTI.Offset(0, 11 + i).Address)
Range(Target).FormulaR1C1 = frmla
Next Target
i = i + 1
Next frmla
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rST.Offset(0, 11).FormulaR1C1 = "=RC4/" & NetAssets
rLT.Offset(0, 11).FormulaR1C1 = "=ROUND(RC[-1],3)"
rTI.Offset(0, 11).FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
?
Regards,
Rory