Populating one ws from another

[mnut1]

Hello!

I have two sheets.
Sheet 1(NOM ROLL) has with id numbers in col A and spaces in col E and F.
Sheet 2(JMES) has a subset of the id numbers in col B and in Cols J & k, strings.

I need to find the matching ids in (s1)A and (s2)B and where they match, copy in (s2)J&K into (s1) E&F.

Below is what i've come up with however its returning a error 9, subscript out of range.

Any help would be much appriciated!

Dim d As Range, c As Range, b As Range
With Sheets("NOM ROLL")
If FilterMode = True Then .ShowAllData
   Range("A3", Range("A" & Rows.Count).End(xlUp)).Interior.ColorIndex = xlNone
   For Each d In Range("A3", Range("A" & Rows.Count).End(xlUp))
      Set c = Sheets("JMES").Find(d.Value, lookat:=xlWhole).Offset(0, 8).Value
      Set b = Sheets("JMES").Find(d.Value, lookat:=xlWhole).Offset(0, 9).Value
                If Not c Is Nothing Then
                    d.Offset(0, 4).Value = c
                    d.Offset(0, 5).Value = b
                    Set c = Nothing
                    Set b = Nothing
                    GoTo MyLoop
                End If
                
MyLoop:                 Next d
            End With
End Sub

 

[Arithos]

Ok, I replicated the data on my end, and this works for me.

For Each d In Sheets("NOM ROLL").Range("A3", Range("A" & Rows.Count).End(xlUp))
       If Not Sheets("JMES").Cells.Find(d) Is Nothing Then
            c = Sheets("JMES").Cells.Find(d).Row
            If Not c = "" Then
                 d.Offset(0, 4).Value = Sheets("JMES").Cells(c, 2).Offset(0, 8).Value
                 d.Offset(0, 5).Value = Sheets("JMES").Cells(c, 2).Offset(0, 9).Value
            End If
       End If
Next d

 

[Arithos]

As a note, you can not dim c as Range , so Dim c as Integer
If you want it to work, the b variable is not needed.

The complete result would then be like this:

Sub mnut1()Dim FilterMode As Boolean
Dim d As Range, c As Integer
With Sheets("NOM ROLL")
    If FilterMode = True Then .ShowAllData
       Range("A3", Range("A" & Rows.Count).End(xlUp)).Interior.ColorIndex = xlNone
       Sheets("NOM ROLL").select ' I add this to make sure the code runs smoothly if ran from other sheets. 
    For Each d In Sheets("NOM ROLL").Range("A3", Range("A" & Rows.Count).End(xlUp))
    
           If Not Sheets("JMES").Cells.Find(d) Is Nothing Then
                c = Sheets("JMES").Cells.Find(d).Row
                     d.Offset(0, 4).Value = Sheets("JMES").Cells(c, 2).Offset(0, 8).Value
                     d.Offset(0, 5).Value = Sheets("JMES").Cells(c, 2).Offset(0, 9).Value
           End If
    Next d
End With


End Sub

 

[mnut1]

Works Perfectly!!!!
Thank Sturla! you are awesome!!

 

[Arithos]

Works Perfectly!!!!
Thank Sturla! you are awesome!!

Wonderful got it in the end!