Guys I am trying to reset / clear the contents of 3 cells C5:E5 by simply “selecting” another cell C10 with the word “Reset” in C10
This is not working?
=IF(C10=TRUE(),"",clear(C5:E5),"")
Anybody see why this might be the case?
Tried making and running a macro but keep running into JRE errors so thought this might be a simple way to achieve this but,
Follow @JohnSUN to solve your problem.
I’ll give your question, “why this is not working” some time:
.
Hey John I have mucked around with trying to get the Macro to work but have now given up. Especially as I want to share this sheet when working properly.
So I am back to trying to get the simple Clear or Rest to work by selecting C10 to clear C5:E5
How should I edit the formaul below to make this work please as I dont quite understand your earlier advice about not using True.
I searched the Help files but could only locate info on Clean and not Clear???
I also have one little problem that I cant quite get right
with the following formula which works as required EXCEPT if a “0” is the number entered into the referenced cell E5 in which case the calculation does not happen. Any number greater than 0 works such as 0.0000001 . So the question is how do I allow “0” to be accepted as a genuine number for the calculation For clarity F4 is a result of the entered number at E5 ie =84-E5
All numbers must be entered in at C5, D5 and E5 for the calculation to work.
=IF(C5=0,"",IF(D5=0,"",IF(E5=0,"",D5/F4*8760))) I used these formulae in an effort to stop the answer cells at C8, D8 and E8 from pre populating with half calculated answers.
I hope that makes some sort of sense?
Share this sheet with us while it not works properly
Perhaps this is because such a function simply does not exist? How long ago did you start getting familiar with spreadsheets (I’m asking about Calc now)?
only, if I assume you don’t read your formula:
but the formula sets a “result” of “” for E=0 in the part
You may rewrite the complete formula to
=IF( OR( C5=0; D5=0; E5=0); ""; D5/F4*8760)
Then it is easier to see: If you remove the ; E5=0 there will be a calculation. But, if you can’t see this yourself, you obviously does not understand your formula and should ask somebody why the condition is there…
Hey John
Thanks for your assistance I do appreciate it.
I have attached the file here which may make it much easier for both of us.
The sheet as constructed performs the calculations required with any number except “0” when entered into E5. This is the first problem and I do understand that I have E5=0 in the formula so I was looking for a way to have “0” accepted as well. My aim is to have the calculation work only when all three cells C5, D5 and E5 have valid numbers entered. Before adding in the E=0 previously it was showing odd numbers and or various Error messages at the time only one or two of these cells had numbers entered.
So is there a function that could be inserted instead of a Macro that will “Clear” the contents of the three cells to reset the sheet ready for the next entry of numbers?
Note new file update as previous one was incorrect, my apologies for that.
Experimental copy AussieWayne’s Running calculation of FTSO Rewards APR APY.xlsx (8.4 KB)
Most of your problems are related to the format of your spreadsheet - if you are forced to use xlsx workbook then you should use Excel, if you want your spreadsheet to work well in LibreOffice Calc then save your spreadsheet in ODS format.
Experimental copy AussieWayne’s Running calculation of FTSO Rewards APR APY.ods (30.6 KB)
Hey John Thank you so much for sticking with me on this as we have success now.
The macro problem was solved by totally uninstalling the old version then reinstalling a new copy of Java plus I then had to click on allow LibreOffice in a setting in Malwarebytes as that was stopping even the new version from running as it should.
I have now even added another button CALCULATE with a macro that is working just fine.
I also changed the =0,"", to =IF(OR(C5="",D5="",E5=""),"",
and now it will accept “0” as a valid number and still calculate correctly.
Once again a huge thank you for your advice and assistance.
In principle, it is not very difficult. Let’s do it together step by step.
Let’s write a macro first. Did you say that you had problems with this?
Actually, this should not happen with new versions of the office suite. By the way, what version and what operating system are you using?
So, choose Record Macro from the menu. Select cells C5:E5, press Backspace, specify what exactly should be cleared in these cells and press Enter, select cell C10 and stop recording. Save to the current document in Module1 with a name, for example, clearRng. We get something like this code:
sub clearRng
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$5:$E$5"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Flags"
args2(0).Value = "SVDFN"
dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args2())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$C$10"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
end sub
Now let’s use the trick that our esteemed colleague Lupp has repeatedly described - let’s create a hyperlink that will call the macro. Write the formula in cell A10
=HYPERLINK("vnd.sun.star.script:Standard.Module1.clearRng?language=Basic&location=document";"Reset")
That’s it, problem solved.
Version: 7.4.3.2 (x64) / LibreOffice Community
Build ID: 1048a8393ae**********31b5c133c5f1d08b890
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-AU (en_AU); UI: en-GB
Calc: threaded
It’s strange, very strange… And what claims to the JRE did the macro recorder have? Can you quote his complaint verbatim?