VBA: For Each Match Return Adjacent Cell

[BenGee]

Hi

Here's what I have so far;

    For Each c In Worksheets("Rota").Range("A3:A31")
        If Not IsError(Application.Match(c, Worksheets("Key").Range("A16:A41"), 0)) Then
            [COLOR="#FF0000"]Worksheets("Rota").c.Offset(0, 1).Value = Worksheets("Key").Match.c.Offset(0, 1).Value[/COLOR]
        End If
    Next

The red line seems to cause problems. The result should be for each match, the adjacent cell value in "Rota" to be the same as the adjacent cell value in "Key".

Any ideas how I can get this to work would be hugely appreciated. Thank you

Edit: By problems, I mean a 'Run-time error '438' - Object doesn't support this property or method'. Sorry should've been more specific.

 

[Fluff]

How about

Dim x As Variant
For Each c In Worksheets("Rota").Range("A3:A31")
   x = Application.Index(Worksheets("Key").Range("B16:B41"), Application.Match(c, Worksheets("Key").Range("A16:A41"), 0))
    If Not IsError(x) Then
        Worksheets("Rota").c.Offset(0, 1).Value = x
    End If
Next

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG23Apr15
 [COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] Range, Rngk [COLOR="Navy"]As[/COLOR] Range
 [COLOR="Navy"]Set[/COLOR] Rngk = Worksheets("Key").Range("A16:A41")
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] c [COLOR="Navy"]In[/COLOR] Worksheets("Rota").Range("A3:A31")
        [COLOR="Navy"]If[/COLOR] Not IsError(Application.Match(c, Rngk, 0)) [COLOR="Navy"]Then[/COLOR]
              c.Offset(0, 1).Value = Rngk.Parent.Range("B" & Application.Match(c, Rngk, 0) + 15)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick