Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Shortcut Usage - Urgent

    Hi,


    I am in need of a macro that would help me in finding the details as to how many times a shortcut being used.


    Attached is the file with tabs as Data, Shortcuts, Usage.


    Now, I want that when I run that macro, it should check the text listed in "Text" field of "Shortcuts" in the "Data" tab and if it finds that text, it should add the number to the corresponding shortcut in the Usage tab.


    Any help on this will be highly appreciated.


    Thanks,
    Ankit
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Ankitag85:

    Attached is a formula based solution, no Maco needed.

    I did insert two rows in the data sheet and put the formulas on the data sheet.

    If you don't want to touch the data sheet then add a new sheet copy the formulas to that sheet and make sure the formula points to the proper cells in the Data and Shorcut sheets. Also copy all forumlas down as many rows as needed

    Good Luck

    DuthieT
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Duthiet is right. You don't need macros.
    You can use formulas to get what you want.

    So here's another formula-based solution.
    It doesn't require any changes or formulas on your [Data] sheet.
    It doesn't even require your [Usage] sheet.
    You can get the usage count directly alongside your shortcut definitions, using a shorter, simpler formula.

    see attached file

    zeddy
    Attached Files Attached Files

  4. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2016-04-28)

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Joe Cool.gif

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Zeddy:

    Yes Totally Awesome!!!

    I have never used CountIf and not only did you use CountIf you put it on Steroids.
    Great Solution.

    DuthieT

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi DuthieT

    I've had more steroids than I care to remember!!!

    Glad you liked that COUNTIF formula.
    We mostly use COUNTIF to count cells containing specific text, but it's easy to forget that you can also use 'wildcard' characters in the text criteria to be matched.
    There is also a COUNTIFS function you should check out (along with its cousins SUMIFS, and, in Excel 2016, MAXIFS, MINIFS, and IFS )

    zeddy

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    ..many thanks for that cool pic!

    zeddy

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Maybe I am reading it wrong but I think this is what you want to do. Clicking on the verbiage in column A will increment the totals on the usage sheet>

    Place in the sheet's module
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A2:A5")) Is Nothing Then
        x = WorksheetFunction.Match(Target.Offset(0, 1), Worksheets("Usage").Range("A2:A5"), 0)
        With Worksheets("Usage")
            .Cells(x + 1, 2) = .Cells(x + 1, 2) + 1
        End With
    End If
    [a1].Select
    End Sub
    HTH,
    Maud
    Attached Files Attached Files

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    I thought what an interesting reading of the OPs problem. Then I re-read it and you may well have hit the nail on the head. Guess we'll have wait for the OP to post back. Once again this points out the strength of the Lounge's "Many Eyes" approach to problem solving!

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2016-04-28)

  12. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Meh! I took the time after work to actually read the OP. My interpretation was not correct

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    I was playing around with this and I assume the OP is analysing some kind of on-line-chat log file.

    To demo this working in Excel, I have created my own list of 'shortcuts'.
    This uses the Microsoft 'Autocorrect' feature.
    Excel and all other Microsoft Office applications store AutoCorrect shortcuts in a single *.acl file in your Windows folder.

    Personally, I always define my shortcuts beginning with a z (of course), because this puts them at the bottom of the 'Autocorrect List' and thus are easy to find.

    In the attached file, I also put a worksheet-change event for the sheet named [Data]
    If you click the button to create the shortcuts, then when you type one of these shortcuts in column [A] on sheet [Data], the corresponding assigned text will be entered along with a timestamp and Username (as per the OP file sample).
    You can cancel the assigned shortcuts by clicking the [cancel shortcut] button in the attached file.

    Here's the code for the shortcuts:
    Code:
    'This routine creates a list of shortcuts.
    'The full text is listed in column [A]
    'The corresponding shortcut to be assigned is in column [B]
    
    Sub createShortcuts()
    
    Sheets("Shortcuts").Select
    
    zCount = [a1].CurrentRegion.Rows.Count
    
    '~~~~~~~~~~~~~~~~~~~~~~~~~~
    For r = 2 To zCount         'start on row 2; (i.e. ignore header row 1)
    zText = Cells(r, 1)         'fetch text value from column 1 = col A
    zShortcut = Cells(r, 2)     'fetch assigned shortcut from column 2 = col B
    
    Application.AutoCorrect.AddReplacement zShortcut, zText 'create shortcut
    Next r                      'process next row
    '~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    End Sub
    
    
    'This routine cancels the list of shortcuts
    '(as defined on sheet named [Shortcuts]
    
    Sub cancelShortcuts()
    
    Sheets("Shortcuts").Select
    
    zCount = [a1].CurrentRegion.Rows.Count
    
    On Error Resume Next        'skip expected error if shortcut already deleted
    '~~~~~~~~~~~~~~~~~~~~~~~~~~
    For r = 2 To zCount         'start on row 2; (i.e. ignore header row 1)
    zShortcut = Cells(r, 2)     'fetch assigned shortcut from column 2 = col B
    
    Application.AutoCorrect.DeleteReplacement zShortcut 'cancel  shortcut
    Next r                      'process next row
    '~~~~~~~~~~~~~~~~~~~~~~~~~~
    On Error GoTo 0             'reset error trap
    
    End Sub
    Others may find this useful to adapt for their use in Excel (and other Office programs)

    zeddy
    Attached Files Attached Files

Posting Permissions

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