We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

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

asked 2021-06-10 02:32:46 +0200

Madmaxneo gravatar image

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.

edit retag flag offensive close merge delete

Comments

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?

JohnSUN gravatar imageJohnSUN ( 2021-06-10 06:29:08 +0200 )edit
1

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.

Lupp gravatar imageLupp ( 2021-06-10 13:46:55 +0200 )edit

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.

Madmaxneo gravatar imageMadmaxneo ( 2021-06-10 17:39:25 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2021-06-10 12:01:17 +0200

Kamil Landa gravatar image
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
edit flag offensive delete link more

Comments

@Kamil Landa 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.

JohnSUN gravatar imageJohnSUN ( 2021-06-10 12:40:45 +0200 )edit

Or maybe use for example convert to Long like this:

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

Kamil Landa gravatar imageKamil Landa ( 2021-06-10 12:50:14 +0200 )edit

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
sokol92 gravatar imagesokol92 ( 2021-06-10 14:31:50 +0200 )edit

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.

Madmaxneo gravatar imageMadmaxneo ( 2021-06-10 17:44:11 +0200 )edit

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.

JohnSUN gravatar imageJohnSUN ( 2021-06-10 17:50:53 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2021-06-10 02:32:46 +0200

Seen: 25 times

Last updated: Jun 10