Cell 255 character limit & VBA

J

JEff

I need to build a calculation that refers to values from 7 different sheets.
But when I use the formula I get an out of memory error due to the length of
the network path.

Any ideas
Arr = Array("[29382_F03Q1LE_CIS_Ph1&2 Historical & R1A.xls]", "[70173_PMO
F05_CIS x.xls]", "[70174_R1B F05 LE_CIS x.xls]", "[70175_R2 Disputes F05
LE_CIS x.xls]", "[70176_R2 IVR F05 LE_CIS x.xls]", _
"[70177_R2 Marketing F05 LE_CIS.xls]", "[70178_R3_F05_CIS.xls]")
Arr1 = Array("29382 Historical & R1A Reconciliation", "70173 PMO
Reconciliation", "70174 R1B Reconciliation", _
"70175 Disputes Reconciliation", "70176 IVR Reconciliation", "70177
Marketing Reconciliation", "70178 Release 3 Reconciliation")

MyPath = "'\\v1sacpdofc2\cpdprojects\CIS Financials\QLE\LE\F05 LEs\"
Actuals = Xlmonth2 & " " & XlYear & " - " & (Xlmonth3) & " Actuals wz " &
Xlmonth2 & " LE\"
FwdMth = xlmonth + 1

Worksheets("Project Plan05-06").Cells(60, CurMth + 1) = "=" & MyPath &
Actuals & Arr1(0) & "\" & Arr(0) & _
"Project Plan'!$W$60" & "+" & MyPath & Actuals & Arr1(1) & "\" & Arr(1) &
"Proj Plan'!E60" & _
"+" & MyPath & Actuals & Arr1(2) & "\" & Arr(2) & "Proj Plan'!E60" & "+" &
MyPath & Actuals & Arr1(3) & "\" & Arr(3) & "Proj Plan'!E60" & "+" & MyPath &
Actuals & Arr1(4) & "\" & Arr(4) & "Proj Plan'!E60" & "+" _
& MyPath & Actuals & Arr1(5) & "\" & Arr(5) & "Proj Plan'!E60" & "+" &
MyPath & Actuals & Arr1(6) & "\" & Arr(6) & "Proj Plan'!E60"
 
D

Dave Peterson

Divide and conquer.

Plop smaller portions of your formula into other cells (a hidden worksheet???).
Then use those cell references to build your result.


I need to build a calculation that refers to values from 7 different sheets.
But when I use the formula I get an out of memory error due to the length of
the network path.

Any ideas
Arr = Array("[29382_F03Q1LE_CIS_Ph1&2 Historical & R1A.xls]", "[70173_PMO
F05_CIS x.xls]", "[70174_R1B F05 LE_CIS x.xls]", "[70175_R2 Disputes F05
LE_CIS x.xls]", "[70176_R2 IVR F05 LE_CIS x.xls]", _
"[70177_R2 Marketing F05 LE_CIS.xls]", "[70178_R3_F05_CIS.xls]")
Arr1 = Array("29382 Historical & R1A Reconciliation", "70173 PMO
Reconciliation", "70174 R1B Reconciliation", _
"70175 Disputes Reconciliation", "70176 IVR Reconciliation", "70177
Marketing Reconciliation", "70178 Release 3 Reconciliation")

MyPath = "'\\v1sacpdofc2\cpdprojects\CIS Financials\QLE\LE\F05 LEs\"
Actuals = Xlmonth2 & " " & XlYear & " - " & (Xlmonth3) & " Actuals wz " &
Xlmonth2 & " LE\"
FwdMth = xlmonth + 1

Worksheets("Project Plan05-06").Cells(60, CurMth + 1) = "=" & MyPath &
Actuals & Arr1(0) & "\" & Arr(0) & _
"Project Plan'!$W$60" & "+" & MyPath & Actuals & Arr1(1) & "\" & Arr(1) &
"Proj Plan'!E60" & _
"+" & MyPath & Actuals & Arr1(2) & "\" & Arr(2) & "Proj Plan'!E60" & "+" &
MyPath & Actuals & Arr1(3) & "\" & Arr(3) & "Proj Plan'!E60" & "+" & MyPath &
Actuals & Arr1(4) & "\" & Arr(4) & "Proj Plan'!E60" & "+" _
& MyPath & Actuals & Arr1(5) & "\" & Arr(5) & "Proj Plan'!E60" & "+" &
MyPath & Actuals & Arr1(6) & "\" & Arr(6) & "Proj Plan'!E60"
Click to expand...
 
D

Dave Peterson

PS. The limit is 1024 characters in a formula (when measured in R1C1 reference
style).

Array formulas do have a limit (255??) characters when passing back from VBA to
the cell.
I need to build a calculation that refers to values from 7 different sheets.
But when I use the formula I get an out of memory error due to the length of
the network path.

Any ideas
Arr = Array("[29382_F03Q1LE_CIS_Ph1&2 Historical & R1A.xls]", "[70173_PMO
F05_CIS x.xls]", "[70174_R1B F05 LE_CIS x.xls]", "[70175_R2 Disputes F05
LE_CIS x.xls]", "[70176_R2 IVR F05 LE_CIS x.xls]", _
"[70177_R2 Marketing F05 LE_CIS.xls]", "[70178_R3_F05_CIS.xls]")
Arr1 = Array("29382 Historical & R1A Reconciliation", "70173 PMO
Reconciliation", "70174 R1B Reconciliation", _
"70175 Disputes Reconciliation", "70176 IVR Reconciliation", "70177
Marketing Reconciliation", "70178 Release 3 Reconciliation")

MyPath = "'\\v1sacpdofc2\cpdprojects\CIS Financials\QLE\LE\F05 LEs\"
Actuals = Xlmonth2 & " " & XlYear & " - " & (Xlmonth3) & " Actuals wz " &
Xlmonth2 & " LE\"
FwdMth = xlmonth + 1

Worksheets("Project Plan05-06").Cells(60, CurMth + 1) = "=" & MyPath &
Actuals & Arr1(0) & "\" & Arr(0) & _
"Project Plan'!$W$60" & "+" & MyPath & Actuals & Arr1(1) & "\" & Arr(1) &
"Proj Plan'!E60" & _
"+" & MyPath & Actuals & Arr1(2) & "\" & Arr(2) & "Proj Plan'!E60" & "+" &
MyPath & Actuals & Arr1(3) & "\" & Arr(3) & "Proj Plan'!E60" & "+" & MyPath &
Actuals & Arr1(4) & "\" & Arr(4) & "Proj Plan'!E60" & "+" _
& MyPath & Actuals & Arr1(5) & "\" & Arr(5) & "Proj Plan'!E60" & "+" &
MyPath & Actuals & Arr1(6) & "\" & Arr(6) & "Proj Plan'!E60"
Click to expand...
 
Top