parsing a column of data into a single entry separated by commas

[ssylee]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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?

 

[Dave Peterson]

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

 

[Rick Rothstein]

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)


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?