Get first cell with email address

[KGee]

I have a report where the number of recipients change as does the starting row where the email addresses for those recipients are listed. How do I determine the cell of the first row containing an email address? I need to use that cell to define the beginning of a range. The email addresses will be listed in column F and searching for the "@" should be adequate as that is the only column where the symbol is used.

 

[daverunt]

Hi,

This should find @ in the cell, exit the search when the first one is found and give you the cell address.

Sub Find_at()
 
Set Rng = Range(Range("F1"), Range("F" & Rows.Count).End(xlUp))
For Each Dn In Rng
If InStr(Dn.value, "@") > 0 Then
Addr = Dn.address(0, 0)
Exit For
End If
Next
MsgBox (Addr)
 
End Sub

 

[KGee]

Hi Dave,

This works but I have a follow up question. I'm actually calling this code from a button on another sheet which does not contain the list of email addresses and the code is written to run on the active sheet. I've tried specifying the name of the sheet with the email addresses but get an "application-defined or object-defined error" message.

Set Rng = Sheets("Emails").Range(Range("F1"), Range("F" & Rows.Count).End(xlUp))

As a workaround, I left the code intact and have it switch to the email sheet at the beginning then back to the report sheet at the end. The part I don't understand is why the command above also works when I switch back and forth between sheets.

Sub Find_at() 
Sheets("Emails").Select
[COLOR=red]' Either set range command works[/COLOR]
[COLOR=red]Set Rng = Range(Range("F1"), Range("F" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=red]Set Rng = Sheets("Emails").Range(Range("F1"), Range("F" & Rows.Count).End(xlUp))[/COLOR]
For Each Dn In Rng
If InStr(Dn.value, "@") > 0 Then
Addr = Dn.address(0, 0)
Exit For
End If
Next
MsgBox (Addr)
Sheets("Reports").Select
End Sub

I'm fine with what I have because it works but wanted to know how to set the range so I don't have to switch back and forth between sheets.

 

[daverunt]

Hi,


The code works if you specify the sheet in each part:
There must be a shorter way to write this so someone kindly show both of us.

Set Rng = Sheets("Emails").Range(Sheets("Emails").Range("A1"), Sheets("Emails").Range("A" & Rows.Count).End(xlUp))

The code also works if you specify a range:

Set Rng = Sheets("Emails").Range("A1:A300")