Try to use Index and Match Function

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. 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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,444
Office Version
  1. 365
Platform
  1. Windows
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,
VBA Code:
wsDest.Range("B" & i).Value =  IndexRng (Application.WorksheetFunction.Match(wsDest.Range("A" & i).Value, MatchRng, 0))
should be adequate.
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,444
Office Version
  1. 365
Platform
  1. Windows
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)
VBA Code:
?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

Well-known Member
Joined
Dec 30, 2008
Messages
12,444
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could try this - it will give you a more visual indication of what is happening:

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
            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

Well-known Member
Joined
Dec 30, 2008
Messages
12,444
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 23, 2020
Messages
370
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top