Microsoft Excel guru (please help)

staffjam · 08-03-2008, 03:30 PM

Hi,

I'm trying to figure out how to put automatic hyperlinks in some web addresses - so they automatically click through to the site. e.g.:
www.robin.com
www.happy.net
www.bigboy.org

I've been trying to figure it out and i have 1000 sites to check out and really don't want to have to type them all in. I've tried inserting a hyperlink, but it doesn't come through as a live link.
Any help would be greatly appreciated.

Thanks

coreyg · 08-03-2008, 03:53 PM

Are you referring to URL forwarding at the Domain Name Registrar?

Not sure exactly what you are asking, if you supply more information that would be good.

Cheers
Corey

nasaboy007 · 08-03-2008, 04:27 PM

i think what he wants is a way to visit sites from a url in an excel file by just clicking...

one trick would be to take an empty column, and use the following:

Code:

=HYPERLINK(A1,A1)

where A1 is the cell that has the URL you want.

then just copy/paste that down (it should offer to fill in all of them if you just keep going down). it shoudl also automatically change A1 to A2, A3, A4, etc as you go down. then, you should be able to click those hyperlinks.

does that help?

sdsinc · 08-03-2008, 04:28 PM

I assume you have a column of URLs as non-clickable text correct ?
To convert the text to hyperlinks you could run a macro like this, assuming your range is "a1:a40"

PHP Code:

  Dim r As Integer, str As String

 
For r = 1 To Sheets("Sheet1").Range("a1:a40").Rows.Count ' scan rows

    str = Sheets("Sheet1").Cells(r, 1).Value ' read cell value

    str = "http://" & str ' add leading http://

    Sheets("Sheet1").Cells(r, 1).Select

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=str, TextToDisplay:=str ' convert to hyperlink

Next r

weblord · 08-03-2008, 04:40 PM

excel's default is treat all prefixed with "www" and/or "http://" as automatic hyperlinks so they're all clickable.

staffjam · 08-12-2008, 04:49 AM

Sorry for coming back late- been on holiday.

The list i'm talking about has been merged - so no hyperlinks were created.
Nasaboy - i tried what you suggested but didn't have any joy.
Sdsinc- yes i have a column of url's as non clickable text - sadly i haven't got a clue how to apply the macro you have written.

sdsinc · 08-12-2008, 08:33 AM

Quote:

Originally Posted by staffjam

Sdsinc- yes i have a column of url's as non clickable text - sadly i haven't got a clue how to apply the macro you have written.

Open Excel, then use menu Tools -> Macro -> Record new macro. Stop recording.
Again go to menu Tools -> Macro -> Macros, select the macro you've just recorded and click Edit. You will see a new window with code like this:

Code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/12/2008 by ...
'

'
End Sub

Paste the code between Sub & End Sub.
You may have to change the range (here a1:a40) to fit your needs.

Return to Excel. Use menu Tools -> Macro -> Macros, and click Run to execute the code. This should convert the URLs to clickable links.

staffjam · 08-12-2008, 02:47 PM

Thanks - i've tried it and still can't get it to work

08-03-2008, 03:30 PM	#1 (permalink)
staffjam NamePros Regular Join Date: Jun 2007 Posts: 775 318.75 NP$ (Donate)	Microsoft Excel guru (please help) Hi, I'm trying to figure out how to put automatic hyperlinks in some web addresses - so they automatically click through to the site. e.g.: www.robin.com www.happy.net www.bigboy.org I've been trying to figure it out and i have 1000 sites to check out and really don't want to have to type them all in. I've tried inserting a hyperlink, but it doesn't come through as a live link. Any help would be greatly appreciated. Thanks __________________ Buy Domain Names – 9i.com, 3J.com, CXC.com, FEJ.com, ZB.org, Foob.com, Wodo.com, Sixth.com – These are just a few of the great names on offer at namecake.com (Click below) Buy Domain Names Solar Garden Lights Virtual Reality Games

08-03-2008, 03:53 PM	#2 (permalink)
coreyg Senior Member Join Date: Mar 2008 Location: Sydney, Australia Posts: 2,834 173.84 NP$ (Donate)	Are you referring to URL forwarding at the Domain Name Registrar? Not sure exactly what you are asking, if you supply more information that would be good. Cheers Corey __________________ DWI Lawyer☻∙ Braille∙☻ For Michael Jackson WebsiteCheck☻∙ PageRank Checker Tool☻∙ Domain Names For Sale Free Domain Valuation \| Premium Availables ☻∙FMOZ.net Web Directory ☻∙

08-03-2008, 04:27 PM	#3 (permalink)
nasaboy007 Senior Member Join Date: Jul 2005 Location: NJ Posts: 1,112 1,454.30 NP$ (Donate)	i think what he wants is a way to visit sites from a url in an excel file by just clicking... one trick would be to take an empty column, and use the following: Code: =HYPERLINK(A1,A1) where A1 is the cell that has the URL you want. then just copy/paste that down (it should offer to fill in all of them if you just keep going down). it shoudl also automatically change A1 to A2, A3, A4, etc as you go down. then, you should be able to click those hyperlinks. does that help? __________________ Hacksar.com - Your source for random computer tips and tricks! MySiteMemberships.com - Keep track of your site registration information! Like my post? Rep is appreciated!

08-03-2008, 04:28 PM	#4 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 6,287 1,095.94 NP$ (Donate)	I assume you have a column of URLs as non-clickable text correct ? To convert the text to hyperlinks you could run a macro like this, assuming your range is "a1:a40" PHP Code: `Dim r As Integer, str As String For r = 1 To Sheets("Sheet1").Range("a1:a40").Rows.Count ' scan rows str = Sheets("Sheet1").Cells(r, 1).Value ' read cell value str = "http://" & str ' add leading http:// Sheets("Sheet1").Cells(r, 1).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=str, TextToDisplay:=str ' convert to hyperlink Next r` __________________ Buy now - MassDeveloper.com $500

08-12-2008, 04:49 AM	#6 (permalink)
staffjam NamePros Regular Join Date: Jun 2007 Posts: 775 318.75 NP$ (Donate)	Sorry for coming back late- been on holiday. The list i'm talking about has been merged - so no hyperlinks were created. Nasaboy - i tried what you suggested but didn't have any joy. Sdsinc- yes i have a column of url's as non clickable text - sadly i haven't got a clue how to apply the macro you have written. __________________ Buy Domain Names – 9i.com, 3J.com, CXC.com, FEJ.com, ZB.org, Foob.com, Wodo.com, Sixth.com – These are just a few of the great names on offer at namecake.com (Click below) Buy Domain Names Solar Garden Lights Virtual Reality Games

08-03-2008, 04:40 PM	#5 (permalink)
weblord NamePros Legend Join Date: Dec 2005 Location: Philippines - www.Nabaza.com Posts: 19,840 21,700.43 NP$ (Donate)	excel's default is treat all prefixed with "www" and/or "http://" as automatic hyperlinks so they're all clickable.

08-12-2008, 02:47 PM	#8 (permalink)
staffjam NamePros Regular Join Date: Jun 2007 Posts: 775 318.75 NP$ (Donate)	Thanks - i've tried it and still can't get it to work __________________ Buy Domain Names – 9i.com, 3J.com, CXC.com, FEJ.com, ZB.org, Foob.com, Wodo.com, Sixth.com – These are just a few of the great names on offer at namecake.com (Click below) Buy Domain Names Solar Garden Lights Virtual Reality Games

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)