Table Or Array That Can Auto Fill In Column Information Based On Two Cells Text Value

[Admin]

HI,

I have made some more comments and write the actual sheet names in the code. Hope this helps you.

Sub kTest()
    
    Dim MyData          As Variant
    Dim MyTable         As Variant
    Dim i               As Long
    Dim r               As Long
    Dim rFound          As Variant
    Dim wksIniDevices   As Worksheet
    Dim wksPartNumList  As Worksheet
    Dim rngIniDevices   As Range
    
    Set wksIniDevices = ThisWorkbook.Worksheets("Initiating devices")
    Set wksPartNumList = ThisWorkbook.Worksheets("Sheet2")
    
    With wksIniDevices
        r = .Range("a" & .Rows.Count).End(xlUp).Row         'find last row in col A
        Set rngIniDevices = .Range("a2:f" & r)              'range where your data
        MyData = rngIniDevices.Value2                       'convert the range into variant array as array works faster than range
    End With
    
    With wksPartNumList
        r = .Range("a" & .Rows.Count).End(xlUp).Row
        MyTable = .Range("a2:c" & r).Value2                 'part num list
        .Range("a2:a" & r).Name = "Type"                    'define a name range to use in formula
        .Range("b2:b" & r).Name = "Manu"                    'define a name range to use in formula

        .Range("h2").FormulaArray = "=match(f2&""|""&g2,type & ""|"" & manu,0)" 'put the formula. see f2 and g2 - adjust these cells if you use a different cells
        
        For i = 1 To UBound(MyData, 1)
            'put the Type nad Manu in F2 and G2 respectively.adjust f2 and g2 if necessary
            .Range("f2") = MyData(i, 2) 'type
            .Range("g2") = MyData(i, 4) 'manu
            'find match row
            rFound = .Range("h2").Value2
            If Not IsError(rFound) Then
                'insert part # in the array
                MyData(i, 5) = MyTable(rFound, 3)
            End If
        Next
    End With
    'write back the array with parts
    rngIniDevices = MyData
    
End Sub

[william516]

OK you changed a few things from the first one but I just want to get one thing straight if you could explain this part of the code.

With wksIniDevices
        r = .Range("a" & .Rows.Count).End(xlUp).Row         'find last row in col A
        Set rngIniDevices = .Range("a7:f" & r)              'range where your data
        MyData = rngIniDevices.Value2                       'convert the range into variant array as array works faster than range
    End With
    
    With wksPartNumList
        r = .Range("a" & .Rows.Count).End(xlUp).Row
        MyTable = .Range("a2:c" & r).Value2                 'part num list
        .Range("a2:a" & r).Name = "Type"                    'define a name range to use in formula
        .Range("b2:b" & r).Name = "Manu"                    'define a name range to use in formula

        .Range("h2").FormulaArray = "=match(f2&""|""&g2,type & ""|"" & manu,0)" 'put the formula. see f2 and g2 - adjust these cells if you use a different cells
        
        For i = 1 To UBound(MyData, 1)
            'put the Type nad Manu in F2 and G2 respectively.adjust f2 and g2 if necessary
            .Range("f2") = MyData(i, 2) 'type
            .Range("g2") = MyData(i, 4) 'manu
            'find match row
            rFound = .Range("h2").Value2
            If Not IsError(rFound) Then
                'insert part # in the array
                MyData(i, 5) = MyTable(rFound, 3)
            End If
        Next
    End With
    'write back the array with parts
    rngIniDevices = MyData

In one of the comments you say to adjust f2 and g2 to the correct cells if needed. I'm confused about what this should actually be. I changed the name of the worksheet to "AUTO FILL PARTS" to fit what I have the sheet name for. I changed that in the set wksPartNumList = thisworkbook.worksheets ("AUTO FILL PARTS")

Now this is where I get confused

.Range("h2").FormulaArray = "=match(f2&""|""&g2,type & ""|"" & manu,0)"

I'm assuming that this range is referring to the part number list if that Is the case then the range would be the range for the column Type and Manuf?
If that is the case then it would change to A2 and b2 correct or is this referring to something else?

Almost there but still not 100% sure.

[william516]

thanks a ton i got the first version of the code to work by changing my range and simply adding the "" that i forgot all about to the name of the worksheet. Wow I feel stupid that's why im far from the expert. It works fine now as far as i can tell thanks once again.

Great job!