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

[Madmaxneo]

This is for Calc.
I Need help with a macro. The best way to describe it is to give an example:
In cell C5 I have the number 3. Due to some other function in the calc sheet I have the number 2 in cell A5.
I want to take the 3 from cell C5 and the 2 from cell A5, add them together, clear out A5, then put the total (5) into cell C5.
Note that this will be a column full of different numbers, in this example it would be column C and column A.
Each cell in the column will have different numbers.

[JohnSUN]

Welcome! How about simply recording a sequence of actions using the macro recorder: select A5, cut, select C5, paste special with the option Add, select A5?

[Lupp]

By what should the execution of the macro be triggered?
What about looking for a design not needing such a “macro”?
If you describe what your sheet is actually expected to achieve, you may get good advice in that direction.

[Madmaxneo]

The macro will be triggered by a button.
The sheet itself is pretty complex and most of the sheet is handled with input from the end user and manipulation through a variety of functions. This macro, along with a few others, is helping to automate some of the processes.
Unfortunately I am a complete newb to macros (despite many many years manipulating spreadsheets using functions and some limited PDF javascript coding). There is a lot that I do not understand (in macros) and don’t have the time to learn from the beginning.

[KamilLanda]

Sub sumFromCells '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("A5:A9")
	oRange2=oSheet.getCellRangeByName("C5:C9")
	data1=oRange1.getDataArray 'data from the range
	data2=oRange2.getDataArray
	for i=lbound(data1) to ubound(data1) 'make sum 
		data2(i)(0)=data1(i)(0)+data2(i)(0)
	next i
	oRange1.clearContents(1023) 'clear all contents in the range; number in SDK com.sun.star.sheet.CellFlags
	oRange2.setDataArray(data2) 'set new data
End Sub

[JohnSUN]

@KamilLanda Good decision. But when it comes to the Total, perhaps getData should be used instead of getDataArray. In this case, if by mistake a text is entered into one of the cells in column A, we will not get a result like as “text32”, but the previous value of the sum 32 will remain.

[KamilLanda]

Or maybe use for example convert to Long like this:

data2(i)(0)=CLng(data1(i)(0))+CLng(data2(i)(0))

[sokol92]

You can use the following construction:

 If VarType(data1(i)(0))=V_DOUBLE And (len(data2(i)(0))=0 Or VarType(data2(i)(0))=V_DOUBLE) Then
    data2(i)(0)=data1(i)(0)+data2(i)(0)
  End If

[Madmaxneo]

Where would this 'If" construction be placed in the above example?

Would this above script work fine in a button, as is my intention?

Sorry, I am new to the whole macro thing.

[JohnSUN]

This if-then must been instead line data2(i)(0)=data1(i)(0)+data2(i)(0)

However, take @Lupp’s advice and describe the meaning of this copying and summing in even more detail. He is right, the problem may have a simpler solution.

[Madmaxneo]

Oh wow, things feel apart there but I’m back now. Can you possibly still help with this?

[JohnSUN]

@Madmaxneo Would you like to continue here? Or move on to a new topic?

[Madmaxneo]

Yeah that will work! I created the other topic before I found this again. I had no idea it was on this forum…lol.