Try to use Index and Match Function

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,427
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Most likely issue as Jason said is that one is stored as a number and the other as text, assuming they are numeric codes.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
Try adding CLng to the code
VBA 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

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Try adding CLng to the code
VBA 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

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

ADVERTISEMENT

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

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows
Here you go
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,427
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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:

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

Darren Smith

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,427
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I did look at the workbook - that's where the line of code above came from.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

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