Updating Value On Worksheet A, Where Condition is Met on Worksheet B

[reberryjr]

I'm trying to interrogate the data on mMD, and compare it to the data on mTP. If a range exists on both sheets, I want to update column J on mTP with "Y". If the range doesn't exist on mTP, then I want to add the info to mTP (this part works).

The code below works, except for updating column J with "Y". I'm getting a Next without For error, but I'm not clear as to why.

Private Sub cmd_TotalPopMid_Click()
Application.ScreenUpdating = False
Dim m As Workbook
Dim mTP, mMD As Worksheet
Dim Rng As Range
Dim RngList As Object
Set m = ThisWorkbook
Set mTP = ThisWorkbook.Sheets("Total_Population")
Set mMD = ThisWorkbook.Sheets("MD_Consolidated")
Set RngList = CreateObject("Scripting.Dictionary")
mMDLR = mMD.Range("A" & Rows.Count).End(xlUp).Row
mTPLR = mTP.Range("A" & Rows.Count).End(xlUp).Row

For Each Rng In mTP.Range("A2", mTP.Range("A" & mTP.Rows.Count).End(xlUp))
    If Not RngList.Exists(Rng.Value & Rng.Offset(0, 2)) Then
        RngList.Add Rng.Value & Rng.Offset(0, 2), Nothing
    End If
Next
[COLOR=#ff0000]For Each Rng In mMD.Range("A2", mMD.Range("A" & mMD.Rows.Count).End(xlUp))
    'Update column J on mTP with Y, where the RngList exists.
    If RngList.Exists(Rng.Value & Rng.Offset(0, 2)) Then
        mTP.Range("J" & RngList).Value = "Y"[/COLOR]
    Else
    If Not RngList.Exists(Rng.Value & Rng.Offset(0, 2)) Then
        mMD.Range("A" & Rng.Row & ":H" & Rng.Row).Copy mTP.Cells(mMD.Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
Next
RngList.RemoveAll
Application.ScreenUpdating = True
End Sub

 

[reberryjr]

That appears to have fixed it, but can you help me understand this verbiage?

    Else
        Set RngList(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) = Union(RngList(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)), rng)

 

[Fluff]

The part in blue returns the range currently held in the dictionary item

Set RngList(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) = Union([COLOR=#0000ff]RngList(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2))[/COLOR], rng)

Then the Union adds that to Rng & stores it as the item

 

[reberryjr]

Thank you!

 

[Fluff]

You're welcome