Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
I am receiving a Run-time error "1004": unable to set the FormulaArray
property of the Range class. Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3 included that fix that was in SP2? If so, why am I still receiving the error and the formula does not work correctly. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
It might help if you show us your code
Rick "sharon t" wrote in message ... I am receiving a Run-time error "1004": unable to set the FormulaArray property of the Range class. Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3 included that fix that was in SP2? If so, why am I still receiving the error and the formula does not work correctly. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
=SUM(IF('Grand Total-All Detail'!$B$3:$B$14="Jane",IF('Grand Total-All
Detail'!$A$3:$A$14="Doe",IF('Grand Total-All Detail'!$C$3:$C$14=2007,'Grand Total-All Detail'!$L$3:$L$14,0),0),0)) The "Grand Total-All Detail" is one of many spreadsheets in the workbook and am writing the array formula in another "summary" spreadsheet. It will work if I enter on same spreadsheet but don't want it there. Have always been able to write this formula to show summaries on a separate spreadsheet within the same workbook. "Rick Rothstein (MVP - VB)" wrote: It might help if you show us your code Rick "sharon t" wrote in message ... I am receiving a Run-time error "1004": unable to set the FormulaArray property of the Range class. Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3 included that fix that was in SP2? If so, why am I still receiving the error and the formula does not work correctly. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
Hopefully someone more knowledgeable about this stuff will come along to
help you; however, that formula appears to work correctly on my system. I'm not sure if this will help you to focus in on the problem or not; but I do note that the original error message you reported, namely... "Run-time error "1004": unable to set the FormulaArray property of the Range class" looks like a VBA error message (from, say, a macro or UserForm code module) and not a worksheet error message. Do you have any VBA code running that is trying to set a FormulaArray property of a range (perhaps one that is overlapping the cell(s) you are putting your formula into)? Rick "sharon t" wrote in message ... =SUM(IF('Grand Total-All Detail'!$B$3:$B$14="Jane",IF('Grand Total-All Detail'!$A$3:$A$14="Doe",IF('Grand Total-All Detail'!$C$3:$C$14=2007,'Grand Total-All Detail'!$L$3:$L$14,0),0),0)) The "Grand Total-All Detail" is one of many spreadsheets in the workbook and am writing the array formula in another "summary" spreadsheet. It will work if I enter on same spreadsheet but don't want it there. Have always been able to write this formula to show summaries on a separate spreadsheet within the same workbook. "Rick Rothstein (MVP - VB)" wrote: It might help if you show us your code Rick "sharon t" wrote in message ... I am receiving a Run-time error "1004": unable to set the FormulaArray property of the Range class. Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3 included that fix that was in SP2? If so, why am I still receiving the error and the formula does not work correctly. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
No VBA code running which is trying to set a FormulaArray property of a
range. The fix is out under Help and support, article ID 885245. States SP2 fixes this error. I have SP3 loaded since updated computer a couple weeks ago and now receiving this error. I would think SP3 would contain all fixes included in SP2 but doesn't appear to be the case. Never had the problem prior to upgrade. I use the conditional sum array formula all the time-never had a problem prior to this. "Rick Rothstein (MVP - VB)" wrote: Hopefully someone more knowledgeable about this stuff will come along to help you; however, that formula appears to work correctly on my system. I'm not sure if this will help you to focus in on the problem or not; but I do note that the original error message you reported, namely... "Run-time error "1004": unable to set the FormulaArray property of the Range class" looks like a VBA error message (from, say, a macro or UserForm code module) and not a worksheet error message. Do you have any VBA code running that is trying to set a FormulaArray property of a range (perhaps one that is overlapping the cell(s) you are putting your formula into)? Rick "sharon t" wrote in message ... =SUM(IF('Grand Total-All Detail'!$B$3:$B$14="Jane",IF('Grand Total-All Detail'!$A$3:$A$14="Doe",IF('Grand Total-All Detail'!$C$3:$C$14=2007,'Grand Total-All Detail'!$L$3:$L$14,0),0),0)) The "Grand Total-All Detail" is one of many spreadsheets in the workbook and am writing the array formula in another "summary" spreadsheet. It will work if I enter on same spreadsheet but don't want it there. Have always been able to write this formula to show summaries on a separate spreadsheet within the same workbook. "Rick Rothstein (MVP - VB)" wrote: It might help if you show us your code Rick "sharon t" wrote in message ... I am receiving a Run-time error "1004": unable to set the FormulaArray property of the Range class. Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3 included that fix that was in SP2? If so, why am I still receiving the error and the formula does not work correctly. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
According to the download page for SP3, it has SP2 fixes included (as well
as SP1 also). I don't know if this has anything to do with your problem or not, but putting "Features that are unavailable in shared workbooks" in the Help Search field in the worksheet window and clicking that same article title says that for shared workbooks, you cannot "change or delete array formulas" but that "existing array formulas continue to calculate correctly". I also did a quick Google search and found a reference to that same error (but from a "hiding" issue) and the solution involved removing a Comment that was attached to one of the cells. Just figured I would mention these "in case". Rick "sharon t" wrote in message ... No VBA code running which is trying to set a FormulaArray property of a range. The fix is out under Help and support, article ID 885245. States SP2 fixes this error. I have SP3 loaded since updated computer a couple weeks ago and now receiving this error. I would think SP3 would contain all fixes included in SP2 but doesn't appear to be the case. Never had the problem prior to upgrade. I use the conditional sum array formula all the time-never had a problem prior to this. "Rick Rothstein (MVP - VB)" wrote: Hopefully someone more knowledgeable about this stuff will come along to help you; however, that formula appears to work correctly on my system. I'm not sure if this will help you to focus in on the problem or not; but I do note that the original error message you reported, namely... "Run-time error "1004": unable to set the FormulaArray property of the Range class" looks like a VBA error message (from, say, a macro or UserForm code module) and not a worksheet error message. Do you have any VBA code running that is trying to set a FormulaArray property of a range (perhaps one that is overlapping the cell(s) you are putting your formula into)? Rick "sharon t" wrote in message ... =SUM(IF('Grand Total-All Detail'!$B$3:$B$14="Jane",IF('Grand Total-All Detail'!$A$3:$A$14="Doe",IF('Grand Total-All Detail'!$C$3:$C$14=2007,'Grand Total-All Detail'!$L$3:$L$14,0),0),0)) The "Grand Total-All Detail" is one of many spreadsheets in the workbook and am writing the array formula in another "summary" spreadsheet. It will work if I enter on same spreadsheet but don't want it there. Have always been able to write this formula to show summaries on a separate spreadsheet within the same workbook. "Rick Rothstein (MVP - VB)" wrote: It might help if you show us your code Rick "sharon t" wrote in message ... I am receiving a Run-time error "1004": unable to set the FormulaArray property of the Range class. Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3 included that fix that was in SP2? If so, why am I still receiving the error and the formula does not work correctly. Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
Thanks for the info. Not a shared workbook and no comments in any of the
cells. It is a "monster" workbook, 24 spreadsheets in it, thousands of links and full of array formulas. Everything worked fine until I had my computer upgraded, they downloaded SP3 (the fix was in SP2) and now I'm getting the error. It will work if I stay of the spreadsheet where all the data is but when trying to use the conditional sum to enter the info on the summary sheet, it gives me the error. "Rick Rothstein (MVP - VB)" wrote: According to the download page for SP3, it has SP2 fixes included (as well as SP1 also). I don't know if this has anything to do with your problem or not, but putting "Features that are unavailable in shared workbooks" in the Help Search field in the worksheet window and clicking that same article title says that for shared workbooks, you cannot "change or delete array formulas" but that "existing array formulas continue to calculate correctly". I also did a quick Google search and found a reference to that same error (but from a "hiding" issue) and the solution involved removing a Comment that was attached to one of the cells. Just figured I would mention these "in case". Rick "sharon t" wrote in message ... No VBA code running which is trying to set a FormulaArray property of a range. The fix is out under Help and support, article ID 885245. States SP2 fixes this error. I have SP3 loaded since updated computer a couple weeks ago and now receiving this error. I would think SP3 would contain all fixes included in SP2 but doesn't appear to be the case. Never had the problem prior to upgrade. I use the conditional sum array formula all the time-never had a problem prior to this. "Rick Rothstein (MVP - VB)" wrote: Hopefully someone more knowledgeable about this stuff will come along to help you; however, that formula appears to work correctly on my system. I'm not sure if this will help you to focus in on the problem or not; but I do note that the original error message you reported, namely... "Run-time error "1004": unable to set the FormulaArray property of the Range class" looks like a VBA error message (from, say, a macro or UserForm code module) and not a worksheet error message. Do you have any VBA code running that is trying to set a FormulaArray property of a range (perhaps one that is overlapping the cell(s) you are putting your formula into)? Rick "sharon t" wrote in message ... =SUM(IF('Grand Total-All Detail'!$B$3:$B$14="Jane",IF('Grand Total-All Detail'!$A$3:$A$14="Doe",IF('Grand Total-All Detail'!$C$3:$C$14=2007,'Grand Total-All Detail'!$L$3:$L$14,0),0),0)) The "Grand Total-All Detail" is one of many spreadsheets in the workbook and am writing the array formula in another "summary" spreadsheet. It will work if I enter on same spreadsheet but don't want it there. Have always been able to write this formula to show summaries on a separate spreadsheet within the same workbook. "Rick Rothstein (MVP - VB)" wrote: It might help if you show us your code Rick "sharon t" wrote in message ... I am receiving a Run-time error "1004": unable to set the FormulaArray property of the Range class. Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3 included that fix that was in SP2? If so, why am I still receiving the error and the formula does not work correctly. Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
Rick knows more about VBA than I do but the error message you're getting is
not one that you should get by typing/entering a formula in a cell. when trying to use the conditional sum to enter the info on the summary sheet, it gives me the error. Does that mean you're using the conditional sum wizzard? What happens if you don't use the conditional sum wizzard and just type the formula in? Also, this can be done with a non-array formula (normally entered): =SUMPRODUCT(--('Grand Total-All Detail'!$B$3:$B$14="Jane"),--('Grand Total-All Detail'!$A$3:$A$14="Doe"),--('Grand Total-All Detail'!$C$3:$C$14=2007),'Grand Total-All Detail'!$L$3:$L$14) -- Biff Microsoft Excel MVP "sharon t" wrote in message ... Thanks for the info. Not a shared workbook and no comments in any of the cells. It is a "monster" workbook, 24 spreadsheets in it, thousands of links and full of array formulas. Everything worked fine until I had my computer upgraded, they downloaded SP3 (the fix was in SP2) and now I'm getting the error. It will work if I stay of the spreadsheet where all the data is but when trying to use the conditional sum to enter the info on the summary sheet, it gives me the error. "Rick Rothstein (MVP - VB)" wrote: According to the download page for SP3, it has SP2 fixes included (as well as SP1 also). I don't know if this has anything to do with your problem or not, but putting "Features that are unavailable in shared workbooks" in the Help Search field in the worksheet window and clicking that same article title says that for shared workbooks, you cannot "change or delete array formulas" but that "existing array formulas continue to calculate correctly". I also did a quick Google search and found a reference to that same error (but from a "hiding" issue) and the solution involved removing a Comment that was attached to one of the cells. Just figured I would mention these "in case". Rick "sharon t" wrote in message ... No VBA code running which is trying to set a FormulaArray property of a range. The fix is out under Help and support, article ID 885245. States SP2 fixes this error. I have SP3 loaded since updated computer a couple weeks ago and now receiving this error. I would think SP3 would contain all fixes included in SP2 but doesn't appear to be the case. Never had the problem prior to upgrade. I use the conditional sum array formula all the time-never had a problem prior to this. "Rick Rothstein (MVP - VB)" wrote: Hopefully someone more knowledgeable about this stuff will come along to help you; however, that formula appears to work correctly on my system. I'm not sure if this will help you to focus in on the problem or not; but I do note that the original error message you reported, namely... "Run-time error "1004": unable to set the FormulaArray property of the Range class" looks like a VBA error message (from, say, a macro or UserForm code module) and not a worksheet error message. Do you have any VBA code running that is trying to set a FormulaArray property of a range (perhaps one that is overlapping the cell(s) you are putting your formula into)? Rick "sharon t" wrote in message ... =SUM(IF('Grand Total-All Detail'!$B$3:$B$14="Jane",IF('Grand Total-All Detail'!$A$3:$A$14="Doe",IF('Grand Total-All Detail'!$C$3:$C$14=2007,'Grand Total-All Detail'!$L$3:$L$14,0),0),0)) The "Grand Total-All Detail" is one of many spreadsheets in the workbook and am writing the array formula in another "summary" spreadsheet. It will work if I enter on same spreadsheet but don't want it there. Have always been able to write this formula to show summaries on a separate spreadsheet within the same workbook. "Rick Rothstein (MVP - VB)" wrote: It might help if you show us your code Rick "sharon t" wrote in message ... I am receiving a Run-time error "1004": unable to set the FormulaArray property of the Range class. Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3 included that fix that was in SP2? If so, why am I still receiving the error and the formula does not work correctly. Thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
I have tried both with the conditional sum wizzard and entering the actual
formula in. It's looking more and more like the file may be corrupted as I can enter this into a blank, new workbook and it works fine. This all happened when SP3 was loaded on my computer so don't know if that is what caused the problem or not. Workbook worked fine up until that point. Getting it restored and will re-write the last three spreadsheets within the workbook. Thanks to all who gave assistance. "T. Valko" wrote: Rick knows more about VBA than I do but the error message you're getting is not one that you should get by typing/entering a formula in a cell. when trying to use the conditional sum to enter the info on the summary sheet, it gives me the error. Does that mean you're using the conditional sum wizzard? What happens if you don't use the conditional sum wizzard and just type the formula in? Also, this can be done with a non-array formula (normally entered): =SUMPRODUCT(--('Grand Total-All Detail'!$B$3:$B$14="Jane"),--('Grand Total-All Detail'!$A$3:$A$14="Doe"),--('Grand Total-All Detail'!$C$3:$C$14=2007),'Grand Total-All Detail'!$L$3:$L$14) -- Biff Microsoft Excel MVP "sharon t" wrote in message ... Thanks for the info. Not a shared workbook and no comments in any of the cells. It is a "monster" workbook, 24 spreadsheets in it, thousands of links and full of array formulas. Everything worked fine until I had my computer upgraded, they downloaded SP3 (the fix was in SP2) and now I'm getting the error. It will work if I stay of the spreadsheet where all the data is but when trying to use the conditional sum to enter the info on the summary sheet, it gives me the error. "Rick Rothstein (MVP - VB)" wrote: According to the download page for SP3, it has SP2 fixes included (as well as SP1 also). I don't know if this has anything to do with your problem or not, but putting "Features that are unavailable in shared workbooks" in the Help Search field in the worksheet window and clicking that same article title says that for shared workbooks, you cannot "change or delete array formulas" but that "existing array formulas continue to calculate correctly". I also did a quick Google search and found a reference to that same error (but from a "hiding" issue) and the solution involved removing a Comment that was attached to one of the cells. Just figured I would mention these "in case". Rick "sharon t" wrote in message ... No VBA code running which is trying to set a FormulaArray property of a range. The fix is out under Help and support, article ID 885245. States SP2 fixes this error. I have SP3 loaded since updated computer a couple weeks ago and now receiving this error. I would think SP3 would contain all fixes included in SP2 but doesn't appear to be the case. Never had the problem prior to upgrade. I use the conditional sum array formula all the time-never had a problem prior to this. "Rick Rothstein (MVP - VB)" wrote: Hopefully someone more knowledgeable about this stuff will come along to help you; however, that formula appears to work correctly on my system. I'm not sure if this will help you to focus in on the problem or not; but I do note that the original error message you reported, namely... "Run-time error "1004": unable to set the FormulaArray property of the Range class" looks like a VBA error message (from, say, a macro or UserForm code module) and not a worksheet error message. Do you have any VBA code running that is trying to set a FormulaArray property of a range (perhaps one that is overlapping the cell(s) you are putting your formula into)? Rick "sharon t" wrote in message ... =SUM(IF('Grand Total-All Detail'!$B$3:$B$14="Jane",IF('Grand Total-All Detail'!$A$3:$A$14="Doe",IF('Grand Total-All Detail'!$C$3:$C$14=2007,'Grand Total-All Detail'!$L$3:$L$14,0),0),0)) The "Grand Total-All Detail" is one of many spreadsheets in the workbook and am writing the array formula in another "summary" spreadsheet. It will work if I enter on same spreadsheet but don't want it there. Have always been able to write this formula to show summaries on a separate spreadsheet within the same workbook. "Rick Rothstein (MVP - VB)" wrote: It might help if you show us your code Rick "sharon t" wrote in message ... I am receiving a Run-time error "1004": unable to set the FormulaArray property of the Range class. Stated fix is to install SP2. I have SP3 installed. Does anyone know if SP3 included that fix that was in SP2? If so, why am I still receiving the error and the formula does not work correctly. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array formula | Excel Worksheet Functions | |||
Array Formula | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Array Formula | Excel Worksheet Functions |