Need Calc help with a formula (or macro) for adding numbers from other cells to a total cell then clearing the other cells

English
#1

The “Total Cell” is H44. I am adding numbers to cells A44, B44, and C44. I want to take the numbers from cells A44 through C44 (along with cell H44) and add them to the Total Cell (to get a new total), then clear out cells A44 through C44.

For example:

H44= 5, A44=2, B44=1, C44=2.

I want the formula (or macro) to add all those numbers (5+2+1+2) to get a new total of 10 then put that number in H44 and clear out cells A44 to C44.

This is actually for a range of cells from A (B,C,H) 44 to A (B,C,H) 52. So if there is a way to do a range of cells without doing it line by line that would be awesome.

Understand that I am not good at macros. I was working on this issue years ago and someone helped me create a macro but things happen in a move and I have had to recreate the sheet I was using and now I do not remember how to even create a macro much less use one.

The document is in .xlsx format so that others can use the spreadsheet with other spreadsheet programs.

Version: 7.6.0.3 (X86_64) / LibreOffice Community

Build ID: 69edd8b8ebc41d00b4de3915dc82f8f0fc3b6265

I need a macro that will replace a number in a cell with a new total.
#2

While this is quite easy in most programming languages, you can not do this in a spreadsheet without macros. Spreadsheets use functions, so H44 would contain a formula and show the result. But formulas can not reference itself. Also you can not delete the contents of other cells by formula. So you will need a macro or use another approach to solve the problem in Calc. (A tool to drill holes is not helpful, if you need a saw.)

So you need a programmer for vba, not for StarBASIC. If the macro will then work in LibreOffice is another point. Compatibility for macros is limited, but as your needs seem to need no special UNO-functions this may work in this case

Programming languages know loops, so there is an easy way to do it line-by-line. No need for you to repeat 8 times.

#3

I figured that but can you help me with the macro or do you have a partial solution?

#4

It can be something like this:

Rem From KamilLanda in https://ask.libreoffice.org/t/i-need-a-macro-that-will-replace-a-number-in-a-cell-with-a-new-total/65269/5

Sub sumAndClearRanges 'make sum from two ranges and put it to the second range
	dim oDoc as object, oSheet as object, data1(), data2(), oRange1 as object, oRange2 as object, i&
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	oRange1=oSheet.getCellRangeByName("A44:C52")
	oRange2=oSheet.getCellRangeByName("H44:H52")
	data1=oRange1.getData
	data2=oRange2.getData
	for i=lbound(data1) to ubound(data1) 'make sum 
		data2(i)(0)=data1(i)(0)+data1(i)(1)+data1(i)(2)+data2(i)(0)
	next i
	oRange1.clearContents(1023) 'clear all contents in the range; number in SDK com.sun.star.sheet.CellFlags
	oRange2.setData(data2) 'set new data
End Sub
#5

Sorry, I avoided VBA/MS-macros, as they would run nowhere else and I won’t start now.

#7

Is this for a macro within a button? If I copied and pasted this into the macro area of the button would it work or is there something else I needed to do?
I get the idea with the oRange and the data being added though.

#8

I don’t quite understand what you mean by “pasted this into the macro area of the button”. :face_with_raised_eyebrow:

You said that

This means that you cannot insert a macro into this document - neither this macro. For documents with macros, you should use xls or xlsm (but of course it’s best to use ods) - the xlsx file cannot save code.

To use the above code in your work, you need to place it in the macro library in the My Macros container

After that, you will have many different options for executing this macro at the right time. For example, you can invoke this with Alt+F11 and the Run button. Or you can invoke this with the Tools - Macros - Run Macro menu and the Run button. Or with the Tools - Customize - Toolbars tab you can create a button with an icon or label on an existing toolbar or on your own toolbar and run a macro on the click of that button. Or with Tools - Customize - Keyboard tab you can create a keyboard shortcut to run a macro from the keyboard.
You are free in your choice - this will not affect the general document that other people will work with. This will only set up your own instance of Calc for the convenience of your work.

#9

Ok, got it. One question before I go about trying the above code this weekend. Will it add the appropriate Rows together to get the correct totals in the H column of cells? Meaning each cell in column H will have a different total based on the cells in columns A, B, and C for each row.

Need help with a Macro in Basic that will take a result from one cell and put it in the next available cell in a range
#10

Ok, this works pretty good except when there is nothing in the cells to be added together. If there is a single number in any cell it works fine but not if all the cells are blank. The result I get if all the cells (all the cells in the row of A, B, C, and H) are blank is “8.90029543402881E-308” which shows up in the cell as “8 9E-308”. How do change that macro to ignore the row when they are all blank or at least have it return a result of a blank in the H column when the pertinent cells in the row are blank.

#11

Too bad you didn’t mention in your original question that sometimes cells might not contain a number. And what values do you expect in the cells of the range A44:C52?

