Inside the loop, check each cell using the InStr function. Something like
this...


If InStr(TheValueFromCell, "@") 0 Then 'It's likely an email address

--
Rick (MVP - Excel)


"ssylee" wrote in message
...
Thanks for your help. This is the code that I have adapted so far:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/19/2008 by Stanley Lee
'

'
Dim endrow As Integer
Dim i As Long
Dim receive As String

Range("A6").Select
Selection.End(xlDown).Select

' Obtain the current cell number

endrow = ActiveCell.Row

' Add the comma from the first name to the second last name
'For i = 6 To endrow - 1

With Worksheets("Members")
receive = .Cells(6, 4).Value
For i = 7 To endrow
receive = receive & "," & .Cells(i, 4).Value
Next i
End With

MsgBox receive

End Sub

However at row 36, I have a value that doesn't resemble an email
address. It looks something like #VALUE!

I'm planning to check the validity of the entry by finding if there's
a "@" character in the list, or if there's a robust way of checking
the value. How would I go about to check the type of value entered in
a cell with standard functions in VBA?

On Aug 19, 10:14 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Something like this maybe....

Dim X As Long
Dim LastRow As Long
Dim Answer As String
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
Answer = Answer & ", " & .Cells(X, "A").Value
Next
End With
MsgBox Answer

Rick

"ssylee" wrote in message

...

What would be the easiest way perhaps in a for loop in a macro that
would parse from the first row in a column to the last row in the same
column into Cells(row1), Cells(row2), ... , Cells(row n)?