INDEX MATCH Loop

[Yingy88]

Hi

I'm trying set an index match where I have Named the column, however its giving me an error even before I set this to loop.

I am trying to match the data from the Import File worksheet to the Master worksheet.

Sub INDEXMATCHDATA()

   Dim P_REF As Integer
   Dim PTYPE As Integer
   Dim x As Integer
  
   Sheets("Master").Select
    
    x = Application.WorksheetFunction.Index("Import Data[PTYPE]", _
    Application.WorksheetFunction.Match("sheet2!a2", "Import Data[P_REF]", 0))

End Sub

Thanks in advance

 

[Chihiro]

What is it that you are trying to accomplish here? Pull result from Import Data and populate Master sheet with corresponding data?

I'd not use Application.WorksheetFunction for this type of operation.
As well, Index isn't really needed here.

I'd recommend one of following.
1. Put both Import data and Master sheets' used range into separate array. Then use nested loop to check value in col1 of Master array against Import array and populate Master array from Import.

2. Do as above, but instead of nested loop, use Application.Match.

3. Use formula.


As for why your code isn't working... there are many issues with it.
1. "Import Data[PTYPE]" there is no such table or named range.

2. You need TableName[Column] or Named range within Range() object. If code runs between multiple sheet, you should also qualify it with sheet codename, index or sheet name.
Ex:

Sheet1.Range("Type")
Sheets("Import Data").Range("Table1[Col1]")

2. Same goes for "sheet2!a2", it should not be string but Range object or value within that range. As well, there is no such sheet in your sample workbook... I assume it's Master sheet.

Sheets("Master").Range("A2")

Etc.

 

[Hui]

I think your code should be:

Sub INDEXMATCHDATA()

  Dim P_REF As Integer
  Dim PTYPE As Integer
  Dim x As Variant
   
  Sheets("Master").Select
 
   
  x = Application.WorksheetFunction.Index(Sheet1.Range("TYPE"), Application.WorksheetFunction.Match(Sheet1.Cells(2, 1), Sheet1.Range("P_Ref"), 0))

End Sub

Note there is no Named Range PTYPE defined ?

Note Sheet1 in above refers to

 

[Yingy88]

I think your code should be:

Sub INDEXMATCHDATA()

  Dim P_REF As Integer
  Dim PTYPE As Integer
  Dim x As Variant
  
  Sheets("Master").Select

  
  x = Application.WorksheetFunction.Index(Sheet1.Range("TYPE"), Application.WorksheetFunction.Match(Sheet1.Cells(2, 1), Sheet1.Range("P_Ref"), 0))

End Sub

Note there is no Named Range PTYPE defined ?

Note Sheet1 in above refers to

View attachment 48214

Hi Hui
I've updated the sample file as the Named Range should be PTYPE which I have now corrected along with Sheet 2 change.

The code does not cause an error, however its not pulled any data from the Import Data sheet to the Master sheet.

Help please

Thanks

 

[Hui]

Its working perfectly
It is setting the variable x to a value of A

if you want to use that
maybe it should say

 

[Hui]

I sort of have a feeling that this is actually what you want to do?

Sub INDEXMATCHDATA()

    Dim c As Range
    Dim lastrow As Variant
   
    Sheets("Master").Select
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
   
    On Error Resume Next
    For Each c In Range("B2:B" & lastrow)
        c = Sheet1.Range(Sheet1.Range("A:A").Find(c.Offset(0, -1), LookIn:=xlValues).Address).Offset(0, 1).Value
    Next
End Sub

 

[Chihiro]

Here's one way to do it using Array. See attached.

Sub DemoMatch()
Dim iA, mA, x, i As Long
With Sheet1
    iA = .Range("A2:F" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With
With Sheet2
    mA = .Range("A2:F" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With

For i = 1 To UBound(mA)
    x = Application.Match(mA(i, 1), Application.Index(iA, 0, 1), 0)
    If IsNumeric(x) Then
        mA(i, 1) = Application.Index(iA, i, 0)
    Else
        mA(i, 1) = Array(mA(i, 1), "", "", "", "", "", "")
    End If
Next
With Sheet2
    For i = 1 To UBound(mA)
        .Cells(i + 1, 1).Resize(, 6) = mA(i, 1)
    Next
End With
End Sub

 

[Yingy88]

Th

Here's one way to do it using Array. See attached.

Sub DemoMatch()
Dim iA, mA, x, i As Long
With Sheet1
    iA = .Range("A2:F" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With
With Sheet2
    mA = .Range("A2:F" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With

For i = 1 To UBound(mA)
    x = Application.Match(mA(i, 1), Application.Index(iA, 0, 1), 0)
    If IsNumeric(x) Then
        mA(i, 1) = Application.Index(iA, i, 0)
    Else
        mA(i, 1) = Array(mA(i, 1), "", "", "", "", "", "")
    End If
Next
With Sheet2
    For i = 1 To UBound(mA)
        .Cells(i + 1, 1).Resize(, 6) = mA(i, 1)
    Next
End With
End Sub

This works wonders, thanks so much