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!" <(E-Mail Removed)> wrote in message
> news:401B67BD-2F08-4EE4-9939-(E-Mail Removed)...
> >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.
>
>
>
|
Similar Threads
