If you find any post useful then, please do share with others. Thanks!

Popular Posts


Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Thursday, August 4, 2016

Macro to add hyperlink for a list (Excel VBA)

We can use the below Excel Macro to add hyperlinks for a list. For example, we have a list of text to display and in next column a list of websites. This Macro will add the respective website as hyperlink.
So in our active sheet, in column 'A', we have some text which we want to be hyperlinked to website available in next column. Here in this Macro the list length is 6, which we can change as per our requirement.

Column A                            Column B

Tutorial on Excel
Tutorial on SQL
Tutorial on VBA
Tutorial on Java
Tutorial on HTML
Tutorial on REGEX

The data will look like this after running the Macro; the Column A is hyperlinked now.

And below is the VBA Code/Macro
Sub Amiq()
With ActiveSheet
Dim counter As Integer
counter = 1
Do While counter <= 6
.Hyperlinks.Add Anchor:=.Cells(counter, "A"), _
 Address:=Cells(counter, "B").Value, _
 ScreenTip:="Web Site", _
 TextToDisplay:=Cells(counter, "A").Value
counter = counter + 1
 End With
End Sub

Thursday, June 30, 2016

Excel MROUND Function

The Excel MROUND function is to round values to the nearest multiple. This function is available in Excel 2007 & above.

The syntax of this function is MROUND(number, multiple). Here the number is the cell or value and multiple is what you want your value to round to.

We can round the time as well. For example we have start times and want to round it to nearest 15 minutes.

When we want to round time, the multiple is written is double quotes same as time format e.g. =MROUND(A2,”0:15”).

And for normal numbers the multiple is entered without double quotes.

And when we round negative numbers, the multiple is entered in same sign, otherwise we will get error.

And another point to note that this function will round up or down to the nearest multiple and not in a fixed direction.

For fixed direction, we can use ROUNDUP or ROUNDDOWN functions.


Round some prices to nearest 5 cents
Rounded Value
Round some start times to nearest 15 minutes
Start Time
Rounded Value
6:53 AM
7:00:00 AM
5:08 AM
5:15:00 AM
9:23 AM
9:30:00 AM
2:38 AM
2:45:00 AM
Round a negative value
Rounded Value