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

 

[jasonb75]

There is nothing that stands out as an error in the code

But will not enter the value into the correct cell on the Destination sheet

Could you elaborate on the problem? Right value, wrong row? Right value, wrong column? No value returned? Something else entirely?

It would be preferable to declare i as long instead of integer, although this would not be the cause of the problem you are currently trying to solve.

Also, you shouldn't need the Index function,

wsDest.Range("B" & i).Value =  IndexRng (Application.WorksheetFunction.Match(wsDest.Range("A" & i).Value, MatchRng, 0))

should be adequate.

 

[Darren Smith]

It returns no value in column B?
It should return a Drawing number in column F from the worksheet Parts List to worksheet Job Card Master column B

 

[jasonb75]

You don't appear to have an active error trap, which means that there must be a valid match for the criteria, otherwise you would get runtime error 1004.
That only leaves the possibility that destlastrow is not working as expected.

Go the vba editor and paste this line into the immediate panel (bottom of the screen, press Ctrl g to show it if it is not visible)

?ThisWorkbook.Worksheets("Job Card Master").Range("A" & Rows.Count).End(xlUp).Row

Does it show the expected last row number for the destination sheet?

 

[jasonb75]

You don't appear to have an active error trap,

I went over your code 3 times and still missed the On Error Resume Next
Try deleting that line, if you get runtime error 1004 then it means no valid match found.

Assuming that not every row will have a match, change ("B" & i) and ("A" & i) to a fixed cell which you believe should have a match in the partslist.

 

[Darren Smith]

Can I send this workbook to you via dropbox please? So you can see what's happening.
The dest last row is working I did a F8 test and yes it works.
I pasted that line into immediate panel but nothing happened.

 

[Darren Smith]

I get error 1004 but i copied the Value off Job Card Master on to the Part List sheet so it should be the same

 

[RoryA]

You could try this - it will give you a more visual indication of what is happening:

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
            Dim matchRow
            matchRow = Application.Match(wsDest.Range("A" & i).Value, MatchRng, 0)
            If IsError(matchRow) Then
                wsDest.Range("B" & i).Value = "No match"
            Else
                wsDest.Range("B" & i).Value = IndexRng.Cells(matchRow, 1)
            End If
        
        Next i
        
    
    End If
    
    Jobcard_Demands.Value = "JobCard Demands"

End Sub

 

[jasonb75]

Are there any userforms / textboxes involved? If the copied value has a numeric format then that could cause a mismatch.

I've found date formats to be a frequent problem with vba matching, but that could be down to the methods I've used.

 

[Darren Smith]

There is a user form involved with a listbox.
I tried to use Rory`s code it say`s No Match in column B?
But the parts list has the value pasted direct from job card master sheet.
Column E on both sheets should match then the drawing No in column F of Parts List Sheet should fill into column B on Job Card Master sheet