-1

I'm trying to figure out how to find multiple match data from another worksheet. My goal is to find not just one match. I want to find all the matches up to the last row of data.

Here's a sample of my code. It only finds one match, then it goes to the next data.

For RowData = 2 to LastRow
   MatchData = Application.WorksheetFunction.Match("Sandwich",Worksheets("Food").Range("A1:A" & LastRow), 0))
   If RowData <> MatchData then
      Msgbox("Data matched!")
   End if
Next

Hope you could help me out. Thanks in advance.

Krish
  • 5,917
  • 2
  • 14
  • 35
bigbryan
  • 411
  • 6
  • 19
  • 36
  • 2
    Like many tasks in Excel and VBA, there are many ways to accomplish what you're describing. A little background would be helpful, to give an idea of what you have, what you're trying to do and why. For example, this may not fit your need, but with what you shared, my first thought is that you don't need VBA at all -- you could simply use [AutoFilter](https://support.office.com/article/quick-start-filter-data-by-using-an-autofilter-08647e19-11d1-42f6-b376-27b932e186e0) to display only the rows matching your criteria. – ashleedawg May 11 '18 at 07:20

2 Answers2

3

Possibly use .findnext to make sure you search up to lastrow. Then store (as a possibility) hits in an array. Code below:

Sub Test()

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

With Worksheets("Food").Range("A1:A" & Worksheets("Food").Range("A" & Rows.Count).End(xlUp).Row)
     Set c = .Find("Sandwich", LookIn:=xlValues)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            ReDim Preserve myArray(y)
            myArray(y) = c.Row
            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

For x = LBound(myArray) To UBound(myArray)
    msg = msg & myArray(x) & ", "
Next x

MsgBox "Matches in row(s): " & msg & " Good luck with it!"

End Sub

You could also get rid of the array and just update the variable msg each time there is a hit below Do... I just like the idea of an array :) The choice is yours!

JvdV
  • 70,606
  • 8
  • 39
  • 70
0

Try,

dim MatchData as variant
For RowData = 2 to LastRow
   MatchData = Application.Match("Sandwich", Worksheets("Food").Range("A" & RowData & ":A" & LastRow), 0))
   if not iserror(matchdata) then
       If RowData <> MatchData then
          Msgbox("Data matched!")
       End if
   end if
Next