Thread: Macro returning string, while i want array

Hi!

My problem started when i was trying to get a VLookup to return mulitple values. For example, I wanted it to return every value that had the word "YES" beside it. But, as we all know, once a VLookup finds the lookup value, it stops searching.

I then discovered a macro that will return multiple values based on a given criteria. It is:

Code:

Public Function FindSeries(TRange As Range, MatchWith As String)
     
    For Each cell In TRange
        If cell.Value = MatchWith Then
            x = x & cell.Offset(0, 1).Value & ", "
        End If
    Next cell
     
    FindSeries = Left(x, (Len(x) - 2))
     
End Function

However, it is returning all the value as a string in the same cell separated by commas! I want it to return each value into a new cell (preferrably going vertically). I'm sure there must be an easy manipulation to this macro to make it return each value in its own cell, rather than as a string. Please help!!!


Thank you so much

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.please

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Cross posted here

This version will return an array. If you are calling from a sheet, you need to select the range of cells where you want the value returned and enter =FindSeries(..) as an array formula, using Ctrl-Shift-Enter (Cmd+Return for Mac) to enter the formula.

Code:

Public Function FindSeries(TRange As Range, MatchWith As String)
    Dim retArray as Variant
    For Each cell In TRange
        If cell.Value = MatchWith Then
            x = x & cell.Offset(0, 1).Value & ", "
        End If
    Next cell
     
    retArray = Split(Left(x, (Len(x) - 2)),", ")
    
    FindSeries = retArray
End Function