Add '+1' to Multiple Cells

Creating a macro - Writing a Script - Using the API

Add '+1' to Multiple Cells

Postby sungar78 » Wed May 23, 2018 12:50 am

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
Posts: 1
Joined: Wed May 23, 2018 12:39 am

Re: Add '+1' to Multiple Cells

Postby FJCC » Wed May 23, 2018 1:02 am

Here is a quick version of a macro to add 1 to each cell in B3:B11 of the current sheet.
Code: Select all   Expand viewCollapse view
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
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Posts: 6634
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Add '+1' to Multiple Cells

Postby RusselB » Wed May 23, 2018 3:38 am

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.4 and LibreOffice on Windows 7 Pro & Ultimate
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.
Posts: 4461
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Add '+1' to Multiple Cells

Postby Lupp » Wed May 23, 2018 2:10 pm

See also [this thread]( ... 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 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
Lupp from München
User avatar
Posts: 1914
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests