cannot set range class?

[Steve]

Howdee all.
I've made a user form to input a couple of worksheet functions, and have run
across a problem that doesn't show up on other workbooks.

I get a runtime error message- 1004-- stating: Unable to set the
FormulaArray property of the Range class.

When I had this earlier today I'd found it was due to my not having set the
..address() components of the range.
However, I'd fixed that, and it works on another workbook just fine.
I then set a message box to see what the equation was outputting, and all I
got was a 'false.'
I.e.,
msgbox myrng3.formula = myformula2, vbokonly, "test"

I'm unable to find anything that'd keep it from working. I did notice that
it did not place the single quote mark around the worksheet name, as is
standard. I tried placing a chr(39) in there, and it threw yet another error,
so I removed it.

The only difference I can identify is that the worksheet's name has a number
on the end, where it's not working.
SheetA, as opposed to SheetA_081

Here's my formula, and set for the FormulaArray.
myFormula2 = "=IF(ROWS(" & MyRng3.Address(RowAbsolute:=True,
ColumnAbsolute:=True, _
external:=False) & ":" & MyRng3.Address(RowAbsolute:=False,
ColumnAbsolute:=True, _
external:=False) & ")>" & MyRng2.Address(RowAbsolute:=False,
ColumnAbsolute:=True, _
external:=False) & "," & Chr(34) & "All Names Accounted For" & Chr(34) &
",INDEX(" _
& MyRng.Address(RowAbsolute:=True, ColumnAbsolute:=True, external:=True) & _
",SMALL(IF(ISNA(MATCH(" & MyRng.Address(RowAbsolute:=True,
ColumnAbsolute:=True, external:=True) _
& "," & MyRngA.Address(RowAbsolute:=True, ColumnAbsolute:=True,
external:=False) & ",0)),ROW(" _
& MyRng.Address(RowAbsolute:=True, ColumnAbsolute:=True, external:=True) & _
")),ROWS(" & MyRng3.Address(RowAbsolute:=True, ColumnAbsolute:=True,
external:=False) _
& ":" & MyRng3.Address(RowAbsolute:=False, ColumnAbsolute:=True,
external:=False) & _
"))-MIN(ROW(" & MyRng.Address(RowAbsolute:=True, ColumnAbsolute:=True,
external:=True) & "))+1))"

MyRng3.FormulaArray = myFormula2

If the rest of the form's code is needed, please let me know.

Thank you.
Best.

 

[Peter T]

Probably don't need all the form code but post enough for others to attempt
to reproduce what you have. Eg, that formula sting ends up pasted as 20 red
lines in the VBE, very tedious to sort out. Try ensuring each line is less
than 76 characters when posted to avoid wrapping. Give an example of the
address of MyRng2 and any sheet names, with a view to being able to return a
string formula to examine for problems.

Regards,
Peter T

 

[Steve]

Morning Peter. Of course, it appears to now be much later in your day.
Ok, ensure the formula string doesn't get too wrapped.

A sample of the MyRng address is ShtNm!$F$5:$F$100
MyRngA address is $C$10:$C45
MyRng2 address is the base location of the first equation for this routine-
$G$10:$G10
MyRng3 address is the location of this specific equation that's giving me
trouble. $F10

ShtNm! is a generic worksheet name from which my source data is obtained.
the rest of the addresses are on the same sheet as the locations are being
placed, so the sheet name it irrelevant.

I did just try my test file and the problem is now occurring there as well
(yesterday it was working fine until I tested it on a "real" workbook).

The code for my form/routin
-------------------------code--------------------------------------------------
Sub NameCatcher()
Dim MyRng, MyRngA, MyRng1, MyRng2, MyRng3, MyCell1, MyCell2 As Range
Dim myFormula1, myFormula2 As String

Set MyRng1 = Nothing

Set MyRng1 = Range(RefEdit3.Text)

MyRng1.Select

