PDA

View Full Version : Solved: Counting Models



Emoncada
10-31-2012, 09:10 AM
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
10-31-2012, 09:11 AM
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.

xld
10-31-2012, 10:03 AM
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
10-31-2012, 12:05 PM
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
10-31-2012, 12:15 PM
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

xld
10-31-2012, 01:29 PM
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
10-31-2012, 01:30 PM
I knew it was going to be something like that.

Thanks XLD

xld
10-31-2012, 01:33 PM
Blimey, were you sitting there waiting for a reply :)

Emoncada
10-31-2012, 01:35 PM
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.