Presenting multiple match data

Question

Screenshot I use this script (found here) and did a little of modification, but now is not presenting the information found, I got the phrase "By Wedge" in the cells C14, C15 and C22, but only prints the info corresponding to C14:

Function WedgeUsadaEn()

Dim myArray() As Variant
Dim x As Long, y As Long, lNumElements As Long
Dim msg As String

With Worksheets("OBQ").Range("C13:C33")
     Set c = .Find("By Wedge", LookIn:=xlValues)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            ReDim Preserve myArray(y)
            myArray(y) = c.Offset(0, -2).Value
            y = y + 1
            Set c = .FindNext(c)
        If c Is Nothing Then
            GoTo DoneFinding
        End If
        Loop While c.Address <> firstAddress
      End If
DoneFinding:
End With

lNumElements = UBound(myArray) - LBound(myArray)

If lNumElements = 1 Then
msg = myArray(0)
Else
    For x = LBound(myArray) To UBound(myArray)
        If x < (lNumElements - 1) Then
            msg = msg & myArray(x) & ", "
        Else
            msg = msg & myArray(x)
        End If
    Next x
End If

WedgeUsadaEn = msg

End Function

I hope anyone can bring me some light about what is this happening

If I keep it as sub and not a function, like the original, all the data is presente properly

Use `lNumElements = y`. With 3 elements UBound(myArray) is 2, LBound(myArray) is 0 so UBound(myArray) - LBound(myArray) is 2 not 3. — CDP1802, Feb 26 '23 at 20:20
Note the `If c Is Nothing Then` block does nothing because after the first match c will never be nothing. You can just use `WedgeUsadaEn = join(myArray,", ")` — CDP1802, Feb 26 '23 at 20:27
Much simpler to add the found cells to a Collection than to use an array. Or skip both and just build up the string in the `Find` loop. — Tim Williams, Feb 26 '23 at 20:46

score 1 · Answer 1 · answered Feb 26 '23 at 21:00

1

Simpler:

Function WedgeUsadaEn()

    Dim c As Range, msg As String, sep as string, firstAddress As String
    
    With Worksheets("OBQ").Range("C13:C33")
         Set c = .Find("By Wedge", LookIn:=xlValues)
         If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                msg = msg & sep & c.Offset(0, -2).Value
                sep = ", "
                Set c = .FindNext(c)
            Loop While c.Address <> firstAddress
          End If
    End With
DoneFinding:
    
    WedgeUsadaEn = msg

End Function

answered Feb 26 '23 at 21:00

Tim Williams

154,628
8
97
125

thanks, this is much simpler, but i still get the #value error when use the function in a cell, let's say N14 – David Suarez Feb 27 '23 at 06:30
Call the function from a sub and see what you get. Or just type `? WedgeUsadaEn()` in the Immediate pane and hit Enter. – Tim Williams Feb 27 '23 at 06:50
If I call it from a sub or type "? WedgeUsadaEn()" works fine in my example and yours, I get the result I want, but not in excel cell – David Suarez Feb 27 '23 at 07:27
My bad - forgot that limitation when used in a UDF: https://stackoverflow.com/questions/28715135/udf-using-findnext-seems-to-abort-without-warning or https://stackoverflow.com/questions/57666720/excel-vba-function-stops-after-using-findnext-works-in-subroutine – Tim Williams Feb 27 '23 at 07:33
Oh I see, thank you so much, that bring peace to my mind – David Suarez Feb 27 '23 at 14:52

Presenting multiple match data

1 Answers1