+ Reply to Thread
Results 1 to 13 of 13

TC Counter -- identify and count number of instances in a selected column.

  1. 10-16-2012, 04:58 PM #1
    mrodrigues
    mrodrigues is offline
    Registered User
    Join Date
    10-16-2012
    Location
    US
    MS-Off Ver
    Excel 1997
    Posts
    16

    Question TC Counter -- identify and count number of instances in a selected column.

    Hello,

    I am trying to creat a VBA macro to locate cells in a manually selected column that have the text value "TC" and if this value is found then the cell value will be changed to TC1, then TC2, TC3, TC4, etc... (up to a max count of 1024).

    I'm starting out small (beginner to VBA) and trying to get TC to increment across a small population of a predefined column. This is what I have so far (though each of the 66 TC values in the column are renamed TC1 and do not increment):

    Please Login or Register  to view this content.
    Any help is appreciated. Thank you.
    Last edited by Cutter; 10-17-2012 at 03:56 PM. Reason: Added code tags
    Reply With QuoteRegister To Reply
  2. 10-16-2012, 05:20 PM #2
    mike7952
    mike7952 is offline
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: TC Counter -- identify and count number of instances in a selected column.

    Something like this.

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    Reply With QuoteRegister To Reply
  3. 10-16-2012, 05:32 PM #3
    mrodrigues
    mrodrigues is offline
    Registered User
    Join Date
    10-16-2012
    Location
    US
    MS-Off Ver
    Excel 1997
    Posts
    16

    Re: TC Counter -- identify and count number of instances in a selected column.

    Hello Mike,

    Yes. That does seem to get the job done. I'll try and wrap my head around your solution... I'm not sure what the $ means.

    Thank you.
    Michael
    Reply With QuoteRegister To Reply
  4. 10-16-2012, 05:44 PM #4
    mike7952
    mike7952 is offline
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: TC Counter -- identify and count number of instances in a selected column.

    It just takes the $ sign out of cell address and drops the number, so we get the Column Letter of the activecell. Test this

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 10-17-2012, 03:56 PM #5
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: TC Counter -- identify and count number of instances in a selected column.

    @ mrodrigues

    Welcome to the forum.

    Please notice that [CODE] tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at the top of the page and read Rule #3.
    Thanks.
    Reply With QuoteRegister To Reply
  6. 10-18-2012, 05:09 PM #6
    mrodrigues
    mrodrigues is offline
    Registered User
    Join Date
    10-16-2012
    Location
    US
    MS-Off Ver
    Excel 1997
    Posts
    16

    Re: TC Counter -- identify and count number of instances in a selected column.

    OK. Thanks Cutter.
    Reply With QuoteRegister To Reply
  7. 10-18-2012, 05:11 PM #7
    mrodrigues
    mrodrigues is offline
    Registered User
    Join Date
    10-16-2012
    Location
    US
    MS-Off Ver
    Excel 1997
    Posts
    16

    Re: TC Counter -- identify and count number of instances in a selected column.

    Is there a way to make the code a little more discerning so that ONLY a TC value in a cell is picked up, and not a value that contains TC? For example if there were some cells values that were TC and others that were TCS and you would want to ignore the TCS values and only apply this to TC.
    Reply With QuoteRegister To Reply
  8. 10-18-2012, 05:17 PM #8
    mike7952
    mike7952 is offline
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: TC Counter -- identify and count number of instances in a selected column.

    Try this

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 10-18-2012, 09:58 PM #9
    mrodrigues
    mrodrigues is offline
    Registered User
    Join Date
    10-16-2012
    Location
    US
    MS-Off Ver
    Excel 1997
    Posts
    16

    Re: TC Counter -- identify and count number of instances in a selected column.

    This seems to take a very long time to execute -- it actually seems to lock up on me (Excel is not responding for a while now), Mike.

    Isn't there a way to qualify the:
    Please Login or Register  to view this content.
    So only TC would be selected and anything with characters after TC would not be affected? Is there a way to do that?
    Reply With QuoteRegister To Reply
  10. 10-18-2012, 10:06 PM #10
    mike7952
    mike7952 is offline
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: TC Counter -- identify and count number of instances in a selected column.

    Opps I put the end if in the wrong spot try this, Put the End if above Set FoundCell

    Please Login or Register  to view this content.
    Last edited by mike7952; 10-18-2012 at 10:09 PM.
    Reply With QuoteRegister To Reply
  11. 10-18-2012, 10:16 PM #11
    mike7952
    mike7952 is offline
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: TC Counter -- identify and count number of instances in a selected column.

    Or even this added LookAt:=xlWhole to only look at TC

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 10-19-2012, 12:54 AM #12
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: TC Counter -- identify and count number of instances in a selected column.

    This should be faster than Find method when many rows of data.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 10-29-2012, 03:13 PM #13
    mrodrigues
    mrodrigues is offline
    Registered User
    Join Date
    10-16-2012
    Location
    US
    MS-Off Ver
    Excel 1997
    Posts
    16

    Re: TC Counter -- identify and count number of instances in a selected column.

    Thank you, jindon.

    That works great.

    Thanks for all your help too, Mike.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1