Problem:
Mirosoft Excel is a wonderful office product and is an essential tool in our work life for most of us. But still many would have not discovered its capabilities to the fullest. Here is a problem that I wanted to solve. Typically, techies don't like to do same thing repeatedly and would look for a possible shortcut for that. I have been working through multiple excel sheets, where in have to find totals for a group of cells, without any specific parameters to determine the qualifying cells. If there is a shortcut to accomplish this it would be of help to many.
Solution:
Given that there are no parameters that determine the qualifying cells, it is difficult to come up with a an algorithm to automate the selection and totalling. But it would be possible to leave the selection to the users and then facilitate the summing part. We can leverage the clipboard to achieve this. Let the users select the desired cells and then invoke the macro using the assigned short cut key combination and the total is copied into the clipboard and available for pasting any where.
Here is the macro that does the work:
Sub mySum()
Dim MyDataObj As New DataObject MyDataObj.SetText Application.Sum(Selection) MyDataObj.PutInClipboard
End Sub
I am sure every one knows how to create a macro and assign a short cut key for it. You may find a lot of resources on creating a macro in excel.
No comments:
Post a Comment