Try to use Index and Match Function

[Darren Smith]

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

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

 

[Darren Smith]

I have changed it back to 1 it puts Value Single in random cells in column B of Job Card Master sheet.

 

[RoryA]

It works for me in your sample file on Dropbox. I suggest you update that and post another link.

 

[Darren Smith]

It works for me in your sample file on Dropbox. I suggest you update that and post another link.

Here you go

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[RoryA]

You changed the match type. It should be:

MatchRow = Application.Match(wsDest.Range("E" & i).Value, MatchRng, 0)

not:

MatchRow = Application.Match(wsDest.Range("E" & i).Value, MatchRng, 1)

 

[Darren Smith]

Phew at last ??
Thank you very much for your help. Brilliant job

 

[RoryA]

Glad we got there in the end!