Error using Index and Match to return data from another spreadsheet

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello everybody!

I'm finishing this macro, which is the last one I had to do, and I decided to use Index and Match to fill in column A using the value in the cell of the corresponding row in column B as a reference.

This data must be obtained from another sheet, and the values I need are also in column A, with the reference values in column B.

But after a lot of trying, I can't get the function to work. Whats wrong? The error occurs in W2.Range just after the parenthesis of the Index function with the following message: "Incorrect number of arguments or invalid property assignment". Was it just a syntax error? Because it seems to me that I filled in the parameters correctly.

VBA Code:
Sub Material_Code()

Dim W1 As Worksheet
Dim W2 As Worksheet
Dim lastRow As Long
Dim Cell As Range

Set W1 = Sheets("Requests")
Set W2 = Sheets("Catalog")

W1.Activate

lastRow = W.Range("A" & Rows.Count).End(xlUp).Row

For Each Cell In W1.Range("A2:A" & lastRow)
      Cell.Offset(0, -1) = Application.WorksheetFunction.Index(W2.Range("A2:A20", Application.WorksheetFunction.Match(Cell, W2.Range("B2:B20", 0)), 0))
Next Cell

End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
Your ranges dont have closing brackets.

VBA Code:
Application.WorksheetFunction.Index(W2.Range("A2:A20"), Application.WorksheetFunction.Match(Cell, W2.Range("B2:B20"), 0), 0)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
This isnt right either:

VBA Code:
lastRow = W.Range("A" & Rows.Count).End(xlUp).Row

W should be W1 by the look and you really should use it on the Rows too:

VBA Code:
lastRow = W1.Range("A" & W1.Rows.Count).End(xlUp).Row
 

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello everybody!

I'm finishing this macro, which is the last one I had to do, and I decided to use Index and Match to fill in column A using the value in the cell of the corresponding row in column B as a reference.

This data must be obtained from another sheet, and the values I need are also in column A, with the reference values in column B.

But after a lot of trying, I can't get the function to work. Whats wrong? The error occurs in W2.Range just after the parenthesis of the Index function with the following message: "Incorrect number of arguments or invalid property assignment". Was it just a syntax error? Because it seems to me that I filled in the parameters correctly.

VBA Code:
Sub Material_Code()

Dim W1 As Worksheet
Dim W2 As Worksheet
Dim lastRow As Long
Dim Cell As Range
[QUOTE="steve the fish, post: 5653153, member: 129873"]
Your ranges dont have closing brackets.

[CODE=vba]Application.WorksheetFunction.Index(W2.Range("A2:A20"), Application.WorksheetFunction.Match(Cell, W2.Range("B2:B20"), 0), 0)


Set W1 = Sheets("Requests")
Set W2 = Sheets("Catalog")

W1.Activate

lastRow = W.Range("A" & Rows.Count).End(xlUp).Row

For Each Cell In W1.Range("A2:A" & lastRow)
Cell.Offset(0, -1) = Application.WorksheetFunction.Index(W2.Range("A2:A20", Application.WorksheetFunction.Match(Cell, W2.Range("B2:B20", 0)), 0))
Next Cell

End Sub[/CODE]
[/QUOTE]
Your ranges dont have closing brackets.

VBA Code:
Application.WorksheetFunction.Index(W2.Range("A2:A20"), Application.WorksheetFunction.Match(Cell, W2.Range("B2:B20"), 0), 0)
The syntax looks better, but error 1004 appears, saying that I can't use the WorksheetFunction class's Match object.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,486
Messages
5,636,611
Members
416,929
Latest member
Nitil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top