Consulting

Results 1 to 9 of 9

Thread: Solved: Counting Models

  1. #1
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location

    Solved: Counting Models

    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?

  2. #2
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    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.

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    [VBA]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
    [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    Hey XLD, I modified the code you provided to this

    [VBA]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[/VBA]

    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

  5. #5
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    Ok, i fixed it to work, now just 1 little thing. I am getting a "0" after the last row in Column A.

    [VBA]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[/VBA]

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    [VBA]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 [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    I knew it was going to be something like that.

    Thanks XLD

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Blimey, were you sitting there waiting for a reply
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    I tried [VBA]
    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
    [/VBA]

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •