i have an array formula that i'd like to evaluate in code and store it in a
variable instead of creating the array formula.

is it possible?

here is what the formula would look like if i wrote it to a cell:

range("A1").formulaarray = "=SUM(G" & iRow & ":I" & iRow &
")/TRANSPOSE($M$2:$M$4)*O" & iRow

i need to loop through some cells and total all of them, that's why i'd like to
store it in a variable.
--


Gary

Untested, uncompiled.

dim myVal as double
dim myFormula as string
dim iRow as long

for irow = 1 to 9
myformula = "SUM(G" & iRow & ":I" & iRow & ")/TRANSPOSE($M$2:$M$4)*O" & iRow
myval = activesheet.evaluate(myformula)
'do something with myval
next irow



Gary Keramidas wrote:

i have an array formula that i'd like to evaluate in code and store it in a
variable instead of creating the array formula.

is it possible?

here is what the formula would look like if i wrote it to a cell:

range("A1").formulaarray = "=SUM(G" & iRow & ":I" & iRow &
")/TRANSPOSE($M$2:$M$4)*O" & iRow

i need to loop through some cells and total all of them, that's why i'd like to
store it in a variable.
--

Gary

--

Dave Peterson

didn't seem to work, dave. i have some code that loops through the range and
gives me the result i need. i was wondering about a formula.

thanks

--


Gary


"Dave Peterson" wrote in message
...
Untested, uncompiled.

dim myVal as double
dim myFormula as string
dim iRow as long

for irow = 1 to 9
myformula = "SUM(G" & iRow & ":I" & iRow & ")/TRANSPOSE($M$2:$M$4)*O" & iRow
myval = activesheet.evaluate(myformula)
'do something with myval
next irow



Gary Keramidas wrote:

i have an array formula that i'd like to evaluate in code and store it in a
variable instead of creating the array formula.

is it possible?

here is what the formula would look like if i wrote it to a cell:

range("A1").formulaarray = "=SUM(G" & iRow & ":I" & iRow &
")/TRANSPOSE($M$2:$M$4)*O" & iRow

i need to loop through some cells and total all of them, that's why i'd like
to
store it in a variable.
--

Gary

--

Dave Peterson

Does the formula work when you type it into a cell?

If yes, share that formula that worked.

Gary Keramidas wrote:

didn't seem to work, dave. i have some code that loops through the range and
gives me the result i need. i was wondering about a formula.

thanks

--

Gary

"Dave Peterson" wrote in message
...
Untested, uncompiled.

dim myVal as double
dim myFormula as string
dim iRow as long

for irow = 1 to 9
myformula = "SUM(G" & iRow & ":I" & iRow & ")/TRANSPOSE($M$2:$M$4)*O" & iRow
myval = activesheet.evaluate(myformula)
'do something with myval
next irow



Gary Keramidas wrote:

i have an array formula that i'd like to evaluate in code and store it in a
variable instead of creating the array formula.

is it possible?

here is what the formula would look like if i wrote it to a cell:

range("A1").formulaarray = "=SUM(G" & iRow & ":I" & iRow &
")/TRANSPOSE($M$2:$M$4)*O" & iRow

i need to loop through some cells and total all of them, that's why i'd like
to
store it in a variable.
--

Gary

--

Dave Peterson

--

Dave Peterson

here's a similar formula that sort of works. i explain it in a separate post
because i was just looking for a formula, not a value to store in a variable.

=(SUM((1-TRANSPOSE(H2:H4))*G22:I22)+SUM(G22:I22))/TRANSPOSE($M$2:$M$4)

--


Gary


"Dave Peterson" wrote in message
...
Does the formula work when you type it into a cell?

If yes, share that formula that worked.

Gary Keramidas wrote:

didn't seem to work, dave. i have some code that loops through the range and
gives me the result i need. i was wondering about a formula.

thanks

--

Gary

"Dave Peterson" wrote in message
...
Untested, uncompiled.

dim myVal as double
dim myFormula as string
dim iRow as long

for irow = 1 to 9
myformula = "SUM(G" & iRow & ":I" & iRow & ")/TRANSPOSE($M$2:$M$4)*O" &
iRow
myval = activesheet.evaluate(myformula)
'do something with myval
next irow



Gary Keramidas wrote:

i have an array formula that i'd like to evaluate in code and store it in
a
variable instead of creating the array formula.

is it possible?

here is what the formula would look like if i wrote it to a cell:

range("A1").formulaarray = "=SUM(G" & iRow & ":I" & iRow &
")/TRANSPOSE($M$2:$M$4)*O" & iRow

i need to loop through some cells and total all of them, that's why i'd
like
to
store it in a variable.
--

Gary

--

Dave Peterson

--

Dave Peterson