Looping Through Records.
Patrick you are a life saver! Thank you so much.
Diana
-----Original Message-----
I suggest a function thatreturns the text to the sheet.
Function JoinString(MyRange As Range)
Dim iLoopCounter As Long
Dim TextList As String
Dim Cell As Range
' start loop
For Each Cell In MyRange.Cells
'check test condition
If Cell.Offset(0, 1).Value = "Y" Then
TextList = TextList & "," & Cell.Value
End If
Next Cell
'remove preceding comma
TextList = Mid(TextList, 2)
'return the result
JoinString = TextList
End Function
If your data is in the range A1:B5
then in any other cell type
=JoinString(A1:5)
and you'll see
B,C,E
Method: Use a FOR Each loop. As we have passes a range,
we can check each cell in the range. If the cell to its
right is Y then we add the cell value to the string.
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Thank you in advance for your time and help.
Here is the senario:
I have a table with Vendor Names and Y or N next to
each
name.
Vendor Name Qualified
A N
B Y
C Y
D N
E Y
I would like to concatenate all the Vendor Names that
are
qualified in one cell. So for this example the result
would be: B, C, E
I realize that I need to write a formula to loop
through
the data but I am not exactly sure how this is done. I
think I have a phobia of loops and arrays:)
.
.
|