Date: prev next · Thread: first prev next last
2020 Archives by date, by thread · List index


I've been trying to take a formula that calculates the difference between to 
dates into years/months/days.The original formula works fine.


Wanted to make a macro or function that would make modifications to 
formula to new date cells. Didn't get anything to work with functions, but 
have gone thru about 20 macros versions and have something that works, 
but don't understand why the final step is required?? Having to manual enter 
space and enter after doing an edit? Do it in record, but it isn't recorded??


Original Formula
=IF(DATEDIF(A2,A1,y),IF(DATEDIF(A2,A1,y)=1,1 
Year,DATEDIF(A2,A1,y)Years),)IF(MOD(DATEDIF(A2,A1,m),12),IF(MOD(DATEDIF(A2,A1,m),12)=1,1 
Month,MOD(DATEDIF(A2,A1,m),12)Months),)IF(DATEDIF(A2,A1,md),IF(DATEDIF(A2,A1,md)=1,1 
Day,DATEDIF(A2,A1,md)Days),)


Just calculates years months and days. If values are 0, they are not include, 
and if singular changes label. Very basic.


Issue was if wanting to use it on other cells, had to do copy to keep address 
matching, or had to paste and manually change all the addresses.


Have this latest version of macro that works with issues.
I've got it to ask for the new ranges and then it modifies the formula.
Have tried to combine steps, but always getting message that formula is 
wrong, and the correction it gives doesn't work. Issue with all thes??
It places the original formula in cell, and the replaces the value, but cell 
doesn't record it as a formula unless I edit the cell, and then have to press a 
space or something and then enter?? Just enter doesn't do anything??


Any ideals?? Not a big deal, just something interesting??
Thanks


sub ymdzz1
rem ----------------------------------------------------------------------
rem define variables
dim documentas object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document= ThisComponent.CurrentController.Frame
dispatcher = createUnoService(com.sun.star.frame.DispatchHelper)


dim sText
sText = InputBox (Please enter Date Cells Example D2,D1:)


rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name =StringName
args2(0).Value ==IF(DATEDIF(A2,A1,y),IF(DATEDIF(A2,A1,y)=1,1 
Year,DATEDIF(A2,A1,y)Years),)IF(MOD(DATEDIF(A2,A1,m),12),IF(MOD(DATEDIF(A2,A1,m),12)=1,1 
Month,MOD(DATEDIF(A2,A1,m),12)Months),)IF(DATEDIF(A2,A1,md),IF(DATEDIF(A2,A1,md)=1,1 
Day,DATEDIF(A2,A1,md)Days),)


dispatcher.executeDispatch(document,.uno:EnterString,, 0, args2())
rem ----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name =By
args3(0).Value = 1
args3(1).Name =Sel
args3(1).Value = false


dispatcher.executeDispatch(document,.uno:GoDown,, 0, args3())


rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name =StringName
args4(0).Value ==SUBSTITUTE(FORMULA(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),A2,A1,sText)
dispatcher.executeDispatch(document,.uno:EnterString,, 0, args4())
dispatcher.executeDispatch(document,.uno:Copy,, 0, Array())


rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name =By
args5(0).Value = 1
args5(1).Name =Sel
args5(1).Value = false


dispatcher.executeDispatch(document,.uno:GoDown,, 0, args5())


rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name =Flags
args6(0).Value =S
args6(1).Name =FormulaCommand
args6(1).Value = 0
args6(2).Name =SkipEmptyCells
args6(2).Value = false
args6(3).Name =Transpose
args6(3).Value = false
args6(4).Name =AsLink
args6(4).Value = false
args6(5).Name =MoveMode
args6(5).Value = 4


dispatcher.executeDispatch(document,.uno:InsertContents,, 0, args6())


rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:SetInputMode,, 0, Array())


rem Have to have edit above then space then enter??




end sub




-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.