Determing if a Hyperlink Exists

[mj0lnr]

All,

This one might be fairly simple (of course if that was simple, I'd have figured it out by now) but, what I'd like to accomplish is the following:

• 1)Comb thru a column (H) and check if a hyperlink exists
  2)If YES, leaves it alone
  3)If NOT, selects the cell and checks the rest of the H column for the same condition
  4)Selects all cells WITHOUT a hyperlink
  5)Determines 1st cell without a hyperlink

Many Thanks in advance,
A.

 

[brettvba]

That is fairly easy do you want the cell to go red or something if it doesn;t have a hyperlink?

Brett

 

[mj0lnr]

oh no, I just need to have it selected for my next sub to run.....

A.

 

[mj0lnr]

oh and one other thing, I need that 1st cell without a hyperlink to be a part of another subroutine. Is that possible? Seeing that it's a variable, would it be able to be incorportated into the next sub?

again, many thanks,
A.

 

[NateO]

I can't see any reason to select anything... What are in these non-hyperlink cells? Nothing?

You'll incorporate it into the same procedure or pass it.

 

[mj0lnr]

no, the non-hyperlink cells are something. Am I asking too much?

A.

 

[NateO]

No, not really:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> kdsjfkdsf()
<SPAN style="color:darkblue">Dim</SPAN> cl <SPAN style="color:darkblue">As</SPAN> Range, y <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">Resume</SPAN> <SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> cl <SPAN style="color:darkblue">In</SPAN> [h:h].SpecialCells(xlConstants, 2)
    y = cl.Hyperlinks(1).Name
    <SPAN style="color:darkblue">If</SPAN> Err.Number <> 0 <SPAN style="color:darkblue">Then</SPAN>
        MsgBox cl.Address
        Err.Clear
        <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN> <SPAN style="color:green">'******Quit after first*********</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

If you don't want them all, don't exit the sub.

 

[mj0lnr]

oh sorry Nate....I guess I should have said that, it needs to start at H23 and probably never go over H200

A.

 

[NateO]

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> kdsjfkdsf()
<SPAN style="color:darkblue">Dim</SPAN> cl <SPAN style="color:darkblue">As</SPAN> Range, y <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">Resume</SPAN> <SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> cl <SPAN style="color:darkblue">In</SPAN> Range([h23], [h65536].End(3))
    y = cl.Hyperlinks(1).Name
    <SPAN style="color:darkblue">If</SPAN> Err.Number <> 0 <SPAN style="color:darkblue">Then</SPAN>
        MsgBox cl.Address
        Err.Clear
        <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN> <SPAN style="color:green">'******Quit after first*********</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

 

[brettvba]

Mines probably not as snazzy as Nates but if your wanting to select the cell with the first hyperlink in it then this will work.

Sub SelectFirstHL()
Set x = ThisWorkbook.Sheets(1).Range("h23:h200").Hyperlinks
    If x.Count > 0 Then
        Cells(x(1).Range.Row, x(1).Range.Column).Select
    End If
End Sub

Brett