Darren Smith
Well-known Member
- Joined
- Nov 23, 2020
- Messages
- 631
- Office Version
- 2019
- Platform
- Windows
Trying to use index match function this code all looks right? But will not enter the value into the correct cell on the Destination sheet
VBA Code:
Private Sub Jobcard_Demands_Click()
If Jobcard_Demands = ("Drawing No`s Update") Then
Dim PartsList As Worksheet
Dim wsDest As Worksheet
Dim PartsListLastRow As Long, wsDestLastRow As Long
Dim IndexRng As Range, MatchRng As Range
Dim i As Integer
Set PartsList = ThisWorkbook.Worksheets("Parts List")
Set wsDest = ThisWorkbook.Worksheets("Job Card Master")
PartsListLastRow = PartsList.Range("A" & Rows.Count).End(xlUp).Row
wsDestLastRow = wsDest.Range("A" & Rows.Count).End(xlUp).Row
Set IndexRng = PartsList.Range("E1:E" & PartsListLastRow)
Set MatchRng = IndexRng.Offset(0, 1)
For i = 2 To wsDestLastRow
On Error Resume Next
wsDest.Range("B" & i).Value = Application.WorksheetFunction.Index( _
IndexRng, _
Application.WorksheetFunction.Match(wsDest.Range("A" & i).Value, MatchRng, 0))
Next i
End If
Jobcard_Demands.Value = "JobCard Demands"
End Sub