Excel VBA match based on concatenating two inputs and comparing to two concatenated ranges

[rrands1]

I have two worksheets containing some address information:
Sheet1 (ssheet)
Col A = Street Number
Col B = Street Name
Col C = Status
Sheet2 (tsheet)
Col A = Street Number
Col B = Street Name
Col C = initially empty, but should be filled in with data from Sheet1.ColC if the combination of Sheet1.ColA & Sheet1.ColB (concatenating them) are a match to Sheet2.ColA & Sheet2.ColB

I am hoping to do this via Match (in VBA), and not looping through all of the rows multiple times, but I have his a wall. Here is what I have so far:

For x = 2 To Targetlastrow ' last row in sheet
tsheet.Range("C" & x).Value = Application.Index(ssheet.Range("C2:C" & x), Application.Match(tsheet.Range("A" & x).Value & tsheet.Range("B" & x).value, ssheet.Range("A2:A" & x) & ssheet.Range("B2:B" & x), 0))
''                                                                                    values to return                                                                   what to match                                                  compare against

Next

I *think* it all works, except where I am trying to compare the concatenated Ax & Bx (for argument's sake, let's say that equates to "123Main Street" with the concatenated Ranges A2:Ax & B2:Bx which, if that combination exists, should also equate to "123Main Street", but it's not doing it that way. (I get a "Type Mismatch" error.

Any thoughts on what I might be missing?

Thank you so much for any help!

 

[tonyyy]

Looping through Arrays can be pretty quick, often times faster than a single loop using Match. You might consider the following...

Sub Koncatenate()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow1 As Long, LastRow2 As Long
Dim arr1 As Variant, arr2 As Variant
Dim i As Long, j As Long

Set ws1 = Sheets(1) ' change to your ssheet
Set ws2 = Sheets(2) ' change to your tsheet
LastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
arr1 = ws1.Range("A1:C" & LastRow1)
arr2 = ws2.Range("A1:C" & LastRow2)

For i = 2 To LastRow1
    For j = 2 To LastRow2
        If arr1(i, 1) & arr1(i, 2) = arr2(j, 1) & arr2(j, 2) Then arr2(j, 3) = arr1(i, 3)
    Next j
Next i

ws2.Range("A1:C" & LastRow2) = arr2
End Sub

Cheers,

Tony