Aging Macro

Posted by matthew schmitt on May 09, 2001 9:55 AM

I am trying to create a macro which will determine the current age in days(not including weekends and certain holidays)of an item, based on the date when the item was entered on to the sheet. I can do it with a formula, but due to protection issues, it will work better with a macro button that updates the age. Unfortuneately, I am not sure how to code this, and I can't find any examples in any books. Any help would be appreciated.

Posted by Dave Hawley on May 09, 2001 10:07 AM

Hi Matthew

Probaly the easiest way would be to use the NETWORKDAYS function on a sheet who's Visible property is set to xlveryhidden eg;

Sub TryThis()
Dim iAge As Integer
Sheet1.Visible = xlSheetVeryHidden
iAge Sheet1.Range("A1")
msgbox iAge & " days"
End Sub

For this you would simply put your NETWORDAYS function in cell A1 of the VeryHidden sheet and have it's arguments reference the appropriate cells. Then all you have to do is pass the result to an Variable.

Dave

OzGrid Business Applications

Posted by Matthew Schmitt on May 09, 2001 10:37 AM

When I did that, no matter what the number of days was, the message box always reads 0 days

Posted by Dave Hawley on May 09, 2001 10:48 AM

When I did that, no matter what the number of days was, the message box always reads 0 days :

What value do you have in Sheet1 range A1 ? Must be nothing. I'm betting your not referencing the correct sheet, try using the Tab name of the sheet as apposed to the CodeName :

Sub TryThis()
Dim iAge As Integer
Sheet1.Visible = xlSheetVeryHidden
iAge Sheets("Sheet1").Range("A1")
msgbox iAge & " days"
End Sub

Dave

OzGrid Business Applications