Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Unable to set FormulaArray property of the Range class
I am trying to update cell formulas (R1C1) using the FormulaArray
property and I noticed that if the cell formulas contains more than one references to external workbooks, I will get this "Unable to set FormulaArray property of the Range class" error message. For example: Suppose I want to update cells A1 and C3 with the following formulas respectively: ='\\someserver\somewhere\[file_1.xls]Sheet1'!R1C1 ='\\someserver\somewhere\[file_1.xls]Sheet1'!R3C3 The following code will work and the formulas will be inserted into cells A1 and A3. Dim strFormulas() as String Redim strFormulas(1 to 3, 1 to 3) Range(Cells(1,1), Cells(1,3).FormulaArray = strFormulas However, if I use a formula such as the following that has more than one external references, I will get an error message. ='\\someserver\somewhere\[file_1.xls]Sheet1'!R1C1+'\\someserver\somewhere\[file_1.xls]Sheet1'!R10C10 It looks to me this is some kind of bug in Excel VBA because the formulas I'm trying to insert is valid and all external references are fine. It would be greatly appreciated if anyone can provide a solution to this issue. Thanks. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Unable to set FormulaArray property of the Range class
Your string is 100 characters, so I assume it is only representative of the
string you are trying to use. FormulaArray will only accept a string of 255 characters - so perhaps that is your problem. I haven't tried it, but put in a dummy sheet named AB C (with a space) then put in your array formula in code using something like "='AB C'!R1C1+'AB C'!R10C10" then in your code use the range Find method to replace AB C with the external file argument \\someserver\somewhere\[file_1.xls]Sheet1 -- Regards, Tom Ogilvy wrote in message ... I am trying to update cell formulas (R1C1) using the FormulaArray property and I noticed that if the cell formulas contains more than one references to external workbooks, I will get this "Unable to set FormulaArray property of the Range class" error message. For example: Suppose I want to update cells A1 and C3 with the following formulas respectively: ='\\someserver\somewhere\[file_1.xls]Sheet1'!R1C1 ='\\someserver\somewhere\[file_1.xls]Sheet1'!R3C3 The following code will work and the formulas will be inserted into cells A1 and A3. Dim strFormulas() as String Redim strFormulas(1 to 3, 1 to 3) Range(Cells(1,1), Cells(1,3).FormulaArray = strFormulas However, if I use a formula such as the following that has more than one external references, I will get an error message. ='\\someserver\somewhere\[file_1.xls]Sheet1'!R1C1+'\\someserver\somewhere\[f ile_1.xls]Sheet1'!R10C10 It looks to me this is some kind of bug in Excel VBA because the formulas I'm trying to insert is valid and all external references are fine. It would be greatly appreciated if anyone can provide a solution to this issue. Thanks. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Unable to set FormulaArray property of the Range class
On Mon, 17 Oct 2005 15:08:45 -0400, "Tom Ogilvy"
wrote: Your string is 100 characters, so I assume it is only representative of the string you are trying to use. FormulaArray will only accept a string of 255 characters - so perhaps that is your problem. I haven't tried it, but put in a dummy sheet named AB C (with a space) then put in your array formula in code using something like "='AB C'!R1C1+'AB C'!R10C10" then in your code use the range Find method to replace AB C with the external file argument \\someserver\somewhere\[file_1.xls]Sheet1 Thanks for the quick reply. And you are right about the maximum string length. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to set the FormulaArrary property of the range class | Excel Worksheet Functions | |||
unable to set the FormulaArray property of the Range class | Excel Programming | |||
Unable to set the Locked property of the range class | Excel Programming | |||
unable to set formulaarray of range class ERROR when using Conditional Sum | Excel Programming | |||
Unable to set the Locked Property of the Range Class | Excel Programming |