Looping Through Records.
Hi Diana,
Place the code below in the standard module and please try this.
Assume the field Vendor Name is located A column.
Sub ConcatenateVendorNames()
Dim a, ret As String, i As Long
Dim c As Range
On Error Resume Next
Set c = Application.InputBox("Please select a cell for return value",
Type:=8)
a = Columns(1).SpecialCells(2).Resize(, 2).Value
For i = LBound(a) To UBound(a)
If UCase(Trim(a(i, 2))) = "Y" Then ret = ret & a(i, 1) & ","
Next
ret = Left(ret, Len(ret) - 1)
c.Value = ret: Set c = Nothing
Exit Sub
End Sub
"Diana" wrote in 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:)
|