Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot set range class?
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"PasteSpecial method of Range class failed" when range Number of Rows 2000 | Excel Programming | |||
IS 'RANGE WINDOW CLASS' AVAILABLE ? | Excel Programming | |||
IS 'RANGE WINDOW CLASS' AVAILABLE ? | Excel Programming | |||
range class problem | Excel Programming | |||
Range Class falure | Excel Programming |