I'm fairly new to using the index and match functions and this is my first time trying them in a macro. I have a workbook with multiple sheets and in the "Master" sheet I get an error when I run the following code:
![]()
Private Sub CommandButton1_Click() Application.ScreenUpdating = False Dim x As String, y As String, i As Integer lrow_Master = Sheets("Master").Range("A65536").End(xlUp).Row For i = 2 To lrow_Master If Cells(i, 12) = "Completed" Then If Cells(i, 13) = "" Then x = Cells(i, 11).Value y = WorksheetFunction.Match(Sheets("Master").Cells(i, 4), Sheets(x).Range("G1:G300"), 0) Sheets(x).Cells(y, 5).Value = WorksheetFunction.Index(Sheets("Master").Cells(i, 3), WorksheetFunction.Match(Sheets("Master").Cells(i, 4), Sheets(x).Range("G2:G300"), 0)) Sheets(x).Cells(y, 20).Value = "Complete" Sheets("Master").Cells(i, 13).Value = "Updated" End If End If Next i Application.ScreenUpdating = True End Sub
The error statement says: "unable to get index property of worksheetfunction class."
I'm not sure why because the code runs the first time through the loop and does what it should, but on the second iteration I get the error.











LinkBack URL
About LinkBacks
Register To Reply
Bookmarks