Need help in macros

[baleshst]

Need a macro to extract email addresses from an excel sheet.

These email addresses could be anywhere in a sheet.

The format in which it shows is as follows

E-Mail: clasarch@bgl.vsnl.net

or
E-Mail: clasarch@bgl.com

or
E-Mail: clasarch@bgl.co.in

or
E-Mail: clasarch@bgl.com

I am using excel 2010.

Thanks in advance.

 

[saltkev]

Hi

I think this will do what you need. Can be run under a command button or Spread sheet event.

For Each netter In Range("b1:z100") 'Range of Sheet to Search
  
    If netter.Value Like "*.net" Or netter.Value Like "*.co.in" Or netter.Value Like "*.com" Then ' What to Search For
       
        netter.Copy
        Range("a1").Select
        
                If Range("a2").Value = "" Then ' Where to put Results
                
                   Range("a2").Select
                   ActiveSheet.Paste
                   GoTo ender
                 End If
                
                
                        If Range("a2").Value > "" And Range("a3").Value = "" Then ' Where to put Results
                
                             Range("a3").Select
                             ActiveSheet.Paste
                             GoTo ender
                       End If
                
                             If Range("a2").Value > "" And Range("a3").Value > "" Then ' Where to put Results
                                Range("a2").Select
                                Selection.End(xlDown).Select
                                Selection.Offset(1, 0).Select
                                ActiveSheet.Paste
                            End If
                
        
              
        End If
        
ender:
        
        Next netter

regards