run-time error 13 after Application.Match

[davidmyers]

Hi,
I'm trying to create a comma separated list in colD from terms in colA where colB and colC match.
I have the following code:

Sub test2()    Dim r1 As Range
    Dim r2 As Range
    Dim cell As Range
    Dim lastrow As Long
    Dim csvTxt As String
    'change Sheet1 to suit
    With ThisWorkbook.Worksheets("Sheet5")
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set r1 = .Range("A2:A" & lastrow)
    End With


    'change Sheet2 to suit
    With ThisWorkbook.Worksheets("Sheet5")
        lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
        Set r2 = .Range("C2:C" & lastrow)
    End With


    For Each cell In r1
        If Not (IsError(Application.Match(cell.Offset(0, 1).Value, r2, 0))) Then
[COLOR=#ff0000]            If r2.Offset(0, 1).Value > " " Then[/COLOR]
                r2.Offset(0, 1) = r2.Offset(0, 1).Value & ", "
            End If
            r2.Offset(0, 1) = r2.Offset(0, 1).Value & cell.Value
        End If
    Next cell
End Sub

At red line I get Run-Time error "13"

What am I doing wrong?

Thanks
David

 

[jimrward]

Try <> to " "

 

[steve the fish]

r2 doesnt have a value. It has multiple values.

 

[davidmyers]

So how do I locate the matching cell in r2 so that I can add to the list in r2.offset(0, 1)?

 

[steve the fish]

Cant you just offset from r1?

 

[davidmyers]

r1 is a list of 400 key words, with group name in the next column.
r2 is just a list of 30 group names and I want a list of keywords for the group in the cell next to the group name.

 

[steve the fish]

Ah i see now. You are going to have to use your match. Because r2 is several cells you can refer to them like this:

r2(1)

for the first cell in the range etc etc. You can use your match to tell you which cell is the correct cell.

 

[davidmyers]

Thanks for this Steve, my coding skills are a bit limited, can you give me a code snippet

Thanks
David

 

[steve the fish]

Adapting yours so you can see the changes:

'change Sheet1 to suit
With ThisWorkbook.Worksheets("Sheet5")
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set r1 = .Range("A2:A" & lastrow)
End With

'change Sheet2 to suit
With ThisWorkbook.Worksheets("Sheet5")
    lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set r2 = .Range("C2:C" & lastrow)
    r2.Offset(0, 1) = ""
End With

For Each cell In r1
    If Not (IsError(Application.Match(cell.Offset(0, 1).Value, r2, 0))) Then
        myMatch = Application.Match(cell.Offset(0, 1).Value, r2, 0)
        If r2(myMatch).Offset(0, 1).Value = "" Then
            r2(myMatch).Offset(0, 1) = cell.Value
        Else
            r2(myMatch).Offset(0, 1) = r2(myMatch).Offset(0, 1).Value & ", " & cell.Value
        End If
    End If
Next cell

 

[davidmyers]

Thanks Steve, Works like a charm, and I've learnt something