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	www.excel.com
Tutorial on SQL	www.sql.com
Tutorial on VBA	www.vba.com
Tutorial on Java	www.java.com
Tutorial on HTML	www.html.com
Tutorial on REGEX	www.regex.com

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

Column A                            Column B

Tutorial on Excel	www.excel.com
Tutorial on SQL	www.sql.com
Tutorial on VBA	www.vba.com
Tutorial on Java	www.java.com
Tutorial on HTML	www.html.com
Tutorial on REGEX	www.regex.com

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

 Loop

 End With

End Sub

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.

Examples

Round some prices to nearest 5 cents
Price	Formula	Rounded Value
$7.99	MROUND(A3,0.05)	8
$7.45	MROUND(A4,0.05)	7.45
$7.49	MROUND(A5,0.05)	7.5
$7.44	MROUND(A6,0.05)	7.45
Round some start times to nearest 15 minutes
Start Time	Formula	Rounded Value
6:53 AM	MROUND(A10,"0:15")	7:00:00 AM
5:08 AM	MROUND(A11,"0:15")	5:15:00 AM
9:23 AM	MROUND(A12,"0:15")	9:30:00 AM
2:38 AM	MROUND(A13,"0:15")	2:45:00 AM
Round a negative value
Price	Formula	Rounded Value
($7.92)	MROUND(A18,-0.05)	-7.9