#12

I didn’t think that it being blank would cause any issues. The values would be from nothing to a whole number without decimals or fractions.

#13

I didn’t expect such problems either :smile:

In this case, the FIX() function can help. Change the line inside the loop to
data2(i)(0)=Fix(data1(i)(0)+data1(i)(1)+data1(i)(2)+data2(i)(0))
and check the result.

#14

That works though I’d prefer to leave them blank than add a zero, but that’s not really important (just a preference).
When adding this same macro for other groups of cells do I need to change the oRange and data sets from 1 and 2 to other numbers?
For example: The next range of cells are range A54 to C58 and H54 to H58. In this set would I need to up the oRanges to oRange3 and oRange4 along with changing the data numbers to data3 and data4?
Or is there an easier way?

#15

This is not a problem - the creators of the office suite have taken care of it. Just choose Tools - Options - LibreOffice Calc - View
image

Then add the same macro for the ranges A60 to C66 and H60 to H66, then for A68 to C74 and H68 to H74 and so on? Why didn’t you ask this in your original question?

#16

Nice, that is an awesome option!

Ok, I’ll try it.
I didn’t have that part in the beginning because I wanted to try and learn as much from this as possible. If I do it in steps it’s easier for me to understand.

#17

So for me to create this same set up for multiple ranges it seems like I need to have a separate module for each macro, is this correct?
Is there a way to add multiple ranges to that macro above so that I can have all the ranges in one button?
The first set of ranges is: “A43:C52” and “H43:H52” Yes it was “A44” originally but that was a typo on my part.
The second set of ranges is: “A54:C58” and “H54:H58”

There are about 9 or 10 sets of ranges currently but I want to get the idea of how to set it up correctly.

#18

If it will works correctly, does what you intended, then yes - “this is correct”.
Perhaps someone will criticize this approach and call such a decision stupid - what do you care about the opinion of critics?

Yes, there is such a way. The problem is that, as always in programming, there are several such paths - it is difficult to choose the “correct” one (they are all “correct” and each of them has flaws)
For example, you can list the addresses of all processed ranges in an array and loop through them all, executing the same code for each pair of addresses. Something like this:

Sub sumAndClearSomeRanges 'make sum from two ranges and put it to the second range
Dim oDoc As Object, oSheet As Object, data1(), data2(), oRange1 As Object, oRange2 As Object, i&, j&
Dim aAllRangesAddress As Variant 
	aAllRangesAddress = Array("A43:C52","H43:H52", "A54:C58","H54:H58", "A62:C64","H62:H64") ' and all others
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	For j = LBound(aAllRangesAddress) To UBound(aAllRangesAddress) Step 2
		oRange1=oSheet.getCellRangeByName(aAllRangesAddress(j))
		oRange2=oSheet.getCellRangeByName(aAllRangesAddress(j+1))
		data1=oRange1.getData
		data2=oRange2.getData
		for i=lbound(data1) to ubound(data1) 'make sum 
			data2(i)(0)=Fix(data1(i)(0)+data1(i)(1)+data1(i)(2)+data2(i)(0))
		next i
		oRange1.clearContents(1023) 'clear all contents in the range
		oRange2.setData(data2) 'set new data
	Next j
End Sub

Or you can exclude any mention of addresses from the macro code, and instead mark the cells on the sheet with some special style - the macro will process the values of such cells wherever they appear on the sheet and no matter how many there are.

But now, when we know that there will be many such ranges, it makes sense to stop and look back, to think again about the problem that you are trying to solve. Perhaps another approach to the solution will be found, less time-consuming and more efficient. For example, a dialog with three fields for cell values from columns A:C and “OK” and “Cancel” buttons that will automatically appear after double-clicking on a certain row? Perhaps this approach would be clearer to the user of your spreadsheet?

#19

This macro is working pretty good so far. But I am having one issue.
The line oRange1.clearContents(1023) 'clear all contents in the range clears out all the cells including the formatting, but I want the formatting to stay as is.
I tried doing some research on this myself and discovered that the part clearContents is supposed to leave the formatting alone and just clear the values in a cell, but that’s VBA and I assume this is a different language than VBA. Does the (1023) in that line have to be different for it to only clear the values? Is there a place I can go to look this information up?

#20

.
They are available in the API descriptions:
https://api.libreoffice.org/docs/idl/ref/CellFlags_8idl.html
.
API: Application Programming Interface = lots of subroutines and functions for controlling the LO. It independent from all of supported programming languages. (…instead of the VBA what is a “similar” bounch of the subs and functions, but it is usable with the MS Basic dialect only.)

#21

Thanks, that works awesomely!
I have another question about a macro for a checkbox and have different values in a linked cell based on of the box is checked or not. I’ll create a new topic for that and mark this one as solved. Thanks for your help, you’ve been excellent in helping me get this figured out.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.