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").Ra nge("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").Rang e_("B2:B3433")
averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Ra nge("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.

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)

"Need Help Fast!" wrote in message
...
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").Ra nge("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").Rang e_("B2:B3433")
averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Ra nge("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 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").Ra nge("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



"Bob Phillips" wrote:

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)

"Need Help Fast!" wrote in message
...
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").Ra nge("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").Rang e_("B2:B3433")
averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Ra nge("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 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").Ra nge("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



"Bob Phillips" wrote:

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)

"Need Help Fast!" wrote in message
...
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").Ra nge("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").Rang e_("B2:B3433")
averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Ra nge("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.

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

"Bob Phillips" wrote:

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)

"Need Help Fast!" wrote in message
...
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").Ra nge("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").Rang e_("B2:B3433")
averageifformula =
basebook.Worksheet_("NetWeatherResidualLookup").Ra nge("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.