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

reberryjr

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

Code:
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
 
That appears to have fixed it, but can you help me understand this verbiage?

Code:
    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)
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
The part in blue returns the range currently held in the dictionary item
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top