Problem with my vba code in Excel?

mrmiyagi · Oct 4, 2010

Hello,

I use the following code to retrieve over due tasks that are not finished from different worksheets and add them to a summary page (see examples below).

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ray As Variant
Dim Ray2 As Variant
Dim Ws As Worksheet
Dim Rng As Range, Dn As Range
Dim Lst As Long
Dim c As Long
ray = Array("Project with Overdue Date", "", "Date", "=Now()")
Ray2 = Array("Stage", "Due Date", "Finish Date")
With Sheets("Summary")
    .Columns("A:C").ClearContents
    .Range("A1:b1") = Application.Index(ray, 1, Array(1, 2))
    .Range("A2:B2") = Application.Index(ray, 1, Array(3, 4))
    .Range("B2").NumberFormat = "dd/mm/yyyy"
End With
For Each Ws In Worksheets
    c = c + 1
     Lst = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
     Lst = Lst + IIf(c > 1, 1, 0)
If Not Ws.Name = "Summary" Then
    With Ws
        Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
            For Each Dn In Rng
                If Dn <> vbNullString And Dn(, 3) = vbNullString And Dn(, 2) < Date Then
                    With Sheets("Summary").Range("A" & Lst)
                        .Value = Ws.Name
                        .Offset(1).Resize(, 3) = Ray2
                        .Offset(2) = Dn
                        .Offset(2, 1) = Format(Dn.Offset(, 1), "dd/mm/yyyy")
                    End With
                 End If
            Next Dn
    End With
End If
Next Ws
End Sub

Examples of sheets:
Each worksheet details tasks in a project.

"ERP Project" Worksheet
<table class="MsoNormalTable" style="width: 233.4pt; margin-left: 4.65pt; border-collapse: collapse;" width="311" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 13.5pt;"> <td style="border: 1pt solid windowtext; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> ERP Project
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom">
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> Stage
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom"> Due Date
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 55.4pt; height: 13.5pt;" width="74" nowrap="nowrap" valign="bottom"> Finish Date
</td> </tr> <tr style="height: 13.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 125pt; height: 13.5pt;" width="167" nowrap="nowrap" valign="bottom"> Examine Legacy System
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 53pt; height: 13.5pt;" width="71" nowrap="nowrap" valign="bottom">

01/09/2010