Loop through cells and create string

**pablowilks** · 04-24-2012, 08:38 PM

Hi there

I would love some help

lets say you have a two column table with kids names in column A and their ages in column B. Some kids are older than ten years old, some are younger.

I want to write a macro that loops through and scoops up all the names of kids that are aged 10 or under. I then want to put all these names into a string like the following:

"Kids aged ten or under are: bob, robert, dan, mike, martha"

This string should then be placed in a cell in column A, 2 rows below the last row of the table.

If you could give me the code it would be much appreciated.

Many thanks

**JBeaucaire** · 04-25-2012, 12:42 AM

How about the UDF approach? Instead of a macro, put this UDF code into a standard code module to give your workbook a new function you can use in a cell like any formula:

Option Explicit

Function AGELIST(MyRNG As Range, Age As Long, ListDir As String) As String
'To get ages above a threshold, used in a cell like:   =AGELIST(A1:B10, 10, "OVER")
'To get ages below a threshold, used in a cell like:   =AGELIST(A1:B10, 10, "UNDER")
Dim cell As Range, Buf As String

If MyRNG.Columns.Count <> 2 Then
    AGELIST = "1: Use a two-column range"
    Exit Function
ElseIf Not Age > 0 Then
    AGELIST = "2: Use a whole number for the age"
    Exit Function
ElseIf InStr(1, "OVERUNDER", ListDir) = 0 Then
    AGELIST = "3: OVER and UNDER only"
    Exit Function
End If

    Select Case ListDir
        Case "OVER"
            For Each cell In MyRNG
                If IsNumeric(cell) And cell >= Age Then Buf = Buf & ", " & cell.Offset(, -1)
            Next cell
        Case "UNDER"
            For Each cell In MyRNG
                If IsNumeric(cell) And cell <= Age Then Buf = Buf & ", " & cell.Offset(, -1)
            Next cell
    End Select
    If Len(Buf) = 0 Then
        AGELIST = "none found"
    Else
        AGELIST = "Kids aged " & Age & " and " & ListDir & " are:" & Mid(Buf, 3, Len(Buf))
    End If
End Function

Used in a cell like:

=AGELIST(A1:B10, 10, "OVER")

The first parameter is your two-column source range
The second parameter is the age threshold
The third parameter is OVER or UNDER, sets the direction the threshold works

**jindon** · 04-25-2012, 02:10 AM

UDF

=MyReport(A2:B9,"<10")
or
=MyReport(A2:B9,"=>10")

If you want to substitute "10" by cell reference
=MyReport(A2:B9,"<"&C1)
where C1 houses 10

Function MyReport(rng As Range, criteria As String) As String
    Dim r As Range
    For Each r In rng.Columns(2).Cells
        If Evaluate(r.Value & criteria) Then
            MyReport = MyReport & ", " & r(, 0).Value
        End If
    Next
    If Len(MyReport) Then
        MyReport = Mid$(MyReport, 3)
    End If
End Function