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+unsubscr...@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