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)?

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)?

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)?

Instead of checking the .value and getting the run time error, you could look at
the .text

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

You could also check for an error with something like:

if iserror(.cells(6,4).value) then
'skip it
else
'process it

If you wanted to check to see if there was an @ sign in each of those 7 cells,
you could use instr():


With Worksheets("Members")
receive = ""
If InStr(1, .Cells(6, 4).Text, "@", vbTextCompare) 0 Then
'found one
receive = .Cells(6, 4).Text
End If
For i = 7 To endrow
If InStr(1, .Cells(i, 4).Text, "@", vbTextCompare) 0 Then
'found one here, too
receive = receive & "," & .Cells(i, 4).Text
End If
Next i
End With

(Untested, uncompiled. Watch for typos.)

ssylee wrote:

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)?

--

Dave Peterson

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)?