I have a Spreadsheet with many sheets. I like to add script per sheet which fills zero in an array of cells when the cell not zero and not empty and not text and not function (=*) (except the first sheet). I like to use a general scrip so it would retrieves the name of the sheet (no need to change for every sheet). I like to set the array in a cell of the first sheet (example A1:D3) which retrieve by the scripts so if I have to change the size of the array in this case I don’t have to change the script of every sheet. The scripts would run when I push the added button on the sheets and If i push the button on the first sheet in this case it runs the scripts of the other sheets. How can I archive this? I appreciate any help to archive this. Thanks
This would be pretty easy to do as a macro. Just iterate your sheets, skip the sheet with the name of your master sheet, and set cell Value to 0 based on current cell String/Formula/Value–or even use FunctionAccess to check for ISBLANK() on each cell.
But, if you don’t get your hands dirty doing some of the LO BASIC, I’m not sure the community will write it for you.
Depending on when these 0’s need to get set, you could also look at a post-processing step via Python or awk??
Do you mean something like this setZero2cells.ods (11.6 kB)?
Unfortunately I don’t understand clearly to your system where to put the 0’s. Some example is needy, with your data before macro, and with the expected result after macro.
In the macro I use method .queryEmptyCells, but it is possible to use .queryContentCells (with the flag 1+4+16 from com.sun.star.sheet.CellFlags?) if you want to rewrite some values by 0’s and not fill the empty cells.
It is a cassa checker. The first sheet is the summary and there i paste the amount of the separate currencies. The following sheets are separately for every currency, When I work at a new place I have to zero out quantity of denominations for every currency and some other cells which also contains quantity information (Damaged banknotes etc,). In the array there are related calculations so therefore the conditions for the fill out. I like to fill out the cells which not 0 (unnecessary if already zero), not text (denomination name and other stuff which is text), and not function (I don’t know in this case it is count as text or number).
There are some samples (main sheet and two others with currencies (HUF, EUR)
The green cells which I fill when I check the cassa so they have to be filled by the macro.
You didn’t help me so much with your description about 0’s :-). Let’s try it without the negations in the description. So if there is a text in the cell, then let it be. If there is a formula in the cell, then let it be. And if there is a value and this value isn’t zero, then put 0 to the cell. All right? If my deduction is right, then it is possible to use simply the method .queryContentCells(1) and all values set to the 0 (the test if the value is 0 or not will be slower than set directly all values to the 0)
Set the constant cMainSheet and your ranges for your sheets in the variable pAddress() and test it.
Sub SetZero2Cells(optional oEvt as object) dim oDoc as object, oActiveSheet as object, oSheet as object, i&, j&, oContentCells as object, oRange as object, oRangeAddresses as object dim pAddress(), p1() const cMainSheet="Main" 'NAME OF MAIN SHEETS pAddress=array("Main.A1:B7", "List2.A1:A6", "List3.C1:C7", "List4.A1:C5") 'RANGES IN THE SHEET oDoc=ThisComponent oActiveSheet=oDoc.CurrentController.ActiveSheet 'active sheet if oActiveSheet.Name=cMainSheet then 'macro from Main sheet for i=lbound(pAddress) to ubound(pAddress) p1=split(pAddress(i), ".") 'name of the sheet oSheet=oDoc.Sheets.getByName(p1(0)) 'one sheet oRange=oSheet.getCellRangeByName(p1(1)) 'range oContentCells=oRange.queryContentCells(1) 'content cells in the range (1=Values, com.sun.star.sheet.CellFlags) for j=lbound(oContentCells.RangeAddresses) to ubound(oContentCells.RangeAddresses) oRangeAddresses=oContentCells.RangeAddresses(j) setZeroes(oDoc, oRangeAddresses) next j next i else 'only one minor sheet for i=lbound(pAddress) to ubound(pAddress) p1=split(pAddress(i), ".") 'name of the sheet if p1(0)=oActiveSheet.Name then 'range is only for minor sheet oSheet=oDoc.Sheets.getByName(p1(0)) 'one sheet oRange=oSheet.getCellRangeByName(p1(1)) 'range oContentCells=oRange.queryContentCells(1) 'content cells in the range (1=Values, com.sun.star.sheet.CellFlags) for j=lbound(oContentCells.RangeAddresses) to ubound(oContentCells.RangeAddresses) oRangeAddresses=oContentCells.RangeAddresses(j) setZeroes(oDoc, oRangeAddresses) next j end if next i end if End Sub Sub setZeroes(oDoc as object, oRangeAddress as object) 'set zeroes to the oRangeAddress in oSheet dim i&, j& i=oRangeAddress.EndRow-oRangeAddress.StartRow j=oRangeAddress.EndColumn-oRangeAddress.StartColumn rem array for setDataArray dim data(i), dataj(0 to j) as integer 'as integer = fill the array with number 0 for i=lbound(data) to ubound(data) data(i)=dataj next i rem set zeroes to the range dim oRange as object oRange=oDoc.Sheets(oRangeAddress.Sheet).getCellRangeByPosition(oRangeAddress.StartColumn, oRangeAddress.StartRow, oRangeAddress.EndColumn, oRangeAddress.EndRow) oRange.setDataArray(data) End Sub
And example file
setZero2cells2.ods (12.4 kB)