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]

So, I was testing this more thoroughly, and I found something odd. There seems to be a single record that is on both the MD and TP tabs, but is not getting a Y in column J. I've compared the data that's being used in the RngList, and the data is an exact match on both tabs.

 

[Fluff]

What values do you have in Cols A & C?
Can you have more than 1 occurrence of the concatenated values A&C?
Are you sure that the "missing" value is in the dictionary & is EXACTLY the same as the dictionary value (ie same case, not extra spaces etc)?
I'd also recommend when concatenating values in this manner use some thing like

If Not RngList.Exists(Rng.Value & "|" & Rng.Offset(0, 2)) Then
        RngList.Add Rng.Value & "|" & Rng.Offset(0, 2), Nothing

Otherwise 123 & 78 would be the same value as 12 & 378

 

[reberryjr]

What values do you have in Cols A & C?

Depending on the source of the data, it could be anything. For this particular instance, column A is "Lotus Notes" and column C is the subject line of an email.

Can you have more than 1 occurrence of the concatenated values A&C?

Yes, that is possible. I'm glad you brought that up. I'll work on concatenating columns A, B & C into the dictionary.

Are you sure that the "missing" value is in the dictionary & is EXACTLY the same as the dictionary value (ie same case, not extra spaces etc)?

I'm positive. I've copied the record in question from both sheets, and did the ol' fashioned =A2=A3 and repeated for column C. Everything came back as true.

Excellent point on adding the pipe into the mix. I never thought about that.

 

[Fluff]

Modifying the dictionary to work on the 1st 3 cols, may well get rid of your problem. I suspect that you have 2 lines that are the same & therefore the dictionary, will only record one of them.

 

[reberryjr]

That was the issue! Thanks again!

 

[Fluff]

You're welcome

 

[reberryjr]

@Fluff Ok, so I've got the code working pretty well. There seems to be one issue during the testing, that I don't understand.

The below code is "supposed" to place a Y in column J of the TP tab, where values in columns A, B & C match across the MD tab against the contents of the TP tab. However, if there are 2 sets of identical records on the MD and TP tabs, it's only placing a Y in column J, for 1 of the 2 records. I need it to place a Y in column J for both records. I realize that it seems counterintuitive since they're duplicates, but this is what the end user wants.

Thoughts?

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, 1) & "|" & rng.Offset(0, 2)) Then
        RngList.Add rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2), rng.Row
    End If
Next
For Each rng In mMD.Range("A2", mMD.Range("A" & mMD.Rows.Count).End(xlUp))
    If RngList.Exists(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) Then
        mTP.Range("J" & RngList(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2))).Value = "Y"
    End If
    If Not RngList.Exists(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) Then
        mMD.Range("A" & rng.Row & ":K" & rng.Row).Copy mTP.Cells(mTP.Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
Next
RngList.RemoveAll
MsgBox ("The mid-day reports have been consolidated to the Total Population tab.  Please assign Processors as necessary.")
Call Formatting
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Try populating the dictionary with the value son the mMD sheet first

 

[reberryjr]

Ok, I tried that, and now it's not matching all of the records it should be.

'Concatenates values in column A, B & C on the MD tab; into the dictionary.
For Each rng In mMD.Range("A2", mMD.Range("A" & mMD.Rows.Count).End(xlUp))
    If Not RngList.Exists(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) Then
        RngList.Add rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2), rng.Row
    End If
Next
'Concatenates values in column A, B & C on the TP tab; into the dictionary.
For Each rng In mTP.Range("A2", mTP.Range("A" & mTP.Rows.Count).End(xlUp))
'If the 2 sets of dictionary items match, adds Y to column J of the TP tab.
    If RngList.Exists(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) Then
        mTP.Range("J" & RngList(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2))).Value = "Y"
    End If
'If the 2 sets of dictionary items don't match, adds the record from the MD tab, to the TP tab.
    If Not RngList.Exists(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) Then
        mMD.Range("A" & rng.Row & ":K" & rng.Row).Copy mTP.Cells(mTP.Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
Next
RngList.RemoveAll

 

[Fluff]

How about

For Each rng In mTP.Range("A2", mTP.Range("A" & mTP.Rows.Count).End(xlUp))
    If Not RngList.Exists(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) Then
        RngList.Add rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2), rng
    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)
    End If
Next
For Each rng In mMD.Range("A2", mMD.Range("A" & mMD.Rows.Count).End(xlUp))
    If RngList.Exists(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) Then
        RngList(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)).Offset(, 9).Value = "Y"
    ElseIf Not RngList.Exists(rng.Value & "|" & rng.Offset(0, 1) & "|" & rng.Offset(0, 2)) Then
        mMD.Range("A" & rng.row & ":K" & rng.row).Copy mTP.Cells(mTP.Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
Next