turn strings into hyperlinks with VBA

ajetrumpet

Banned
Local time
Today, 03:18
Joined
Jun 22, 2007
Messages
5,638
hello all,

I have this code:
PHP:
Function test()

Dim r As Range

   For Each r In Range("a1", "a905")
      If InStr(r, "http://") > 0 Or InStr(r, "www.") > 0 Then
         Range(r.Address).Select
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
                                               Address:="www.google.com", _
                                               TextToDisplay:="www.google.com"
      End If
   Next r

End Function
the function works with literal strings for address and text to display, but if I use the 'R' variable in place of the strings, I get a runtime error 5, invalid call or argument. the args for address and TTD are strings, so I have tried cstr(r) in both and I still get the error. Can someone help me out here? I need to use the 'R' or 'R.VALUE' to turn the current cell's value into a hyperlink with the same text to display. thanks!
 
Works fine for me when written like this:

Code:
Function test()
Dim r As Range

   For Each r In Range("a1", "a905")
      If InStr(r, "http://") > 0 Or InStr(r, "www.") > 0 Then
         Range(r.Address).Select
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
                                               Address:=r.Value, _
                                               TextToDisplay:=r.Value
      End If
   Next r

End Function
 

Users who are viewing this thread

Back
Top Bottom