Passing Array Variables (XP; SR 3)

Topic

New Reply

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

Reply | Quote

Replies

Does this do what you want?

    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!R2:R5000>=2000)*AccountList!Q2:Q5000)"
    Next I

Reply | Quote

Legare,

It does exactly what I want it to do.

Thank,s
John

Reply | Quote

Legare,

It does exactly what I want it to do.

Thank,s
John

Reply | Quote

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

Reply | Quote

Why does your code:

    Set oCell = Nothing

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Why does your code:

    Set oCell = Nothing

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.

Reply | Quote

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

Reply | Quote