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/routine
-------------------------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$2:D2)C$2,"",INDEX(SubRng1,SMALL(IF(I SNA(MATCH
'(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))-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



"Peter T" wrote:

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" wrote in message
...
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.


.