Results 1 to 2 of 2

Thread: If Cell Contains Hyperlink Count
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If Cell Contains Hyperlink Count

    Hello I am trying to construct a vba macro to count everytime a cell contains a hyperlink in range of data. Currenly having it search the row for a hyperlink and then if it finds a hyperlink it will count and not delete the row, deleting the row if it doesn't contain a row. I am currently having trouble defining the hyperlink so if the active cell value contains a hyperlink that says "jama" in it, the macro will count it. Any ideas?

    Sheets("Issue Navigator").Activate
    Range("A1").Select
    Do While ActiveCell.Value <> ""
    If ActiveCell.Value <> "" Then
    TotalVal = TotalVal + 1
    ActiveCell.Offset(0, 1).Select
    Else
    End If
    TotalVal = TotalVal + 1
    ActiveCell.Offset(0, 1).Select
    Loop


    UpperBound = Range("A2").Select
    Selection.End(xlDown).Select
    LowerBound = ActiveCell.Row - 1


    Range("A2").Select


    TotalValNoChange = TotalVal
    'This is the section I am trying to count the hyperlink
    Dim HL As Hyperlink
    For i = 1 To LowerBound
    Do While TotalVal > 0
    If ActiveCell.Value <> HL Then
    Else
    JamaCount = JamaCount + 1
    End If
    ActiveCell.Offset(0, 1).Select
    TotalVal = TotalVal - 1
    Loop
    If JamaCount <= 0 Then
    Selection.EntireRow.Delete
    Else
    ActiveCell.Offset(1, 0).Select
    Range("A" & ActiveCell.Row).Select
    End If
    TotalVal = TotalValNoChange
    JamaCount = 0
    Next i
    '
    End Sub
    Posted on Microsoft Community: https://answers.microsoft.com/en-us/...=1562601785850

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: If Cell Contains Hyperlink Count

    To check whether a cell contains a hyperlink, and whether the hyperlink displays the text "jama", try...

    Code:
        If ActiveCell.Hyperlinks.Count > 0 Then
            If UCase(ActiveCell.Hyperlinks(1).TextToDisplay) = "JAMA" Then
                'increment count
            End If
        End If
    Note that UCase(...) = "JAMA" is used so that the comparison is not case-sensitive.

    Hope this helps!
    Last edited by Domenic; Jul 8th, 2019 at 01:59 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •