How to fill cells with 0 in an array of cells by script with conditions

[Necromancer]

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

[joshua4]

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??

[KamilLanda]

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.

[Necromancer]

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)

[Necromancer]

The green cells which I fill when I check the cassa so they have to be filled by the macro.

[KamilLanda]

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)