Subscript out of range

[kenguru]

Hi,

I have an Excel and on some of the 'D' column elements there is a Hyperlink. I need that links so I wrote the following code:
record = "D" & i ' (i is incresing in every step)
Range(record).Select
If Len(Selection.Hyperlinks(1).Address) > 0 Then (*)
link = Selection.Hyperlinks(1).Address
Else
link = ""
End If

But when there is no hyperlink it gives me the error: "subscript out of range" on the (*) line.
I tried using: Selection.Hyperlinks(1).Address <>""

Any idea?

Or maybe another code which gives me these hyperlinks?

Thank you,
Kenguru

 

[SkipVought]

Hi,

Do you mean...

If Len(Selection.Hyperlinks(1).Address) > 0 Then  Selection = "(*)"

or WHAT?

Skip,

_[red][/red]

 

[kenguru]

No, I put there (*) because I wrote it later on the text that the error occured on that line.

The code is:
record = "D" & i
Range(record).Select
If Len(Selection.Hyperlinks(1).Address) > 0 Then
link = Selection.Hyperlinks(1).Address
Else
link = ""
End If
Kenguru

 

[SkipVought]

Sorry. Subscript out of range usually happens when there is no reference.

When you get the error, DEBUG, and use the Watch Window to get the ROW (value of i). Chances are, there is no hyperlink in column D in that row.

Skip,

_[red][/red]

 

[SkipVought]

What are you actually trying to do?

Would it not be better to loop thru the Hyperlinks Collection?

Skip,

_[red][/red]

 

[kenguru]

Yes i know that there is no hyperlink in that row. I wrote in my first lines that only in some of the lines is hyperlink. But how could i avoid the error ?
I tryed checking if it is "", but no luck.

 

[SkipVought]

What are you trying to accomplish? What's the end result?

Skip,

_[red][/red]

 

[kenguru]

I need that hyperlinks, because i have to introduce them in a database. If there is no hyperlink that is no problem.

 

[SkipVought]

assign firstrow & lastrow values

for r = firstrow to lastrow   
   for each hl in cells(r, "D")
      link = hl.address
   next
next

Skip,

_[red][/red]

 

[kenguru]

what is hl ?

 

[SkipVought]

oops...

dim hl as hyperlink
for r = firstrow to lastrow   
   for each hl in cells(r, "D").hyperlinks
      link = hl.address
   next
next

Skip,

_[red][/red]