let workSheet = Api.GetActiveSheet();
let data = [[“A1”,“B1”],[“A2”,“B2”],[“A3”,“B3”]];
workSheet.GetRange(“A1:B3”).SetValue(data);
How to transpose a value? I mean result like this:
for (let j = 0; j < data.length; j++) {
workSheet.GetRangeByNumber(0+j,0).SetValue(data[j][0]);
workSheet.GetRangeByNumber(0+j,1).SetValue(data[j][1]);
}
Hello @pahetka
Do I understand it correctly that the goal is to swap values of certain cells in A column with the values of cells from B column?
Yes, can I set value in range by rows, not columns. In other words, is there a way to fill values, like in a second version of code but without using a loop?
Sorry for the late reply.
This might be a tough task but we are checking the situation.
I will update this thread once I have something to share.
We ran some tests and, unfortunately, your scenario cannot be achieved without looping.
Based on this request we have created the enhancement suggestion to add possibility to change the direction of filling with SetValue parameter number 61056 into our internal tracker.
If you have any examples of VBA macros that is capable of performing such actions that you can share with us please feel free to share them so we can take notes for the suggestion.
Hello, I’m joining this thread about SetValue to multiCells range.
My purpose is to set “array formula” values using SetValues.
It is easy to do using the editor (Ctrl+Shift+Enter)
but I have no ideas how to do that from a macro.
I think it is a purpose very closed to th current subject
Kind regards
Hello @jpp
Please provide more details about the usage scenario of macro that you want to use.
For now, it doesn’t seem to be related to the topic of this thread.
Hello @Constantine
my purpose is “how to set or get formula array”
OnlyOffice spreadsheet range have no property like VBA FormulaArray .
for instance:
Worksheets(“Sheet1”).Range(“A1:C5”).FormulaArray = “=3”
You can use SetValue method to set formula to the cell and GetFormula method to get formula of the range/cell. Here is example:
oWorksheet.GetRange("B1").SetValue(1);
oWorksheet.GetRange("C1").SetValue(2);
var oRange = oWorksheet.GetRange("A1");
oRange.SetValue("=SUM(B1:C1)");
var sFormula = oRange.GetFormula();
oWorksheet.GetRange("A3").SetValue("Formula from cell A1: " + sFormula);
Let me know if I misunderstand your query.
No, I’m sorry @Constantine but you didn’t catch what i Means.
You demonstrate me how to assign a single cell with a formula working over an array of cells. OK
But My purpose is how to assign a multiple cells array with a single formula which return an array of values.
In an interactive way it is possible : Select Few cells, input the formula the press Ctrl+Shift+Enter.
And so the formula is assigned to the multi cell range and each cell formula field stay null.
the array formula appears inside curve braces.
four instance “{=A1:A10}”
When using SetValue for a formula to a multicell range, the formula is duplicated to each cell of the range. That is wrong.
kind regards
Thank you for the information, now I see your point.
Unfortunately, we do not have alternative method for this operation. We will take a closer look at it and I will provide feedback when any news come up on that.
Hello @jpp
I wanted to inform you that an enhancement suggestion to implement FormulaArray was registered recently. Unfortunately, there is no ETA available yet.