add href hyperlinks to excel range programmatically

Question

This has got to be a common problem with a simple answer but I can't seem to turn up a solution. Using an Excel macro, I examine a website home page for links and put those links into a range in Excel.

Now I want to make those values into hyperlinks.

Set allLinks = objIE.document.GetElementsByTagName("A")
For Each link In allLinks
    If InStr(link.href, inspectOneCat) Then
        inspectLink(linkCount) = link
        linkCount = linkCount + 1
    End If
Next

In the next step, the one-dimensional array is converted to a two-dimensional array with a descriptive column in inspectLink(i,0) and the link values in inspectLink(i,1). Then the array loaded into a range, like this:

        Sheets("Awesomepova").Range("a2:b300").Value = inspectLink

This works. But these links appear as values, not as hyperlinks. I want to do something like this:

    'Sheets("Awesomepova").Hyperlinks.Add Sheets("Awesomepova").Range("a2:b300"), Sheets("Awesomepova").Range("a2:b300").Value

This doesn't work. But I went into the worksheet manually and changed the first cell so it was hyperlinked and I noticed that even when I reload the entire range programmatically, the hyperlink remains, so I'm thinking this is a characteristic of the cell format, not the actual data in the cell.

Maybe the problem can be fixed by applying the same formatting to all the cells in the column where the rule is to use the cell value as the hyperlink value.

Answer 1

This is the equivalent using a For i... loop

Public Sub TestMe()
    Dim myArr As Variant
    Dim i As Long

    myArr = Array("www.bbc.com", "www.stackoverflow.com")

    For i = 0 To UBound(myArr)
        Worksheets(1).Cells(i + 1, 1).Hyperlinks.Add Worksheets(1).Cells(i + 1, 1), myArr(i)
    Next i
End Sub