Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

EOMONTH, EDATE & NETWORKDAYS alternative

Avatar of sq30
sq30 asked on
Microsoft ExcelSpreadsheets
6 Comments1 Solution1683 ViewsLast Modified:
Hi,

Using the attached code I get the error #NAME? on some machines. I think I've pinpointed it down to the fact the the versions of excel range from 2000 - 2003 sp3 and some do not have the analysis toolpack installed?

Is there an alternative I can use for the four lines below?
-------------------------------------------------------------------------------------------------------------------

Range("AS2").Formula = "=EOMONTH(AL2,1)"

Range("A47").Formula = "=AS2-((EOMONTH(AS2,0)-EOMONTH(AS2,-1))-1)"

Range("A48:A58").FormulaR1C1 = "=EDATE(R[-1]C[],-1)"

Range("D47:D58").FormulaR1C1 = "=NETWORKDAYS(RC[-3],EOMONTH(RC[-3],0),Control!R2C13:R23C13)"

-------------------------------------------------------------------------------------------------------------------



What I'm trying to achieve is that that cell AS2 is advanced by plus 1 month then range A47-A58 lists the last 12 months including the current month also range D47:D58 gives the total working days for each of these months.

Thanks in advance
Sq





Private Sub MonthPlus1()

Application.EnableEvents = False

Range("AL2").Formula = "=AS2"
Range("AL2").Value = Range("AL2").Value

Range("AS2").Formula = "=EOMONTH(AL2,1)"
Range("AS2").Value = Range("AS2").Value

Range("AL2").ClearContents
     
    
Range("A47").Formula = "=AS2-((EOMONTH(AS2,0)-EOMONTH(AS2,-1))-1)"
Range("A47").Value = Range("A47").Value

Range("A48:A58").FormulaR1C1 = "=EDATE(R[-1]C[],-1)"
Range("A48:C58").Value = Range("A48:C58").Value

Range("D47:D58").FormulaR1C1 = "=NETWORKDAYS(RC[-3],EOMONTH(RC[-3],0),Control!R2C13:R23C13)"
Range("D47:D58").Value = Range("D47:D58").Value

Application.EnableEvents = True

End Sub