Match Funtion VBA

[Nick72310]

The purpose of my code is to verify if the person in column B has everything in the component list (column D). I have a report in the workbook that contains all the necessary data, that is where the named ranges (PERSON, COMPONENT_ITEM) are coming from.

I cannot seem to get the match function to work properly in my code. It will not display anything, just outputs Run-time error '13': Type mismatch.
Right now, I am just trying to display the correct row of the component, with just one person on the list. I plan to create a checklist in column A saying, yes they have all the components, or no they do not.

Capture.JPG

Sub Check()

For i = 0 To WorksheetFunction.CountA(Range(Cells(4, 2), Cells(4, 2).End(xlDown))) - 1

    For j = 0 To WorksheetFunction.CountA(Range(Cells(4, 4), Cells(4, 4).End(xlDown))) - 1
        Cells(4 + j, 3) = WorksheetFunction.Match(1, ([Person] = Cells(4 + 1, 2)) * ([COMPONENT_ITEM] = Cells(4 + i, 4)), 0).Value
    Next j
Next i

End Sub

[Paul_Hossler]

A small workbook with the 2 named ranges populated would help because the .Match formula is too much for me to hold in my head all at once

I'm guessing that Cells(4 + 1, 2) is a typo and should be Cells(4 + i, 2)

[Nick72310]

VBA Help - Thank You!.xlsm

Thank you for the response. I have attached an example of the what I am trying to do, using the exact code mentioned above.

FYI, the actual table I plan to use contains roughly 270,000 lines.

[Paul_Hossler]

Not worrying about the VBA for now, but It's not clear in your workbook what the 'After' is supposed to be

If all you want is the Row Number of the COMPONENT, this is a formula version. There's VBA that will do the same thing

Capture.JPG


Also, can you explain in words, what the

  Cells(4 + j, 3) = WorksheetFunction.Match(1, ([Person] = Cells(4 + 1, 2)) * ([COMPONENT_ITEM] = Cells(4 + i, 4)), 0)

is intended to do? I've never used MATCH that way, and am having a hard time following it

[Nick72310]

VBA Help 2 - Thank You!.xlsm
My mistake, I did not include an accurate representation of my data table in my original file. Here it is again.

Capture2.JPG

My data table consists of 60,000 different names and components. I want to display the row number where both the name and the component match. So the code below is saying, give me the row where [Person] is equal to the name in Cells(4 + i, 2) and where [COMPONENT_ITEM] is equal to Cells(4 + i, 4).
So to explain the formula in more detail, ([Person] = Cells(4 + i, 2)) will =1 if there is a match. Same goes for ([COMPONENT_ITEM] = Cells(4 + i, 4)). Because they are multiplied together, if they both exist, it will return the row.

Cells(4 + j, 3) = WorksheetFunction.Match(1, ([Person] = Cells(4 + i, 2)) * ([COMPONENT_ITEM] = Cells(4 + i, 4)), 0).Value

[Paul_Hossler]

I can see Nick needs Hat, Shirt, Jacket, and Mike needs Pants, Socks, Gloves

1. How do you show that in your Checklist? Mike is on Nick's Shirt row

Capture.JPG

2. Your WS Match is an array-entered formula, and your VBA is not. I expect that is the problem

3. I'd have thought that you'd want to start with something like Sheet3 in my attachment which takes the requirements (Sheet2) to build the checklist

[Nick72310]

In your image, what I am trying to do is list the people I want to have checked for items. And the components contains the items I want checked for each person. I only want those components checked, I do not want to list all the items of each person.

Column C (Rows) is not something that is permanent. That is just one piece of the puzzle (I want the row number of coding later on). If I left the row there, it would be overwritten each time by the next person on the list (checking if they have the item).

Looking forward, if "Nick" has all the components listed, it will display a "Yes" in cell A4. If he does not have all the listed components, he will get a "No".

[Nick72310]

I have made more progress, but still have the same error with the match function. This file should clear up questions if you are still unsure what I am trying to accomplish.

VBA Help 3 - Thank You!.xlsm

[Paul_Hossler]

Q1: Sheet2 says that Nick NEEDS a Hat, Shirt, and Jacket?

Q2: Sheet1 seems to say that Nick LACKS a Shirt?

Q3: Where / how do you indicate that Nick has Hat and Jacket?

[Nick72310]

Remember, I am only concerned with the components listed in column C on Sheet1.
With that in mind, lets say the list contains: [Shirt, Hat, Socks, Jacket]

Q1-2:
Looking at Sheet2, Nick has [Hat, Shirt, Jacket]. So out of the components listed in Column C, he does not need anything. Therefore, his name would not appear in column D, and he would have a checkmark in column A.

Q3:
I am only concerned with the components listed in column C on Sheet1. I am not interested in what else he has.

Additionally:
Lets look at Mike, using the same component list as above [Shirt, Hat, Socks, Jacket]. Mike has [Pants, Socks, Gloves]. So Mike NEEDS [Shirt, Hat, Jacket]. This will display in column D as his needs. And he will have a red X in column A because his list has not been fulfilled, when compared to the components in column C on Sheet1.

[Paul_Hossler]

Personally, I'd attack it another way, but that's obviously personal style

Option Explicit
Sub AnotherWay()
    Dim aHave() As String
    Dim rHave As Range, rPersons As Range, rComponents As Range, rPerson As Range, rComponent As Range
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    Set rHave = Worksheets("Sheet2").Cells(1, 1).CurrentRegion
    
    Set rPersons = Worksheets("Sheet1").Cells(3, 2)
    Set rPersons = Range(rPersons, rPersons.End(xlDown))
    
    Set rComponents = Worksheets("Sheet1").Cells(3, 3)
    Set rComponents = Range(rComponents, rComponents.End(xlDown))
    
    ReDim aHave(1 To rHave.Rows.Count)
    For i = LBound(aHave) To UBound(aHave)
        aHave(i) = rHave.Cells(i, 1).Value & "-" & rHave.Cells(i, 2).Value
    Next i
    
    For Each rPerson In rPersons.Cells
        For Each rComponent In rComponents.Cells
            i = 0
            On Error Resume Next
            i = Application.WorksheetFunction.Match(rPerson.Value & "-" & rComponent.Value, aHave, 0)
            On Error GoTo 0
            
            If i = 0 Then
                rPerson.End(xlToRight).Offset(0, 1).Value = rComponent.Value
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub

[Nick72310]

I appreciate the help Paul_Hossler, but I figured out how to get that match formula working. I needed to use ".FormulaArray".

Cells(4 + j, 3).FormulaArray = "=WorksheetFunction.Match(1, (Person = Cells(4 + i, 2)) * (COMPONENT_ITEM = Cells(4 + i, 4)), 0)