Add '+1' to Multiple Cells

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
sungar78
Posts: 1
Joined: Wed May 23, 2018 12:39 am

Add '+1' to Multiple Cells

Post by sungar78 »

Hi there,

In advance I apologize if this is in the wrong place.
I'm looking to write a macro that adds +1 to the values of several cells, i.e. B3:B11. I'm a poker player and I use OpenOffice to track individual players throughout hands, so my hope is to basically start a new hand by pushing Ctrl+S.
I'm already familiar with and using the macro the Adds +1 to the current cell, however I've scoured Google for a little over an hour and have been unable to get this current macro Adding +1 to each cell in a specific range to work.
Thanks for any help!!
Open Office 4.1.3 on Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Add '+1' to Multiple Cells

Post by FJCC »

Here is a quick version of a macro to add 1 to each cell in B3:B11 of the current sheet.

Code: Select all

oSheet = ThisComponent.CurrentController.ActiveSheet
oCellRng = oSheet.getCellrangeByName("B3:B11")
DataArry = oCellRng.DataArray
for i = 0 to 8
	DataArry(i)(0) = DataArry(i)(0) + 1
next i
oCellRng.DataArray = DataArry
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Add '+1' to Multiple Cells

Post by RusselB »

An alternative that doesn't require a macro:
1) Enter 1 into a cell outside of the array range (eg: Z1)
2) Copy that cell using Ctrl+c
3) Highlight the array range
4) Press Ctrl+Shift+V (Paste Special)
5) Under Operations select Add
6) Press OK

If your original numbers were generated via a formula, then that formula will become part of a array formula.
If your original numbers were entered manually (or imported as numbers), then the numbers in the range will be replaced with the result.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Add '+1' to Multiple Cells

Post by Lupp »

See also [this thread](https://ask.libreoffice.org/en/question ... ell-value/). In specific regard the first comment on my answer there, and "Edit2", "Edit3" I did regarding that comment.

As always thre are different ways to call a Sub from a spreadsheet, and slightly different flavours of the problem to pass parameters. The push-button-way demonstrated in the mentioned thread shows in a rudimentary way a simple solution for the problem.

In the specific case where a rectangular cell-range is processed, the code lines already presented by "FJCC" are preferrable over the listing of many single cells. A range address like "B3:B11" can also be passed via the .Tag proberty, of course.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply