Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculation Error


What wrong with my syntax'

sh.Range("F" & lr + 1).Value = Application.WorksheetFunction.Sum(Me.txt_Estimated_Life.Value - Int((Now() - Me.txt_Aquisistion_Date.Value) / 365))



Selected Answer

It's hard to say what's wrong since you haven't told us what your Userform contains.

Firstly you shouldn't need to use the Sum function (since VBA adds up natively) and secondly if Me.txt_Aquisistion_Date.Value is something like 01/04/2022 then you are trying to subtract (/divide) a text string.

If you type a number like 42 in the UserForm field txt_Estimated_Life and a date like "01/04/2004" in txt_Aquisistion_Date then you probably need to convert the latter to a date using VBA's DateValue function and subtract if from Date (which returns today's date) but round it to 0 decimal places using a worksheet function as follows:

sh.Range("F" & lr + 1).Value = Me.txt_Estimated_Life.Value - Application.WorksheetFunction.RoundDown((Date - DateValue(Me.txt_Aquisistion_Date.Value)) / 365, 0)

Hope this helps.


Answer the Question

You must create an account to use the forum. Create an Account or Login