Automating F2 to edit

[Darin Kramer]

Hi there,

In column f I have a formlae which uses a function to creae an email
address from a Excel hyperlink.
I then take the contents of column f and past values into column g - so
now within column g I have the actual email address - eg
(e-mail address removed). Problem is the Excel is not recognising this as an
email address. If I press F2 to edit the cell, change nothing, and then
press enter it recognises it as an email address.

Question is, how can I automate the process for all entries in column G,
ie edit the cell, and then "press enter" without making a change to the
contents?

Kind Regards

D

 

[James Snell]

The only way you can do the auto-stuff is via the UI, which means sendkeys
(which is very bad indeed).

Instead you'd need to loop through the values and add the hyperlinks in the
format "mailto:[email protected]" where links needed to be added.

Something like...

Set target = Sheet1.Range("c2")
Sheet1.Hyperlinks.Add target, "mailto:" & target.Value

 

[Darin Kramer]

Thanks for the reply - my VB skills are at the basic level... what would
I need next after the two lines you proposed...?

Regards

D

 

[James Snell]

That's just a demo of how to do one cell. Here's the full code.

Sub SetHyperlinks()
Dim target As Range

For Each target In Sheet1.Range("G1:G65535").Cells
If target.Hyperlinks.Count = 0 Then
If InStr(target.Value, "@") > 0 Then
target.Hyperlinks.Add target, "mailto:" & target.Value
End If
End If
Next target
Set target = nothing
End Sub