Solved: Counting Models

[Emoncada]

I have a spreadsheet that I would like to know how i can get a running count of how many of each model.
So I have Column D that has the Model and I would like to display a running count in Column A.

Example
Column A.......Column B......Column C....... Column D........
1..................10.20.12......YDSHUDG........JC9200...........
2..................10.22.12......YDSEUDG........JC9200...........
3..................10.24.12......YDSEGDG........JC9200...........
1..................10.20.12......YDSDFDG........JC1300...........
2..................10.20.12......YDSDFDG........JC1300...........
3..................10.20.12......YDSDFDG........JC1300...........

And so on.
any ideas?

[Emoncada]

Oh, I would like to accomplish this via VBA since the sort is done via vba and I have another task that will run after this.

[Bob Phillips]

Sub RunningTotals()
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("F1").Resize(lastrow)

.Formula = "=COUNTIF($E$1:$E1,$E1)"
.Value = .Value
End With
End With
End Sub

[Emoncada]

Hey XLD, I modified the code you provided to this

Sub RunningTotals()
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A2").Resize(lastrow)

.Formula = "=COUNTIF($D$1:$D1,$D1)"
.Value = .Value
End With
End With
End Sub

To try to match what I need but I am only getting a "1" in A2 and that's it. I need for it to look at models in Column E and do a count in Column A

[Emoncada]

Ok, i fixed it to work, now just 1 little thing. I am getting a "0" after the last row in Column A.

Sub RunningTotals()
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("A2").Resize(lastrow)

.Formula = "=COUNTIF($D$2:$D2,$D2)"
.Value = .Value
End With
End With
End Sub

[Bob Phillips]

Sub RunningTotals()
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("A2").Resize(lastrow - 1)

.Formula = "=COUNTIF($D$2:$D2,$D2)"
.Value = .Value
End With
End With
End Sub

[Emoncada]

I knew it was going to be something like that.

Thanks XLD

[Bob Phillips]

Blimey, were you sitting there waiting for a reply :)

[Emoncada]

I tried

Sub RunningTotals()
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("A2").Resize(lastrow) - 1

.Formula = "=COUNTIF($D$2:$D2,$D2)"
.Value = .Value
End With
End With
End Sub


And got an error, so then I saw your reply. Well I was close.