Adding Value From Another Worksheet When Range Exists On Both Worksheets

[reberryjr]

I'm trying to compare ranges on two worksheets (Total_Population) and (Prior_Day). When the range exists on both sheets, I want the value from column J of the Prior_Day sheet, to be populated in column J of the Total_Population sheet.

I use a scripting dictionary to do something very similar (it simply places "Y" in a column when the ranges match), so I'm trying to modify it to fit my needs for this task. I think my issue is that I haven't properly coded to retrieve the value from the Prior_Day sheet.

Thoughts?

Private Sub cmd_AssignAM_Click()
Application.ScreenUpdating = False
Dim mTP, mSumm, mAM, mPD As Worksheet
Dim Response As VbMsgBoxResult
Dim Rng As Range
Dim RngList As Object
Set mTP = ThisWorkbook.Sheets("Total_Population")
Set mSumm = ThisWorkbook.Sheets("Summaries")
Set mAM = ThisWorkbook.Sheets("AM_Consolidated")
Set mPD = ThisWorkbook.Sheets("Prior_Day")
Set RngList = CreateObject("Scripting.Dictionary")
If mTP.FilterMode = True Then
    mTP.ShowAllData
Else
End If
If mSumm.Range("K26").Value > 0 Then
Response = MsgBox("Please review the records that show as past SLA, and make any necessary corrections.", vbOKCancel)
If Response = vbOK Then
    With mTP
        On Error Resume Next
        .UsedRange.AutoFilter Field:=8, Criteria1:="<" & Date
    End With
mTP.Activate
Unload Me
Exit Sub
ElseIf Response = vbCancel Then
If mTP.FilterMode = True Then
    mTP.ShowAllData
Else
End If
'Adds the data in columns A:N from the Total Population tab into the dictionary.
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) & "|" & Rng.Offset(0, 3) & "|" & Rng.Offset(0, 6)) Then
        RngList.Add Rng.Value & "|" & Rng.Offset(0, 1) & "|" & Rng.Offset(0, 2) & "|" & Rng.Offset(0, 3) & "|" & Rng.Offset(0, 6), Rng
    Else
        Set RngList(Rng.Value & "|" & Rng.Offset(0, 1) & "|" & Rng.Offset(0, 2) & "|" & Rng.Offset(0, 3) & "|" & Rng.Offset(0, 6)) = _
            Union(RngList(Rng.Value & "|" & Rng.Offset(0, 1) & "|" & Rng.Offset(0, 2) & "|" & Rng.Offset(0, 3) & "|" & Rng.Offset(0, 6)), Rng)
    End If
Next
'If the data in columns A:N on the Prior Day tab are in the dictionary, it adds the processor assigned on the prior day.
For Each Rng In mPD.Range("A2", mPD.Range("A" & mPD.Rows.Count).End(xlUp))
    If RngList.Exists(Rng.Value & "|" & Rng.Offset(0, 1) & "|" & Rng.Offset(0, 2) & "|" & Rng.Offset(0, 3) & "|" & Rng.Offset(0, 6)) Then
        RngList(Rng.Value & "|" & Rng.Offset(0, 1) & "|" & Rng.Offset(0, 2) & "|" & Rng.Offset(0, 3) & "|" & Rng.Offset(0, 6)).Offset(, 9).Value = Rng.Offset(, 9).Value
    'ElseIf Not RngList.Exists(Rng.Value & "|" & Rng.Offset(0, 1) & "|" & Rng.Offset(0, 2) & "|" & Rng.Offset(0, 3) & "|" & Rng.Offset(0, 6)) Then
    '    mEoD.Range("A" & Rng.Row & ":N" & Rng.Row).Copy mTP.Cells(mTP.Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
Next
RngList.RemoveAll
SortAscending mTP, "I1", "C1"
With mTP
    On Error Resume Next
    .UsedRange.AutoFilter Field:=10, Criteria1:=""
End With
End If
End If
Unload Me
'Call Formatting

mTP.Activate
Application.ScreenUpdating = True
End Sub

 

[reberryjr]

Nevermind. I finally got it to walk through and found that my End Ifs were in the wrong place, so it was just closing out and not running the dictionary