Error using Index and Match to return data from another spreadsheet

[F_Ribeiro]

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.

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

 

[steve the fish]

Your ranges dont have closing brackets.

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

 

[steve the fish]

This isnt right either:

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:

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

 

[F_Ribeiro]

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.

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.

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.

 

[steve the fish]

You didnt act on all the advice.