ActiveCell.FormulaR1C1 = "Count"

Union(ActiveCell, ActiveCell.Offset(-1, 0)).MergeCells = True


ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "Missing Name(s)"
Union(ActiveCell, ActiveCell.Offset(1, 0)).MergeCells = True


'For the setting up the Equations
'------------------------------------
'APN List selection

Set MyRng = Nothing

Set MyRng = Range(RefEdit1.Text)

'------------------------------------
'Sum List Selection
Set MyRngA = Nothing

Set MyRngA = Range(RefEdit2.Text)
'-----------------------------------
'select placement of connection for Eq1 to Eq2
'=============================================================
'SumPgSelection
Set MyRng2 = Nothing

Set MyRng2 = Range(RefEdit3.Text).Offset(1, 0)
'not sure if MyRng2.select is necessary or not.
'doesn't seem to matter if commented out.
MyRng2.Select

'select placement of connection for Eq1 to Eq2
'=============================================
'APNPgSelection
Set MyRng3 = Nothing

Set MyRng3 = Range(RefEdit3.Text).Offset(1, 1)
'not sure if MyRng3.select is necessary or not.
'doesn't seem to matter if commented out.
MyRng3.Select
'==============================================
'Equation 1
'---{=SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))}
'SubRng1 is the APN name list, which should be set to MyRng
'Rng2 is the Sum name list, which should be set to MyrngA
'Eq1 good.
myFormula1 = "=SUMPRODUCT(--(ISNA(MATCH(" & MyRng.Address _
(RowAbsolute:=True, ColumnAbsolute:=True, external:=True) & "," & _
MyRngA.Address(RowAbsolute:=True, ColumnAbsolute:=True, _
external:=False) & ",0))))"
'-------------------------------------------
'Equation 2

'{=IF(ROWS(D$22)>C$2,"",INDEX(SubRng1,SMALL(IF(ISNA(MATCH
'(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$22))-MIN(ROW(SubRng1))+1))}
'SubRng1 is APN name list, which should be set to MyRng
'Rng2 is Sum name list, which should be set to MyrngA
'D2, and C2 ranges are changed to MyRng3, and MyRng2,
'respectively.


myFormula2 = "=IF(ROWS(" & MyRng3.Address(RowAbsolute:=True, _
ColumnAbsolute:=True, external:=False) & ":" & MyRng3.Address
_(RowAbsolute:=False, ColumnAbsolute:=True, external:=False) & _
")>" & MyRng2.Address (RowAbsolute:=False, ColumnAbsolute:=True, _
external:=False) & "," & Chr(34) & "All Names Accounted For" & Chr(34) _
& ",INDEX(" & MyRng.Address(RowAbsolute:=True, _
ColumnAbsolute:=True, external:=True) & ",SMALL(IF(ISNA(MATCH(" & _
MyRng.Address (RowAbsolute:=True, ColumnAbsolute:=True, _
external:=True) & "," & MyRngA.Address(RowAbsolute:=True, _
ColumnAbsolute:=True, external:=False) & ",0)),ROW(" & _
MyRng.Address(RowAbsolute:=True, ColumnAbsolute:=True, _
external:=True) & ")),ROWS(" & MyRng3.Address(RowAbsolute:=True, _
ColumnAbsolute:=True, external:=False) & ":" & MyRng3.Address
_(RowAbsolute:=False, ColumnAbsolute:=True, external:=False) _
& "))-MIN(ROW(" & MyRng.Address(RowAbsolute:=True, _
ColumnAbsolute:=True, external:=True) & "))+1))"

MyRng2.FormulaArray = myFormula1
'MsgBox MyRng3.Formula = myFormula2, vbOKOnly, "Test"

MyRng3.FormulaArray = myFormula2

me.hide

end sub
----------------------------end
code--------------------------------------------

This is pretty much the core of the routine (I've only removed
finish/appearance formatting code from the body to post here). I tried
shortening each line of the equation so that it won't wrap, or red-code if
copied.

