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

 

Check if list of Emails Valid or not (VBA Regex)

If we have a list of email addresses, like about 20,000 and we want to check if they are valid i.e. (valid format with an email id followed by ‘@’ and then domain name) then this macro can help us in doing this with a click.

Example sheet also available at end for Download

We will use VBA and the regular expression function (Regex).

To use Regex we need to do a few things beforehand.

Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

Ø For this select the “Developer tab”. If you don’t have it then (for Office 2010) go to File -> Options ->Customize Ribbons ->Popular Commands in Choose commands from -> Click on Developer tab box in customize the ribbon box.

Ø Now you can see the Developer tab on top, click on it and then on Visual Basic.

Ø Go to Tools -> References and select Microsoft VBScript Regular Expressions 5.5.




Now you are set to run the Regex Macros.

Below is the VB Code of Macro.

Sub amiq()

Dim strPattern As String: strPattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
Dim regEx As New RegExp
Dim strInput As String
Dim r As Integer

For r = 2 To Selection.Rows.Count
If strPattern <> "" Then
strInput = Cells(r, 1).Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With

If regEx.Test(strInput) Then

Cells(r, 2).Value = "Valid Email Address"
Else
Cells(r, 2).Value = "Not Valid"
End If
End If
Next r
End Sub


I have found this Regex pattern to check mail address validity from totorialspoint.

Please download the example Macro enabled workbook to see how it works. You just need to paste the list of mails in the first column then select and press the button. The second column will show their validity status.


Click Here to Download the Example File

Similarly if we want to identify the cases where text field have consecutive duplicate words and store the duplicate word in results, then we can use the below regex pattern.

Sub amiq()

Dim strPattern As String: strPattern = "\b(\w+)\b\s+\1\b"

Dim regEx As New RegExp

Dim strInput As String

Dim r As Integer

For r = 2 To Selection.Rows.Count

If strPattern <> "" Then

strInput = Cells(r, 6).Value

With regEx

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = strPattern

Set allMatches = regEx.Execute(strInput)

End With

If allMatches.Count <> 0 Then

Cells(r, 7).Value = allMatches.Item(0)

Else

Cells(r, 7).Value = "Valid"

End If

End If

Next r

End Sub

One Drop down List Dependent on Other (Excel 2010)

One Drop down List Dependent on Other

For example in first drop down, we select the type of food, i.e. American or Pakistani. And the second drop down shows us a list of respective food items.

To do this, we need to do the following easy steps.

On the second sheet of our workbook, create named ranges of following.

American	Pakistani	Types
Pizza	Chargha	American
Pasta	Chicken Karahi	Pakistani
Sausage	Naan Cholay

Creating a named range is very easy, for this go to formula tab and click on Define name. If you cursor is in the header row of column the box will pick it. In the “refers to”, add the range excluding the header row.

This way we will create three named ranges.

Now go back to sheet1 and in Column A1 & A2, add labels/heads like Type of Food and Food Item.

Have your cursor in row A2 and go to Data tab and click on Data Validation.

In the Allow box, select list. And in source box refer the named range of Type, like the below snapshot.

Now go to row B2 and, like we did earlier, go to Data tab and click on Data Validation.

In the Allow box, select list. And in source box enter this formula.

=INDIRECT($A$2)

And we are done. Now, the drop down values of second column, are dependent on first one.

Click here to download the example dependent drop down sheet

Gantt Chart in MS Excel 2010

Gantt chart

Note: Recently, I posted a fresh post, along video, on creation of Gantt Chart, which is more simple and easy

http://amiqexcel.blogspot.com/2021/01/gantt-chart-in-ms-excel-urduhindi-just.html

As per Wikipedia, the Gantt chart is a type of bar chart, adapted by Karol Adamiecki in 1896, and independently by Henry Gantt in the 1910s, that illustrates a project schedule. Gantt charts illustrate the start and finish dates of the terminal elements and summary elements of a project.

We can build this chart in Microsoft Excel also. Please see the step by step illustration.

1-      First we should have a list of data i.e. task table to build the chart.

Task	Start Date	Duration	End Date
Brain Storming	5/2	4	5/6
Listing Ideas	5/6	4	5/9
Selecting Idea	5/9	3	5/11
Estimating	5/11	2	5/12
Executing	5/11	2	5/13
Managing	5/12	2	5/14
Reporting	5/13	2	5/12
Meeting	5/13	2	5/12

2-      Make a Bar chart

From the top menu bar, select Insert then Bar and the 2D Stacked Bar. This will inert a blank chart.

3-       Have your mouse in blank Excel chart and click there and then left click -> Select Data. The source window will appear.

Click on legend entries (click add). This will open the series window.

In the series name box, select the cell reference of Start date column header. Like, if our data is in Column A to D, this would be B1.

In the series values box, select the data range of start date column i.e. b2:b9. Click ok and start dates data is in the chart

In same way add the durations.

Now, we will change the dates on the left side to list of tasks.

For this under Horizontal (Category) Axis labels, click on edit.

With mouse highlight the names of tasks and not include the name of column itself i.e A2:A9. Click ok.

Now chart looks like below.

Now Format the Gantt chart

Our task names are in reverse order to correct this, select task names and right click then

Format Axis. And then in Axis options, check the box of categories in reverse order and close.

For more space in chart, select the start date and duration legend/label with mouse and delete them.

Now hide the blue portions of the chart.

Click on any blue bar, this will select all, right click and choose Format Data series.

Ø  Click on Fill then select no fill

Ø  Now click on Border color and select No Line.

We are almost done. We just need to remove the empty extra area from the start.

Go to the first start date cell in the list, in our case it is A2. Now right click and select Format cell. In the Category section, select General. And note the number appearing, in our case it is 42126. Cancel as we are not changing anything, just need this number to use somewhere else.

In the Gantt chart select the dates appearing at top. Click there to select all and then right click and click on Format Axis.

In the Axis options change the minimum bound to the number, we noted.

Change the major unit to 2 and then press Close.

To remove most of the spaces and make the chart look nicer. Click on the top first bar and right click, select Format Data series.

Change the Gap width to 10%

We are finished, our Gantt chart look like this.