Need help with constructing a macro that counts dates as one day.

[estatefinds]

I have data in columns AB the contain dates in order from top row 20 to bottom of worksheet. in column AC it just numbers them in how many dates I have entered into column AB. In column AA this will be the result after the macro is run.
So I need a macro that will look at the date for example: in column AB row 20 is 04/17/2015 there is only one of these in column AB so a number one placed to the left in column AA row 20 1. the macro will keep running ac ount as in the excel sheet and the macro will see that the 04/28/2015 is entered twice the macro will recognize this and skip to the second 04/28/2015 and continue counting and place a 9 to the second 04/28//2015 down in the column and so on. if you have any questions please ask, also I will be adding data to the column AB so the macro needs to be AS LONG
Thank you in advance for your Help!

[estatefinds]

added corrected file, thank you

[mdmackillop]

You can do this by formula

=IF(COUNTIF(AB:AB,AB20)=COUNTIF(AB$1:AB20,AB20),MAX(AA$1:AA19)+1,"")

[Paul_Hossler]

1. THERE'S NO NEED TO SHOUT IN YOUR TITLES

2. From your other threads, I'm assuming that you want to embed a bit of VBA into a larger macro?

3. This seems to work with your data



Option Explicit
Sub CountDates()
Dim rStart As Range, rEnd As Range, rDate As Range
Dim iNumDates As Long
Set rStart = Worksheets("Sheet1").Range("AB20")
Set rEnd = rStart.End(xlDown)
rStart.Offset(0, 1).Value = 1
For Each rDate In Range(rStart, rEnd).Cells
If rDate.Value <> rDate.Offset(1, 0).Value Then
iNumDates = iNumDates + 1
rDate.Offset(0, -1).Value = iNumDates
End If

rDate.Offset(0, 1).Value = rDate.Offset(-1, 1).Value + 1
Next
End Sub

[estatefinds]

Thank you very much!!!!!

[estatefinds]

Excellent work Thank you!!!!

[mdmackillop]

If you have a formula solution, this can readily be converted to a macro.

Sub Test()
Set r = Range("AB:AB").SpecialCells(2).Offset(, -1)
With r
.FormulaR1C1 = "=IF(COUNTIF(C[1],RC[1])=COUNTIF(R1C[1]:RC[1],RC[1]),MAX(R1C:R[-1]C)+1,"""")"
.Value = .Value
End With
End Sub