Problem with ArrayFormula

Hye,

I have a problem with aspose.cells for .NET (VB)

I want to transpose a column to a line. With Excel it exist the formula “=TRANSPOSE()” so I tried to use it by this way :

worksheet.Cells(“C2”).SetArrayFormula("=TRANSPOSE(" & worksheet.Name & “!A1:A10)”, 1, 10)

But it doesn’t work. In my excel document I have “#NOM?” (I’m french so in English it must be something different) in C2.
However if I check the formula and press Ctrl+Enter when I am in this cell, the formula worK.

The problem is the same if I try to do the same with only one cell :
worksheet.Cells(“C1”).Formula = “=TRANSPOSE(” & worksheet.Name & "!A1:A10)"
When I press enter in this cell, the formula work too.

Can you please help me to solve this problem.


I attach in this post a project, with two documents of exemple :
excel/myDocument.xls is the document used in the project
and excel/final.xls is the document that I what to have


Thanks

Hi,

Thanks for project with files,

We will sort out your issue and get back to you soon.

Thank you.

Hi, I’m with the very same problem.
Can anyone suggest any help?

Hi,

Which verison do you use?

Please try the attached fix.

Thanks for the reply, but I’ve still the same problem.
My previous version was 4.5.0.0, and now I used your supplied version 4.5.0.12, but problem remains.

I’m explaining the situation…
I want to use the Array Formula,
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-

COUNTBLANK(BlanksRange),“”,INDIRECT(ADDRESS(SMALL(

(IF(BlanksRange<>“”,ROW(BlanksRange),ROW()+ROWS(BlanksRange))),

ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))


The purpose of using the formula is in, Eliminating Blank Cells From Lists

After using the formula, using Cell.SetArrayFormula(), the cells displays the error “#NAME?” but when I go to the edit formula mode in Excel, and press CTRL+SHIFT+ENTER (the method to use an array formula in excel), then I get the desired result (list of all nonblank values).


Please give a quick solve.

Hi,

Please try this fix.

In the old version, we do not support to set the function "SMALL".