Shortcut Usage – Urgent

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

Zeddy,

44299-Joe-Cool

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Zeddy:

Yes Totally Awesome!!!

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

DuthieT

Reply | Quote

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

Reply | Quote

Hi RG

..many thanks for that cool pic!

zeddy

Reply | Quote

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

Reply | Quote

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!

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Meh! I took the time after work to actually read the OP. My interpretation was not correct

Reply | Quote

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

Reply | Quote