Add Telephone Hyperlink to cell

[jeffmoseler]

I would like to add a Teams phone call link to a column of phone numbers. I know that using a HYPERLINK formula with tel: in front works, but I need to use VBA add that formula for the whole column.

Here are some examples:

Cell A1: (555) 555-5555
=HYPERLINK(CONCATENATE("TEL:", A1)) (shows tel: (555) 555-5555) If I click on the link it takes me to Teams and makes the call.

How do I apply the HYPERLINK to the whole column?

I have come up with this but I know it is wrong:

Dim vPhoneNo As String

For Each cell In Sheet1.Range("N12:N500")
    If Not IsEmpty(cell) Then
        vPhoneNo = cell.Value
        vPhoneNo = "tel:" & vPhoneNo
        cell.Value = Hyperlinks.Add(vPhoneNo)
    End If
Next

What would you suggest?

 

[Logit]

.
See if this works :

Sub Test()
Dim cell As Range
Dim bIsEmpty As Boolean
Dim Hyperlinks
Dim vPhone As String

vPhone = Range("A1").Value
bIsEmpty = False
For Each cell In Range("N12:N500")
    If IsEmpty(cell) = True Then
        ActiveSheet.Hyperlinks.Add cell, "Tel :" & vPhone
     End If
Next cell
End Sub