array formulas in vba help

[Guest]

I have a workbook with a range of array formulas or CSE formulas. They are
essentially averageif formulas. They are available in 2007 but this has to be
saved in xls format. So, I have done a CSE or Array formula with the
ctrl+shift+enter. My problem is copying and pasting my formulas into other
workbooks with vba and them keeping that format. Here is the part of my code
where I try to alleviate that problem.


Here is my array formula that comes from
basebook.Worksheets("NetWeatherResidualLookup").Range("B2:B3433").Formula:

"=AVERAGE(IF('Cnty Effect'!$A$2:$A$10000=NetWeatherResidualLookup!A2,'Cnty
Effect'!$C$2:$C$10000,""))"




Here is the code:

Set myrange = mybook.Worksheets("NetWeatherResidualLookup").Range_("B2:B3433")
averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Range("B2:B3433").Formula
myrange.FormulaArray = averageifformula

Application.DisplayAlerts = False

mybook.Close ([True])


Basically it is copying the formulas from the basebook and pasting them into
mybook. The mybook is all of the workbooks in a folder. Can someone help me
with this? Right now it takes forever to paste the range and when I do, my
relative reference in the equation is even. Instead of it referencing cell
A1, A2 and A3, it is A2, A4 and A6. Any help would be greatly appreciated.

 

[Bob Phillips]

Use FormulaArray not Formula, and embedded quotes within the formula string
have to be doubled up

"=AVERAGE(IF('Cnty Effect'!$A$2:$A$10000=NetWeatherResidualLookup!A2,'Cnty
Effect'!$C$2:$C$10000,""""))"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob the equation is the equation that is on the sheet. It's not actually in
the vba. I call the function with [averageifformula =

basebook.Worksheet_("NetWeatherResidualLookup").Range("B2:B3433").Formula]. It is saying that it is pulling the formula out. If I put formularray instead of formula nothing gets pasted. When I use [myrange.FormulaArray = averageifformula] I am turning the formulas into array formulas in my worksheet. Thanks

Use FormulaArray not Formula, and embedded quotes within the formula string
have to be doubled up

"=AVERAGE(IF('Cnty Effect'!$A$2:$A$10000=NetWeatherResidualLookup!A2,'Cnty
Effect'!$C$2:$C$10000,""""))"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I have a workbook with a range of array formulas or CSE formulas. They are
essentially averageif formulas. They are available in 2007 but this has to
be
saved in xls format. So, I have done a CSE or Array formula with the
ctrl+shift+enter. My problem is copying and pasting my formulas into other
workbooks with vba and them keeping that format. Here is the part of my
code
where I try to alleviate that problem.


Here is my array formula that comes from
basebook.Worksheets("NetWeatherResidualLookup").Range("B2:B3433").Formula:

"=AVERAGE(IF('Cnty Effect'!$A$2:$A$10000=NetWeatherResidualLookup!A2,'Cnty
Effect'!$C$2:$C$10000,""))"




Here is the code:

Set myrange =
mybook.Worksheets("NetWeatherResidualLookup").Range_("B2:B3433")
averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Range("B2:B3433").Formula
myrange.FormulaArray = averageifformula

Application.DisplayAlerts = False

mybook.Close ([True])


Basically it is copying the formulas from the basebook and pasting them
into
mybook. The mybook is all of the workbooks in a folder. Can someone help
me
with this? Right now it takes forever to paste the range and when I do, my
relative reference in the equation is even. Instead of it referencing cell
A1, A2 and A3, it is A2, A4 and A6. Any help would be greatly appreciated.

 

[Guest]

Bob the equation is the equation that is on the sheet. It's not actually in
the vba. I call the function with [averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Range("B2:B3433").Formula].
It is saying that it is pulling the formula out. If I put formularray instead
of formula nothing gets pasted. When I use [myrange.FormulaArray =
averageifformula] I am turning the formulas into array formulas in my
worksheet. Thanks

Use FormulaArray not Formula, and embedded quotes within the formula string
have to be doubled up

"=AVERAGE(IF('Cnty Effect'!$A$2:$A$10000=NetWeatherResidualLookup!A2,'Cnty
Effect'!$C$2:$C$10000,""""))"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I have a workbook with a range of array formulas or CSE formulas. They are
essentially averageif formulas. They are available in 2007 but this has to
be
saved in xls format. So, I have done a CSE or Array formula with the
ctrl+shift+enter. My problem is copying and pasting my formulas into other
workbooks with vba and them keeping that format. Here is the part of my
code
where I try to alleviate that problem.


Here is my array formula that comes from
basebook.Worksheets("NetWeatherResidualLookup").Range("B2:B3433").Formula:

"=AVERAGE(IF('Cnty Effect'!$A$2:$A$10000=NetWeatherResidualLookup!A2,'Cnty
Effect'!$C$2:$C$10000,""))"




Here is the code:

Set myrange =
mybook.Worksheets("NetWeatherResidualLookup").Range_("B2:B3433")
averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Range("B2:B3433").Formula
myrange.FormulaArray = averageifformula

Application.DisplayAlerts = False

mybook.Close ([True])


Basically it is copying the formulas from the basebook and pasting them
into
mybook. The mybook is all of the workbooks in a folder. Can someone help
me
with this? Right now it takes forever to paste the range and when I do, my
relative reference in the equation is even. Instead of it referencing cell
A1, A2 and A3, it is A2, A4 and A6. Any help would be greatly appreciated.

 

[Guest]

Thanks Bob, but I figured it out. Appreciate the help.

Use FormulaArray not Formula, and embedded quotes within the formula string
have to be doubled up

"=AVERAGE(IF('Cnty Effect'!$A$2:$A$10000=NetWeatherResidualLookup!A2,'Cnty
Effect'!$C$2:$C$10000,""""))"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I have a workbook with a range of array formulas or CSE formulas. They are
essentially averageif formulas. They are available in 2007 but this has to
be
saved in xls format. So, I have done a CSE or Array formula with the
ctrl+shift+enter. My problem is copying and pasting my formulas into other
workbooks with vba and them keeping that format. Here is the part of my
code
where I try to alleviate that problem.


Here is my array formula that comes from
basebook.Worksheets("NetWeatherResidualLookup").Range("B2:B3433").Formula:

"=AVERAGE(IF('Cnty Effect'!$A$2:$A$10000=NetWeatherResidualLookup!A2,'Cnty
Effect'!$C$2:$C$10000,""))"




Here is the code:

Set myrange =
mybook.Worksheets("NetWeatherResidualLookup").Range_("B2:B3433")
averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Range("B2:B3433").Formula
myrange.FormulaArray = averageifformula

Application.DisplayAlerts = False

mybook.Close ([True])


Basically it is copying the formulas from the basebook and pasting them
into
mybook. The mybook is all of the workbooks in a folder. Can someone help
me
with this? Right now it takes forever to paste the range and when I do, my
relative reference in the equation is even. Instead of it referencing cell
A1, A2 and A3, it is A2, A4 and A6. Any help would be greatly appreciated.