Macro to add numbers to cells with IF statement

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
ChrisW88
Posts: 4
Joined: Tue Feb 19, 2019 10:38 am

Macro to add numbers to cells with IF statement

Post by ChrisW88 »

Hi all,

I'm using a macro which I'll post below to add £5.00 onto a value via a button. Is it possible to add an IF statement to this macro to only add £5.00 to the selected cells based on the value of another cell? For example, if I have £0.00 in cell A1 and I want to add £5.00 but only if B1=3. If B1 didn't equal 3 I wouldn't want it to add anything.

Hopefully this makes sense :D

This is the macro I'm currently using;

Code: Select all

Sub add_5pounds
oDoc = ThisComponent
oView = oDoc.getCurrentController()
oSheet= oView.getActiveSheet()
oSel = oDoc.getCurrentSelection()
oAddr = oSel.getRangeAddress()

nSCol = oAddr.StartColumn
nSRow = oAddr.StartRow
nECol = oAddr.EndColumn
nERow = oAddr.EndRow

for j = nSCol to nECol
   for i=nSRow to nERow   
      oCell = oSheet.getCellByPosition(j,i)
      oCell.value = oCell.value + 5.00
   next 'i
next 'j

End Sub
Thanks in advance!
OpenOffice 4.1.6 / Windows 10 Pro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to add numbers to cells with IF statement

Post by Villeroy »

A spreadsheet is a simplified, more visual programming language for non-programmers. Use it.
C1: =A1+IF(B1;5)
returns A1 or A1+5 if B1
dbl-click the cell handle (botom-right corner) of cell C1 to fill down.
Copy, paste-special over the original data.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ChrisW88
Posts: 4
Joined: Tue Feb 19, 2019 10:38 am

Re: Macro to add numbers to cells with IF statement

Post by ChrisW88 »

Hi Villeroy, thanks for your suggestion, only problem is the main spreadsheet where this data is entered is used by multiple, non-tech savvy users. It's also printed as a report. This was the reason for creating the button in the first place as its the easiest and most efficient method for staff (not so easy for me!).

I'm going to try my best to give you an example of what this spreadsheet does!

Joe Bloggs has a value of £1.00 in column A. This value is typed in by staff. After 3 days, Joe receives £5.00 as a one of payment. The £5.00 should be added via clicking a button which looks at the sheet, checks to see how many entries in the sheet are equal to 3 days old and adding £5.00 to the original cell in column A.

At the moment, I already have a column which counts days from original date of data entry into the sheet. I also have a button which adds £5.00 but only to cells manually selected by the user.

Hopefully that all makes sense!

Thanks again
OpenOffice 4.1.6 / Windows 10 Pro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to add numbers to cells with IF statement

Post by Villeroy »

This is an inappropriate approach to handle financial transactions.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ChrisW88
Posts: 4
Joined: Tue Feb 19, 2019 10:38 am

Re: Macro to add numbers to cells with IF statement

Post by ChrisW88 »

The spreadsheet is not handling financial transactions. It is being used as a temporary reporting tool completely unrelated to financial transactions. The example above is purely an example.
OpenOffice 4.1.6 / Windows 10 Pro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to add numbers to cells with IF statement

Post by Villeroy »

Teach your users how to use a spreadsheet.
I won't teach you how to program nor will I write the code for you.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to add numbers to cells with IF statement

Post by JeJe »

The Basic syntax is if... then... like so:

If B1=3 then A1 = A1 + £5.00

or

If B1=3 then
A1 = A1 + £5.00
C1 = 6
'other statements
end if

or

If B1=3 then
A1 = A1 + £5.00
'other statements
else
C1 = 6
'other statements
end if
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
ChrisW88
Posts: 4
Joined: Tue Feb 19, 2019 10:38 am

Re: Macro to add numbers to cells with IF statement

Post by ChrisW88 »

Wow, that escalated quickly Villeroy. Community forums wouldn’t exist without helpful guys like you. If everyone knew what they were doing people wouldn’t be on here asking questions...

JeJe, thanks for that. It actually helps more to see it noted down that way. I’ll read some more about macros and figure out how to incorporate it all myself. Thanks again though.
OpenOffice 4.1.6 / Windows 10 Pro
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Macro to add numbers to cells with IF statement

Post by Sébastien C »

@ChrisW88
Your code may make more sense if you give us to see an example spreadsheet (by uploading an example of Calc’s file undone from any personal data). Just note that when reading your lines of code, I notice that you have put "i" and "j" in comment (in the "Next" instruction). It works, but you can quite rightly specify "Next i" and "Next j", without comment. It is even, in my opinion, always much clearer.

I am personally so much less talented in formal languages than Villeroy; as far as human languages are concerned, you will allow me to honor you with mine: bienvenue.
:D

@Villeroy
Aber warum wählen Sie die Musikalität deutscher Militärkapellen (von denen wir wissen, wie man darauf verzichten kann), um diese Dinge auszudrücken? Wissen Sie, Bachs Fugen, in ihren Komplexitäten, ihren Feinheiten und, ehrlich gesagt, ihren Schönheiten, sind sicherlich eher geeignet, was Ihnen am Herzen liegt: Effizienz.

Ihre lieber (sehr) kleiner Franzose.
:ouch:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to add numbers to cells with IF statement

Post by Villeroy »

"My users can't use" --> "I can't program nor do I read any books" --> "Can you help me?" is a race condition of incompetence which needs to be stopped at some point because this forum is becoming more and more unattractive for competent helpers.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Macro to add numbers to cells with IF statement

Post by Sébastien C »

And you really think you can raise the level by barking like that?

If it is clear to some of us that your own skills are (very, I would like to have the tenth) well above average, what value of your own person can judge someone whose visit counter was zero before the publication of the present thread?

Unless to inspire us "Godwin point" back, I do not think the vastness of your skills can give you the right to be obnoxious. Our geographical situation being more comfortable, it is still fortunately easy, for me, to give you the alms of this economy. Wish it can last.

In other words, by dint of seeing you imagine cleaning shit with a karcher, we will end up finding a pity to see you splashed first.

Because THAT does not raise the level.
:ucrazy:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Post Reply