Page 1 of 1
Add '+1' to Multiple Cells
Posted: Wed May 23, 2018 12:50 am
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!!
Re: Add '+1' to Multiple Cells
Posted: Wed May 23, 2018 1:02 am
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
Re: Add '+1' to Multiple Cells
Posted: Wed May 23, 2018 3:38 am
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.
Re: Add '+1' to Multiple Cells
Posted: Wed May 23, 2018 2:10 pm
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.