I have data in Column A in excel..I am iterating through column and i need to find if a cell value has hyperlink init.

LR=Activeworkbook.Worksheets("Emp").Range("A65000").End(xlup).Row
for j=1 to LR
  if Thisworkbooks.Worksheets("Emp").cells(j,1)="" then 'Logic to find hyperlink
    'Function 
  end if

next

CodePudding user response：

Here is something that can be used to run through each row to determine if it can be set as a hyperlink. Kinda hard to figure out what the range of possible solutions are that will work for you without fully understanding the context...

Private Sub cmdFollowLink_Click() 

CreateHyperlink Me!cmdFollowLink, Me!txtSubAddress, _ Me!txtAddress End Sub

Sub CreateHyperlink(ctlSelected As Control, _ strSubAddress As String, Optional strAddress As String) Dim hlk As Hyperlink Select Case ctlSelected.ControlType Case acLabel, acImage, acCommandButton Set hlk = ctlSelected.Hyperlink With hlk If Not IsMissing(strAddress) Then .Address = strAddress Else .Address = "" End If .SubAddress = strSubAddress .Follow .Address = "" .SubAddress = "" End With Case Else MsgBox "The control '" & ctlSelected.Name _ & "' does not support hyperlinks." End Select End Sub

CodePudding user response：

Identify Cells Containing Hyperlinks

• As Red Hare already mentioned in the comments, it is best tested with something like the following:

  Dim cell As Range: Set cell = Sheet1.Range("A1")
  If cell.Hyperlinks.Count > 0 Then ' has a hyperlink
  Else ' has no hyperlink
  End If
  

  that is, using the Hyperlinks.Count property of the Hyperlinks object returned by the cell's Hyperlinks property which is a collection of hyperlinks in a range (in this case, a single cell). For a single cell, the Count property will return only 0 or 1 so you could actually use

  If cell.Hyperlinks.Count = 1 Then ' has a hyperlink
  

  instead.

Example Code

Option Explicit

Sub IdentifyCellsWithHyperlink()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' If it's not, modify accordingly.
    
    Dim ws As Worksheet: Set ws = wb.Worksheets("Emp")
    Dim rg As Range
    Set rg = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
    
    Dim cell As Range
    
    For Each cell In rg.Cells
        If cell.Hyperlinks.Count > 0 Then ' has a hyperlink
        
        Else ' has no hyperlink
        
        End If
    Next cell

End Sub