Again, the issue that I'm facing is that with the second equation I get a
runtime error message- 1004-- stating: Unable to set the FormulaArray
property of the Range class.

It was working fine until I tried a "live" workbook, and then the issue
started. I then tried my test book again and it's now having the same issue.

Thank you for your helps.
Best.
Steve

 

[Steve]

Ok.... further workings....
I took out the Array for the FomulaArray and just made it
MyRng3.Formula = myFormula2

This does not throw the error. So, if I'm understanding this correctly, the
issue is that the command
..FormulaArray
is the issue.

What would cause this function to not work correctly, or not be set?

Thank you.

 

[Steve]

Ok, did some more digging.
I found a post done by Jim Rech back in August that states there is a 255
character limit to FormulaArray's.
http://support.microsoft.com/kb/213181

I know for a fact that my VBA formula is 946 characters (including spaces,
line separators, etc...).
I checked the finished, active formula and it's only 187 characters.

So, my question is-- which one is the one that Excel VBA sees when it does
the limitations?

This was in fact the very first thing I'd checked, because I know that Excel
has a 255 character limit for each cell.

 

[Steve]

More digging.....
It appears that the equation includes the name of the workbook, instead of
just the sheet's name-- even though the name of the book doesn't appear in
the final equation.

This in fact would take it over the 255 character limit. 263 characters to
be exact.

Is there a way to prevent the workbook's name from being included in the
initial setup?

