Hyperlink Multiple URL Macro

[John Caines]

Hello All.
Sometime ago, I put a post up for a hyperlink Macro, to convert various URLs To active hyperlinks to open their respective webpage.

The URLs (Which at the moment aren't hyperlinked can look like the following;

4cm.com 
4crossfire.com 
4her4him.net 
abbeytrade.com 
abcnews.go.com 
abekaacademy.org 
http://airlines-flight.net 
http://airlines-hq.com 
http://airline-sports-concert-show-tickets.com 
http://airman.com 
http://airner.info 
http://airplanetickets.fjore.info 
http://airplanetickets.fjore.info 
soundclick.com 
soundexpressiongreetings.com 
soundgrid.net 
southafrica.info 
http://www-suif.stanford.edu 
http://www-sul.stanford.edu 
http://www-sul.stanford.edu 
http://www-truck-deals.info 
http://www-truck-news.info

As you can see some don't even have the Http:,,,some don't have the www. but they are all websites which I'd like to be able to click on and open in my web browser.

VOGII helped me with a macro for this that I thought worked,, but I just couldn't have tested it correctly.
I've been trying for ages today, and I just can't get this to open all the above as active hyperlinks. (Actually it seems to only convert the first URL?)
VOGII's code was;

Sub HyperlinkConverter2() 
Dim lastrow As Long, i As Long 
Dim web As String 
Dim t As Date 
t = Now() 
Application.ScreenUpdating = False 
Application.Calculation = xlManual 
lastrow = Cells(Rows.Count, 2).End(xlUp).Row 
For i = 4 To lastrow 
    web = Cells(i, 2).Value 
    If Left(web, 7) <> "http://" Then web = "http://" & web 
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:=web, TextToDisplay:=web 
Next i 
Application.ScreenUpdating = True 
Application.Calculation = xlAutomatic 
MsgBox "Hyperlink Converting Run Time: " & Format(Now() - t, "hh:mm:ss") 
End Sub

The URL List to be converted started from cell C4 by the way. There are approx 30,000
URL's to convert.

If someone can help me on this It would be most appreciated.

Many Thanks Everyone
John Caines

 

[VoG]

John

This works for me with the examples that you posted:

Sub HyperlinkConverter3()
Dim lastrow As Long, i As Long
Dim web As String
Dim t As Date
t = Now()
Application.ScreenUpdating = False
Application.Calculation = xlManual
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
For i = 4 To lastrow
    web = Cells(i, 3).Value
    If Left(web, 7) <> "http://" Then web = "http://" & web
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=web, TextToDisplay:=web
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
MsgBox "Hyperlink Converting Run Time: " & Format(Now() - t, "hh:mm:ss")
End Sub

The only difference is that this is operating on column C whereas the previous code was for column B.

 

[John Caines]

Hyperlink Macro

Many Thanks For Your reply VOGII.

Yes, it does appear to work on the example I Posted.
I should have posted the part of the list that doesn't work really
(Stupid of me...)
I have a large list of URL's to hyperlink.
Here is the first few, which, when I use your macro VOGII,
It comes up with a debug error with this line of code highlighted in yellow.

 ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=web, TextToDisplay:=web

here's the first few URL's from my list.
As a note VOGII, when I run your Macro, it only turns the first URL
(www.clubdirect.com) blue.

Here's the start of my list;

www.clubdirect.com
 www.ecvv.com
0-0.com
000apartments.com
000city.com
00357.com
01gf.com
01webdirectory.com
02z.info
0861agents.co.za
0932303888.com.tw
0rlando-vacation.org
1-2-c.com
1-2-hotels.com
1-4a.com
1-800-translate.com
1-atm.com
1-bangkok-thailand-hotels.com
1-costaricalink.com
1-miami-vacation-rentals.com
1-realestate-directory.com
1-resources.com
1-webtipps.de

There is actually 24,979 URLs in this list.
I'm guessing here, but is the macro having trouble with numbers ,,,,,,,,,,,,
Wait,,,, hang on here,,,,, I have just found the problem as I'm typing this VOGII
Man,,,, I just didn't see it! I've been trying for the last 3 days,,,, and I can just
see it now whilst writing this post.
How the hell have I missed this,,, God, sorry about this, I just haven't seen the
mistake even though it's right in front of my eyes

In the list I've just posted, it's "www.ecvv.com"
There is a space at the beginning of the URL and as such, the macro hasn't picked it up!
Looks so obvious in this post now I'm focusing on it, but just didn't see it
in the spreadsheet. Maybe I need to increase my font size!

I really can't believe this VOGII.
Well, at least it's case closed.
Just ran it on the large list.
Took 17 seconds

Many thanks VOGII.
Simple mistake by me.
Funny how sometimes we have things right before our eyes and yet just can't see them

All the best
John Caines