identify earliest & latest date groups in table

**MickG** · 07-18-2011, 09:16 AM

Sorry, what I meant was, are any of the Data cells That should have Text Numbers or Dates Blank or any of the Data cells that should contain Dates, Blank.
Example :- Your data is in Columns "L to O", if say there are 100 rows in those columns with data if any cell within this range is blank or has not got a date, you could get that error.
If there are blanks and that is how your data would normally be presented to you, then I can perhaps alter the code to take account of that, but I was trying to understand where the error came from.
Mick

**Brontosaurus** · 07-18-2011, 10:25 AM

Sorry I misunderstood.

The data in columns L & M is alphanumeric for grouping. The data in N & O is date format, can be over 1000 rows but there are occasional blanks.

Thanks

Louise

**Brontosaurus** · 07-18-2011, 10:26 AM

I forgot to say - also row 1 contains text headings.

Louise

**MickG** · 07-18-2011, 11:13 AM

Try this:-

Sub MG18Jul13
Dim Rng     As Range
Dim Dn      As Range
Dim Q       As Variant
Dim Twn     As String
Dim Dt1 As Date
Dim Dt2 As Date
Set Rng = Range(Range("L2"), Range("L" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
       .CompareMode = vbTextCompare
For Each Dn In Rng
    If Application.CountA(Dn.Resize(, 4)) = 4 Then
    Twn = Dn & Dn.Offset(, 1)
        Dt1 = Format(Dn(, 3), "dd/mm/yyyy")
        Dt2 = Format(Dn(, 4), "dd/mm/yyyy")
        If Not .Exists(Twn) Then
            .Add Twn, Array(Dn, Dn(, 2), Dt1, Dt2)
        Else
            Q = .Item(Twn)
            If Dt1 < DateValue(Q(2)) Then Q(2) = Dt1
            If Dt1 > DateValue(Q(3)) Then Q(3) = Dt2
            .Item(Twn) = Q
        End If
  End If
Next
 Sheets("sheet2").Range("A1").Resize(.Count, 4) = Application.Index(.items, Evaluate("row(" & 1 & ":" & .Count & " )"), Array(1, 2, 3, 4))
End With
End Sub

Regards Mick

**Brontosaurus** · 07-18-2011, 11:35 AM

Hi Mick,

I get a Compile error - expected End With

This sounds simple but not sure where to put it?

Thanks

Louise

**MickG** · 07-18-2011, 11:45 AM

Sorry , Bad Copying.
I've altered the code above
"End With" is right at the bottom just above "End Sub"
Mick

**Brontosaurus** · 07-18-2011, 11:51 AM

Run time error 13 - type mismatch

the debugger is highlighting this line;

Sheets("Group Dates").Range("A1").Resize(.Count, 4) = Application.Index(.items, Evaluate("row(" & 1 & ":" & .Count & " )"), Array(1, 2, 3, 4))

I get the feeling we're nearly there

Thanks for your patience

Steve

identify earliest & latest date groups in table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: identify earliest & latest date groups in table

Re: identify earliest & latest date groups in table

Re: identify earliest & latest date groups in table

Re: identify earliest & latest date groups in table

Re: identify earliest & latest date groups in table

Re: identify earliest & latest date groups in table

Re: identify earliest & latest date groups in table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions