Automating F2 to edit

D

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

*** Sent via Developersdex http://www.developersdex.com ***
 
J

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top