We help IT Professionals succeed at work.
Get Started

Copy Date

Cartillo
Cartillo asked
on
281 Views
Last Modified: 2012-05-12
Hi Experts,
I would like to request Experts help. The attached the script has been used to copy only unique data from Column C (Data Sheet) to Detail sheet.

However, after I modified the Date from original format (previously the date is tagged with a day reference e.g. 12/11/2011(Tues) into standard date format (d/m/yyyy), the macro no logger copying the date value in Detail sheet (Column A).   Hope Experts will help me get the date value of the title. Attached the workbook for Experts perusal.
Sub Filter()
    
    Dim k(), ka, I As Long, d As String, n As Long, c As Long
    
    With Worksheets("Data")
        ka = .Range("a6:d" & .Range("a" & .Rows.Count).End(xlUp).Row)
    End With
    
    ReDim k(1 To UBound(ka, 1), 1 To 5)
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For I = 1 To UBound(ka, 1)
            If ka(I, 1) Like "*(*)" Then d = ka(I, 1): GoTo Nxt
            If Len(ka(I, 3)) Then
                If Not .exists(ka(I, 3)) Then
                    n = n + 1: k(n, 1) = d
                    For c = 1 To UBound(ka, 2)
                        k(n, c + 1) = ka(I, c)
                    Next
                    .Add ka(I, 3), Nothing
                End If
            End If
Nxt:
        Next
    End With
    If n Then
        With Worksheets("Detail")
            Union(.Range("b2").Resize(n), .Range("e2").Resize(n)).NumberFormat = "[h]:mm"
            .Range("a2").Resize(n, UBound(k, 2)) = k
        End With
    End If
    
End Sub

Open in new window

Date-Filter.xls
Comment
Watch Question
Most Valuable Expert 2012
Top Expert 2012
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
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
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