EOMONTH, EDATE & NETWORKDAYS alternative

sq30
sq30 used Ask the Experts™
on
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:

private void button6_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook oWB;
            Microsoft.Office.Interop.Excel.Worksheet oWS;
            oWB = oXL.Workbooks.Add(Missing.Value);
            oWS = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets.get_Item(1);
            oXL.ScreenUpdating = false;

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=master");
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM spt_values", conn);
            try
            {
                conn.Open();
                System.Data.SqlClient.SqlDataReader myReader = cmd.ExecuteReader();
                List<object[]> values = new List<object[]>();
                while (myReader.Read())
                {
                    object[] stra = new object[myReader.FieldCount];
                    for (int col = 0; col < myReader.FieldCount; col++)
                    {
                        //going cell by cell is too slow
                        //oWS.Cells[row, col + 1] = myReader.GetValue(col).ToString();
                        stra[col] = myReader.GetValue(col);//.ToString();
                    }
                    values.Add(stra);
                }
                object[,] v = new object[values.Count, myReader.FieldCount];
                int row = 0;
                foreach (object[] sa in values)
                {
                    for (int col = 0; col < myReader.FieldCount; col++)
                    {
                        v[row, col] = sa[col];
                    }
                    row++;
                }
                oWS.get_Range("A1", oWS.Cells[v.GetLength(0), v.GetLength(1)])
                    .set_Value(Missing.Value, v);
            } catch(Exception e1) {
                MessageBox.Show(String.Format("Exception Occured –>> {0}",e1));
            } 

            // now finally show the prepared sheet
            oXL.ScreenUpdating = true;
            oXL.Visible = true;
            oXL = null;
            // or uncomment the below to save it and quit
            //oXL.DisplayAlerts = false;
            //oWB.SaveAs(@"C:\temp\sample.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValueDelComp, misValueDelComp, misValueDelComp, misValueDelComp, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValueDelComp, misValueDelComp, misValueDelComp, misValueDelComp, misValueDelComp);
            //oXL.Quit();
        }

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Sorry - cross posted.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:

Function Networkdaysvba(startDate As Double, endDate As Date, Optional holidays As Variant) As Integer
    Dim i As Integer
    Dim tmp As Integer
    Dim dt As Date, dtStart As Date
    
    startDate = Fix(startDate)
    tmp = ((endDate - startDate + 1) \ 7) * 5 ' entire work weeks
    dtStart = startDate + (tmp * 7 / 5) ' move to last week
    For dt = dtStart To endDate
        If Weekday(dt, vbMonday) <= 5 Then tmp = tmp + 1 ' add work days in the last week
    Next
    
    If IsArray(holidays) Then
        For Each h In holidays
            ' if any holiday falls in the range, remove it
            If Weekday(h, vbMonday) <= 5 And _
                startDate <= h And endDate >= h Then
                tmp = tmp - 1
            End If
        Next
    End If
    Networkdaysvba = tmp
End Function

Function EOMonthVBA(startDate As Double, months As Long) As Date
    startDate = Fix(startDate)
    EOMonthVBA = DateAdd("m", months + 1, startDate - Day(startDate) + 1) - 1
End Function

Function EDateVBA(startDate As Double, months As Long) As Date
    startDate = Fix(startDate)
    EDateVBA = DateAdd("m", months, startDate)
End Function

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Range("AS2").Formula = "=EOMONTHvba(AL2,1)"

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

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

Range("D47:D58").FormulaR1C1 = "=NETWORKDAYSvba(RC[-3],EOMONTHvba(RC[-3],0),Control!R2C13:R23C13)"
Top Expert 2010

Commented:
cyberkiwi,Nice work with the VBA versions.BTW, EOMONTH can be emulated with the DATE function.  For example,=EOMONTH(AL2,1)    can be    =DATE(YEAR(AL2),MONTH(AL2)+2,0)and =EOMONTH(AS2,-1)    can be    =DATE(YEAR(AL2),MONTH(AL2),0):)Patrick

Author

Commented:
Cyberkiwi,

Brilliant! That's cured the problem on two PC's and I will try the others later.

Many thanks for your help
Sq

@matthewspatrick - Thank you too.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial