# Thread: Passing Array Variables (XP; SR 3)

1. ## Passing Array Variables (XP; SR 3)

I am trying to pass a variable into an array formula: The variable I would like to use is oCell = "A" & i & "". The variable oCell would replace A22 in the code below.

For i = 22 To 48
Range("A" & i & "") = "'" & i + 18
Range("C" & i & "").FormulaArray = "=SUM((LEFT(AccountList!B2:B5000,2)= A22)*(AccountList!C2:C5000=""Dollars"")* _
(AccountList!R2:R5000>=2000)*AccountList!Q2:Q5000) "
Next i

Thanks,
Joihn

2. ## Re: Passing Array Variables (XP; SR 3)

Does this do what you want?

<pre> For I = 22 To 48
Range("A" & I) = "'" & I + 18
Range("C" & I).FormulaArray = "=SUM((LEFT(AccountList!B2:B5000,2)= A" & I & ")*" & _
"(AccountList!C2:C5000=""Dollars"")*(AccountList!R 2:R5000>=2000)*AccountList!Q2:Q5000)"
Next I
</pre>

3. ## Re: Passing Array Variables (XP; SR 3)

The following executes, but you will have to tell us if it is correct...
'--------------------------
Sub Test()
Dim oCell As Double
Dim i As Long
For i = 22 To 48
Cells(1, i).Value = i + 18
oCell = Cells(1, i).Value
Cells(3, i).FormulaArray = _
"=SUM((LEFT(AccountList!B2:B5000,2)=" & oCell & ")*(AccountList!C2:C5000=""Dollars"")*(AccountList !R2:R5000>=2000)*AccountList!Q2:Q5000)"
Next ' i
Set oCell = Nothing
End Sub
'------------------------------

Jim Cone
San Francisco, CA

4. ## Re: Passing Array Variables (XP; SR 3)

Legare,

It does exactly what I want it to do.

Thank,s
John

5. ## Re: Passing Array Variables (XP; SR 3)

<pre> Set oCell = Nothing
</pre>

oCell is defined as a Double, not an object variable.

I also don't think the OP wanted the formula to compare to a constant value, I think he wanted to insert the cell address.

6. ## Re: Passing Array Variables (XP; SR 3)

Legare,
The Set statement was a left over from my first try at the code.
It shouldn't have been there.
Regards,
JimCone
San Francisco, CA

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•