Usually in the sequential database system, we can SUM only unique values in table column by adding all the values from SELECT DISTINCT query result only.
Based on the same principle, we can also create a simple VBA function in Microsoft Excel by adding the values only available in a collection of unique values we have created before hand.
Public Function [...] Continue Reading…
Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here).
Sub CreateCommentsSummary()
Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer, iCol As Integer
[...] Continue Reading…
The purpose of the following example of Excel VBA macro is to get the acronym or an abbreviation of each of the first letter of any given words.
Function Acronym(Words As Variant) As String
Dim aWord() As String, ix As Integer
aWord = Split(Words.Value, ” “)
For ix = 0 To [...] Continue Reading…
If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel.
The VBA code is the exact replica of the code to get the last row, only this time, we change [...] Continue Reading…
Simple Microsoft Excel formula to find the last day of any given month.
‘
=DATE(YEAR(A1),MONTH(A1)+1,0)
‘
I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook.
Below is a sample of how we can do this in Microsoft Excel *just an outline*.
The logic is simple, when we open the Workbook, we call for [...] Continue Reading…
There is a time when we must create a set of Excel Worksheets templates on a regular basis in our work. For example, a set of Excel Worksheet for each month of the year or may be based on the type of work we should done in certain time base.
Here a simple example of how to create a new [...] Continue Reading…