+ Reply to Thread
Results 1 to 10 of 10

VBA - Automate counting ranges in excel spreadsheet

  1. #1
    Registered User
    Join Date
    11-27-2018
    Location
    Washington, DC
    MS-Off Ver
    MS Office 2016
    Posts
    4

    VBA - Automate counting ranges in excel spreadsheet

    I have a table that looks like this:

    ER Visits
    Control #
    2055
    2056
    2057
    2058
    2059

    and I want to count how many ER visits are in the table. The catch is that I have a lot of excel spreadsheets with more or less ER visits. I need a VBA code that can count them for me automatically when I open each spreadsheet, without me editing this code over and over again:

    Sub ER()
    Range("G6") = WorksheetFunction.counta(Range("A20:A24"))
    End Sub


    Thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,805

    Re: VBA - Automate counting ranges in excel spreadsheet

    Try:

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-27-2018
    Location
    Washington, DC
    MS-Off Ver
    MS Office 2016
    Posts
    4

    Re: VBA - Automate counting ranges in excel spreadsheet

    Thanks for sending this. For some reason it doesn't count correctly. I should have 45 but it gives me 94...

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,805

    Re: VBA - Automate counting ranges in excel spreadsheet

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,185

    Re: VBA - Automate counting ranges in excel spreadsheet

    Try this
    Please Login or Register  to view this content.
    "ThankyouFor Attention * And Your Help!!"

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,805

    Re: VBA - Automate counting ranges in excel spreadsheet

    What can I say? It works for me. Only you know what your workbook, worksheet and data look like. I have to make it up ... and with what I made up, it works.

    I suspect you may have more data lower down the column, perhaps hidden. Who knows?

  7. #7
    Registered User
    Join Date
    11-27-2018
    Location
    Washington, DC
    MS-Off Ver
    MS Office 2016
    Posts
    4

    Re: VBA - Automate counting ranges in excel spreadsheet

    Okay, see attached a sample file.
    I have a lot of files like this, but the counts for those categories will vary from spreadsheet to spreadsheet and I need the code to count them for me instead of me counting them manually...
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,805

    Re: VBA - Automate counting ranges in excel spreadsheet

    OK, well you could just use a formula in cell G6:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you really feel the need for VBA, the simplest implementation (IMO) would be:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    11-27-2018
    Location
    Washington, DC
    MS-Off Ver
    MS Office 2016
    Posts
    4

    Re: VBA - Automate counting ranges in excel spreadsheet

    Thank you all for your help. I will adapt this to the rest of my files.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,805

    Re: VBA - Automate counting ranges in excel spreadsheet

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automate Macro When Opening Excel Spreadsheet
    By nate5539 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-30-2018, 12:21 PM
  2. [SOLVED] Counting Matching values in two separate ranges without counting duplicates
    By Rhall6310 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-01-2014, 03:35 PM
  3. Replies: 6
    Last Post: 09-27-2012, 10:45 AM
  4. vba to automate naming cell ranges
    By RAH in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2008, 01:13 PM
  5. Counting rows in Excel spreadsheet.
    By Jim15 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-02-2006, 05:17 PM
  6. [SOLVED] automate printing different ranges on 1 worksheet at different tim
    By SteveM in forum Excel General
    Replies: 2
    Last Post: 12-29-2005, 11:10 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1