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

 

[Fluff]

You're missing an End If before the 2nd Next

 

[reberryjr]

Ugh! Thanks for catching that! Now I'm getting a Wrong Number Of Arguments Or Invalid Property Assignment error at the line in red font.

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
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
[COLOR=#ff0000]        mTP.Range("J" & RngList).Value = "Y"[/COLOR]
    End If
    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

 

[Fluff]

What are you trying to do?
At the moment you are using a dictionary object where VBA is expecting a row number

 

[reberryjr]

I have 4 key tabs at this point. TP, AM, MD and EoD. Various reports are run in the morning. Those get consolidated onto the AM tab, and to the TP tab. Then, the reports are run again mid-day. If the same records are on the MD tab, that are on the TP tab, then I just want to update column J on the TP tab with Y. If there are new records on the MD tab, then I want to add those records to the TP tab.

The same basic process happens at the end of the day, using the EoD tab instead of the MD tab.

The scripting dictionary was something someone put me on to on a prior project, so I leveraged it here. The only difference is that on the prior project, I just ignored the records if they were present on both reports. Here, I have to account for them.

 

[Fluff]

Which row of the Tp tab are you trying to update?

 

[reberryjr]

I'm trying to update the row where the RngList matches on both sheets....so the value of column A and column B.

 

[Fluff]

Try

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),[COLOR=#ff0000] Rng.Row[/COLOR]
    End If
Next
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" & [COLOR=#ff0000]RngList(Rng.Value & Rng.Offset(0, 2))[/COLOR]).Value = "Y"
    End If

 

[reberryjr]

@Fluff that works great! Now, a new issue has appeared. This code is adding unique records to the TP tab from the MD tab, but it's only entering the Y in the first Last Row + 1 instance. I can alter the structure of the MD tab to include it in the copy and paste process, but that feels like a work around. Is there another way to code it so the Last Row keeps adjusting, so that each unique record added to the TP tab has Y entered in column J?

    If Not RngList.exists(Rng.Value & Rng.Offset(0, 2)) Then
        mMD.Range("A" & Rng.Row & ":H" & Rng.Row).Copy mTP.Cells(mTP.Rows.Count, "A").End(xlUp).Offset(1, 0)
        mTP.Range("J" & mTPLR + 1).Value = "Y"
    End If
Next

 

[Fluff]

Simply increment the value of mTPLR by 1 after every copy