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

 

[saeedkk]

Hello,

if i understand well what you are trying to do , you can solve it by the vlookup,
what version of office you have , as i remember that you may face problems when trying to use vlookup in 2007 between sheets.

Regards

 

[Arithos]

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

On what line?

 

[mnut1]

Thanks chaps,
I'm using 2003 and i had difficulty with vlookup thats why I thought I'd do it like this.
error on line 6 Set c = Sheets("JMES").Find(d.Value, lookat:=xlWhole).Offset(0, 8).Value

 

[Arithos]

Thanks chaps,
I'm using 2003 and i had difficulty with vlookup thats why I thought I'd do it like this.
error on line 6 Set c = Sheets("JMES").Find(d.Value, lookat:=xlWhole).Offset(0, 8).Value

Why do you need to use "Set"? I would try running the code without it. You are just storing a value.

Or, it just might not find that value. And at that point my macros usually stops aswell.

 

[Arithos]

I would think about doing it in this manner.
Then you would avoid alot of possible "error-spots" of the code :D

If Not Sheets("JMES").Find(d.Value, , , , , , MatchCase:=False) is nothing then
    d.Offset(0, 4).Value = Sheets("JMES").Find(d.Value, lookat:=xlWhole).Offset(0, 8).Value
    d.Offset(0, 5).Value = Sheets("JMES").Find(d.Value, lookat:=xlWhole).Offset(0, 9).Value
end if

Or maybe a new line to determine if "b" is nothing.

 

[Arithos]

so.. Something like this

Sub Testit()
Dim d As Range
Dim FilterMode As Boolean
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))
             If Not Sheets("JMES").Find(d.Value, , , , , , MatchCase:=False).Offset(0, 8) Is Nothing Then
                     d.Offset(0, 4).Value = Sheets("JMES").Find(d.Value, , , , , , MatchCase:=False).Offset(0, 8).Value
             End If
             If Not Sheets("JMES").Find(d.Value, lookat:=xlWhole).Offset(0, 9) Is Nothing Then
                    d.Offset(0, 5).Value = Sheets("JMES").Find(d.Value, lookat:=xlWhole).Offset(0, 9).Value
             End If
        Next d
End With
End Sub

 

[mnut1]

Sorry Sturla yours is still throwing up the same error for both your if not statements.

 

[mnut1]

Here is a copy of the workbook if its any help?
https://app.box.com/s/boixjrrok25huzqzrgho

 

[Arithos]

Here is a copy of the workbook if its any help?
https://app.box.com/s/boixjrrok25huzqzrgho

It would be very good help, but I'm unable to download it =(
If possible upload to another source or send me an e-mail with it attached, I'll pm it to you.