Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Arrays & .formulaR1C1 in excel vba

Posted on 2007-03-30
Medium Priority
3,825 Views
I am not real familiar with arrays, and the formulas are not working. Any ideas why?

.FormulaR1C1 = "=RC4/" & NetAssets
.FormulaR1C1 = "=ROUND(RC[-1],3)"
.FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
End with

0
Question by:tiehaze
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 85

Expert Comment

ID: 18824617
What are you trying to do? I can only guess that that should be:
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
0

Author Comment

ID: 18824719
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
0

LVL 81

Expert Comment

ID: 18824791
You can't work with arrays like that. Try this:

Dim Target As Range
With Target
.FormulaR1C1 = "=RC4/" & NetAssets
.FormulaR1C1 = "=ROUND(RC[-1],3)"
.FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
End with
Next Target

Kevin
0

LVL 81

Expert Comment

ID: 18827698
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)")
Range(Target).FormulaR1C1 = frmla
Next Target
i = i + 1
Next frmla

0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 18827919
Oh my! Thanks Brad...I really goofed that one...

You could also do it this way:

Dim Target As Range
Set Target = Union(rST, rLT, rTI)
Target.Offset(0, 11).FormulaR1C1 = "=RC4/" & NetAssets
Target.Offset(0, 12).FormulaR1C1 = "=ROUND(RC[-1],3)"
Target.Offset(0, 13).FormulaR1C1 = "=ROUNDDOWN(RC[-2],3)"
...

Kevin
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.