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

 

[RoryA]

Most likely issue as Jason said is that one is stored as a number and the other as text, assuming they are numeric codes.

 

[jasonb75]

Try adding CLng to the code

.Match(CLng(wsDest.Range("A" & i).Value), MatchRng, 0)

No guarantees it will make any difference without more info on the source data.

 

[Darren Smith]

Most likely issue as Jason said is that one is stored as a number and the other as text, assuming they are numeric codes.

How can you overcome the above?

 

[Darren Smith]

Try adding CLng to the code

.Match(CLng(wsDest.Range("A" & i).Value), MatchRng, 0)

No guarantees it will make any difference without more info on the source data.

Could I send the workbook to you might save some time ?

 

[jasonb75]

The forum rules don't permit private sharing of files, but you could upload it to a file share service, onedrive / dropbox / etc and post a link to it here.

 

[Darren Smith]

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're looking up the values in column A which is mostly blank. Well, actually in the example file, you didn't even include a column letter - it says:

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

 

[Darren Smith]

Sorry that should of been Column C in above code.
It's still not working with the C Column in the code
Might help you if you had a look the workbook I sent.

 

[Darren Smith]

Very sorry the above should of been E not C Column

 

[RoryA]

I did look at the workbook - that's where the line of code above came from.