Hyperlink formulas created shows #value

[mdd16]

Hello,

I am trying to create a code with which I can create a 'click to dial' hyperlink in a cell in excel which is referring to phone number in another cell.

I used the hyperlink function and want to enter it in the selected cell.

But the result shows #value error and the formula for hyperlink does not get entered in the selected cell.

Please help

Best Regards

Mohan Dhingra

Sub DoTelText()
Dim rng1, rng2 As Range, i As Long
Dim TelText As String

On Error Resume Next

'rng1 = Selection.Address(ReferenceStyle:=xlR1C1)

Set rng1 = Application.InputBox _
("Select the cell with phone number entry", Type:=8)
MsgBox rng1.Address

If rng1 Is Nothing Then
MsgBox "You selected nothing"
Exit Sub
Else
'rng1 = rng1.Address(ReferenceStyle:=xlR1C1)

TelText = "HYPERLINK(CONCAT(""tel:""," & rng1.Address & ")," & rng1.Address & ")"""
MsgBox TelText
Selection.value = "=" & TelText
End If
Selection.Formula = _
Application.ConvertFormula _
(Formula:=Selection.Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

'Fm_AddString.Hide

End Sub

 

[mdd16]

Corrected my own syntax errors..

Updated code below.. in case someone needs it


Sub DoTelText()
Dim rng1, rng2 As Range, i As Long
Dim TelText As String

On Error Resume Next

'rng1 = Selection.Address(ReferenceStyle:=xlR1C1)

Set rng1 = Application.InputBox _
("Select the cell with phone number entry", Type:=8)

If rng1 Is Nothing Then
MsgBox "You selected nothing"
Exit Sub
Else
'rng1 = rng1.Address(ReferenceStyle:=xlR1C1)

TelText = "IFERROR(HYPERLINK(CONCAT(""tel:""," & rng1.Address & ")," & rng1.Address & "),0)"

Selection.Formula = "=" & TelText
End If
Selection.Formula = _
Application.ConvertFormula _
(Formula:=Selection.Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)


End Sub