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.

 

[mj0lnr]

thanks Brett....but, no, I'm trying to select the first cell without a hyperlink and select all cells in H thereafter that have data, but do not have a hyperlink.

TIA,
A.

 

[NateO]

My procedure works yes-no? You could test the cell length, if cbool(len(cl)) then ....

 

[mj0lnr]

yes, your procedure works great. But it's stops at the first non-hyperlinked cell. I'd like to pass that cells info onto another subroutine while your subroutine keeps looking for H cells with data (in this case numbers) and selects all those cells with data, but no hyperlink. the cells must be selected in order for my next subroutine to work.

A.

 

[NateO]

Hello,

I could show you how to do this, but you don't need to do this. What does your next procedure look like?

 

[mj0lnr]

Nate,

If you follow what Parry helped me out with earlier:

http://www.mrexcel.com/board2/viewtopic.php?t=72209

here's the code that I'm currently using that works, but I'd like to expand it to the point where it's idiot-proof (I thought it was fully functional 'cept you'd be amazed at what some ppl can decide is just too confusing!)

Sub HyperLinkColH() 
Dim MyAddress As String, NBR As Long, c 

'Find next blank row in column H. Start at row 23 if its blank. 
If IsEmpty(Range("h23")) Then 
NBR = 23 
Else 
NBR = Range("h65536").End(xlUp).Row + 1 
End If 

'Look thru selection and place hyperlink in Col H if the 
'cell is a number 
For Each c In Selection 
If IsNumeric(c) Then 
MyAddress = "http://IM/Peregrine/WhatsMyStatus/default.asp?ProbID=IM" _ 
& c & "&submit1=Get+Ticket+Information&SearchName=" 

Cells(NBR, 8).Hyperlinks.Add Anchor:=Cells(NBR, 8), _ 
Address:=MyAddress, TextToDisplay:=Str(c.Value) 
NBR = NBR + 1 
End If 

Next c 

End Sub

What I'm trying to accomplish is to have the H cells selected for the user

Much Thanks in advance...!

 

[brettvba]

Do you mean something like this?

Private Sub SelectAllNonHyp()

  Dim oRange As Range
  Dim oTargetRange As Range
  Dim sFirstRange As String
  Dim sSelect As Variant

  Set oRange = ThisWorkbook.Sheets(1).Range("h23:h68")
  For Each cel In oRange
      
      If cel.Hyperlinks.Count = 0 Then
          If Cnt = 0 Then sFirstRange = cel.Address
          Cnt = 1
          If cel.Hyperlinks.Count = 0 Then
              sSelect = sSelect & "," & cel.Address
          End If
        
       
      End If
  Next

  ThisWorkbook.Sheets("sheet1").Range(Right(sSelect, Len(sSelect) - 1)).Select
End Sub

But when having more rows than specified above the proceedure crashes (not sure why do you know Nate?)

Brett

 

[NateO]

Yes, you've tried to pass too long a string to the Range object.

You could use union, but this is bollox slow on larger ranges, and there's no need to select cells...

I'll have to look at what Parry has given you.

 

[NateO]

Okay, if you want to select, so be it. Try the following:

Sub kdsjfkdsf()
Dim cl As Range, y As String, myRng as Range
On Error Resume Next
For Each cl In Range([h23], [h65536].End(3))
    y = cl.Hyperlinks(1).Name
    If Err.Number <> 0 Then
        If myRng Is Nothing Then Set myRng = cl Else _
            Set myRng = Union(cl, myRng)
        Err.Clear
    End If
Next
myRng.select
set myRng = nothing
End Sub

I have not tested this.

 

[brettvba]

Yes, I figured it was something like that, the only reason I could think that he would want to select cells like that would be to highlight cells with no hyperlink for viewing otherwise.....

Brett

 

[mj0lnr]

PERFECT!!! Now where can I pass the cell that is selected first onto Parry's sub? Would it by "y"?

A.