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 theHyperlinks object
returned by the cell'sHyperlinks 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 useIf 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