This is what appears in my message box.
=IF(ROWS(F$12:F12)>E$12,"All Names Accounted
For",INDEX([TestMeSample.xlsx]APN_081!B$3:B$8,SMALL(IF(ISNA(MATCH([TestMeSample.xlsx]APN_081!B$3:B$8,A$3:A$9,0)),ROW([TestMeSample.xlsx]APN_081!B$3:B$8)),ROWS(F$12:F12))-MIN(ROW([TestMeSample.xlsx]APN_081!B$3:B$8))+1))


This is what appears in the final equation in the cell.
=IF(ROWS(F$18:F18)>E$18,"All Names Accounted
For",INDEX(APN_081!B$3:B$8,SMALL(IF(ISNA(MATCH(APN_081!B$3:B$8,A$3:A$9,0)),ROW(APN_081!B$3:B$8)),ROWS(F$18:F18))-MIN(ROW(APN_081!B$3:B$8))+1))


Your helps are appreciated.

 

[Peter T]

As you've discovered formula arrays are limited to 255, though there is a
workaround -
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Your workbook names "[filename.xls]" are included in the string because of
the use of External:=True in the Address functions, you could remove them
like this

myFormula2 = Replace(myFormula2, MyRng.Parent.Parent.Name, "")
myFormula2 = Replace(myFormula2, MyRng2.Parent.Parent.Name, "")
myFormula2 = Replace(myFormula2, MyRng3.Parent.Parent.Name, "")
myFormula2 = Replace(myFormula2, MyRngA.Parent.Parent.Name, "")
myFormula2 = Replace(myFormula2, "[", "")
myFormula2 = Replace(myFormula2, "]", "")

In passing you don't need to use Select

Regards,
Peter T

 

[Steve]

Hi again Peter,
first, thank you for the article. I've printed it out, as well as bookmarked
it for future study.

I did finally recognize that the external:=true was the cause. I tried a
handful of my own workarounds, and they failed. Then, on the .Select, I'm
guessing you're referring to the two MyRng.select calls I did? I was looking
at them, and began wondering about that. They're now gone.

I guess I'm not entirely understanding the removal of the workbook file
names. It does not appear in the final equation. Only in the "intermediary"
name.

Would it not still view the intermediary and final equations the same?

Onto the crux.....
So, if I'm understanding this correctly, I take my VBA equation, truncate it
at anything less than what WOULD become the 255 character limit, and insert,
instead, a trigger element, to have more code replace it.
For the moment, I have each part split almost in half, so each part would
not exceed the 200 character mark, without the file already being incredibly
overwhelming/long name, long sheet names.

I'm working it, but it's still vague to me...
Again, thank you for your assistance/help.
Best.

As you've discovered formula arrays are limited to 255, though there is a
workaround -
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Your workbook names "[filename.xls]" are included in the string because of
the use of External:=True in the Address functions, you could remove them
like this

myFormula2 = Replace(myFormula2, MyRng.Parent.Parent.Name, "")
myFormula2 = Replace(myFormula2, MyRng2.Parent.Parent.Name, "")
myFormula2 = Replace(myFormula2, MyRng3.Parent.Parent.Name, "")
myFormula2 = Replace(myFormula2, MyRngA.Parent.Parent.Name, "")
myFormula2 = Replace(myFormula2, "[", "")
myFormula2 = Replace(myFormula2, "]", "")

In passing you don't need to use Select

Regards,
Peter T

More digging.....
It appears that the equation includes the name of the workbook, instead of
just the sheet's name-- even though the name of the book doesn't appear in
the final equation.

This in fact would take it over the 255 character limit. 263 characters to
be exact.

Is there a way to prevent the workbook's name from being included in the
initial setup?

This is what appears in my message box.

This is what appears in the final equation in the cell.
=IF(ROWS(F$18:F18)>E$18,"All Names Accounted
For",INDEX(APN_081!B$3:B$8,SMALL(IF(ISNA(MATCH(APN_081!B$3:B$8,A$3:A$9,0)),ROW(APN_081!B$3:B$8)),ROWS(F$18:F18))-MIN(ROW(APN_081!B$3:B$8))+1))


Your helps are appreciated.

.

 

[Peter T]

I guess I'm not entirely understanding the removal of the workbook file
names. It does not appear in the final equation. Only in the
"intermediary"
name.

Would it not still view the intermediary and final equations the same?

As discussed, the Address function with External:=True returns the fully
qualified address with sheet and file names. In a cell formula qualification
to sheet is only required where necessary to refer to a different sheet, and
similarly qualification to the file name if the reference is in another
workbook. An unnecessary qualification to the file-name entered into a
formula will get discarded.

Looking back at the code you posted you also gave these details
MyRng address is ShtNm!$F$5:$F$100
MyRngA address is $C$10:$C45
MyRng2 address is $G$10:$G10
MyRng3 address is $F10

If all the above ranges, except MyRng, are on the same sheet as the eventual
formula cell you don't need to use the External:=True argument in the
respective Address factions at all. If MyRng is on another sheet in the same
wb it will need the qualification to the sheet, but not the file-name.

Another way to get the address qualified to the sheet-name (but not file
name) is like this

sRef = "'" & MyRng.Parent.Name & "'!" & MyRng.Address

The embracing apostrophes are sometimes required depending on certain
characters in the sheet-name, eg a space, but will get discarded in the
formula if not required.

If you've got lots of ref's to the same sheet, simplify your VBA code with
something like this

sSht = "'" & MyRng.Parent.Name & "'!"
sMyRng = sSht & MyRng.Address

If you don't need the Absolute references you can avoid returning the $ to
reduce the formula a little like this -

MyRng.Address(0, 0)

If you can get the formula below 255 without resorting to the workaround
it'll mean it can subsequently be edited manually.

Onto the crux.....
So, if I'm understanding this correctly, I take my VBA equation, truncate
it
at anything less than what WOULD become the 255 character limit, and
insert,
instead, a trigger element, to have more code replace it.
For the moment, I have each part split almost in half, so each part would
not exceed the 200 character mark, without the file already being
incredibly
overwhelming/long name, long sheet names.

I'm working it, but it's still vague to me...
Again, thank you for your assistance/help.

Not quite sure what you mean here and the "trigger element".

Regards,
Peter T