Adding Value From Another Worksheet When Range Exists On Both Worksheets

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
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?

Code:
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,330
Messages
5,675,152
Members
419,552
Latest member
jsanjur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top