Scripting Dictionary on Filtered Range, Not Writing Back

[reberryjr]

I need some help with using a scriptingdictionary on a filtered range. Essentially,I’m trying to identify instances on the Variables tab (columns A & B), thatalso exist on the Holds tab (columns K & G).Where they box exist, I want the values fromcolumns C & D on the Variables tab, to populate into columns R & S onthe Holds tab.I’ve verified that thereare dozens of matches that should be being identified, however, as I stepthrough the code, there aren’t any updates being made at all.

Thoughts on where I’ve gone wrong on this?

Sub IdentifyOwners()[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]Dim d As Workbook
Dim dH, dV As Worksheet
Dim Rng As Range
Dim RngList As Object[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]Set d = ThisWorkbook
Set dH = d.Sheets("Holds")
Set dV = d.Sheets("Variables")
Set RngList = CreateObject("Scripting.Dictionary")[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]SortAscending dH, "R1"[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]With dH
    On Error Resume Next
    .UsedRange.AutoFilter Field:=18, Criteria1:=""
End With[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]dLR1 = dH.Range("B" & Rows.Count).End(xlUp).Row[/COLOR][/FONT]
[FONT=Tahoma][COLOR=#000000]If dLR1 > 1 Then
On Error Resume Next
    For Each Rng In dV.Range("A2", dV.Range("A" & dV.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value & "|" & Rng.Offset(0, 1)) Then
            RngList.Add (Rng.Value & "|" & Rng.Offset(0, 1)), Rng
        End If
    Next
        For Each Rng In dH.Range("K2", dH.Range("K" & dH.Rows.Count).End(xlUp))
            If RngList.Exists(Rng.Value & "|" & Rng.Offset(0, -4)) Then
                dH.Range("R" & Rng.Row).Value = sV.Range("C" & Rng.Row).Value
                dH.Range("S" & Rng.Row).Value = sV.Range("D" & Rng.Row).Value
            End If
        Next
End If
       
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True[/COLOR][/FONT]

[FONT=Tahoma][COLOR=#000000]End Sub[/COLOR][/FONT]

 

[Fluff]

On the Exists & Add lines you need to use .Value, otherwise you'll put the range object into the dictionary & therefore you'll never have a match

 

[reberryjr]

@Fluff, thanks for the quick response. I'm a little confused, as I have Rng.Value in those lines. On the off chance that you're referring to the .Offset portion of those lines, I added the .Value there, and still nothing maps back.

On Error Resume Next
    For Each Rng In dV.Range("A2", dV.Range("A" & dV.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value & "|" & Rng.Offset(0, 1).Value) Then
            RngList.Add (Rng.Value & "|" & Rng.Offset(0, 1).Value), Rng
        End If
    Next
        For Each Rng In dH.Range("K2", dH.Range("K" & dH.Rows.Count).End(xlUp))
            If RngList.Exists(Rng.Value & "|" & Rng.Offset(0, -4).Value) Then
                dH.Range("R" & Rng.Row).Value = sV.Range("C" & Rng.Row).Value
                dH.Range("S" & Rng.Row).Value = sV.Range("D" & Rng.Row).Value
            End If
        Next
End If

 

[John_w]

What is the second On Error Resume Next for? What error are you expecting it to trap? Remove it and see if an error occurs.

Shouldn't the sV variable be dV? sV is undeclared and undefined. Use Option Explicit to prevent these type of issues.

 

[Fluff]

In that case one possibility is the values in A&B on on the dV sheet do not match the K&G values on the dH sheet.
That said, if you add Option Explicit to the very top of the module (before any code) & try and run the macro I think that you will get an error.
I would also recommend getting rid of

On Error Resume Next

from the code as all it will do is mask any errors & make debugging more difficult.

 

[reberryjr]

Ok, I've inserted the Option Explicit, and removed the On Error Resume Next. I'm getting information mapped back, but it's not coming from the correct rows on the dV tab.

 

[Fluff]

Have you made any other changes to the code?

 

[reberryjr]

Only adding the .Value after the .Offsets. Here is the latest version:

Option Explicit
Sub IdentifyOwners()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Dim d As Workbook
Dim dH, dV As Worksheet
Dim Rng As Range
Dim RngList As Object
Dim dLR1 As Long
Set d = ThisWorkbook
Set dH = d.Sheets("Holds")
Set dV = d.Sheets("Variables")
Set RngList = CreateObject("Scripting.Dictionary")
SortAscending dH, "R1"
With dH
    .UsedRange.AutoFilter Field:=18, Criteria1:=""
End With
dLR1 = dH.Range("B" & Rows.Count).End(xlUp).Row
If dLR1 > 1 Then
    For Each Rng In dV.Range("A2", dV.Range("A" & dV.Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value & "|" & Rng.Offset(0, 1).Value) Then
            RngList.Add (Rng.Value & "|" & Rng.Offset(0, 1).Value), Rng
        End If
    Next
        For Each Rng In dH.Range("K2", dH.Range("K" & dH.Rows.Count).End(xlUp))
            If RngList.Exists(Rng.Value & "|" & Rng.Offset(0, -4).Value) Then
                dH.Range("R" & Rng.Row).Value = dV.Range("C" & Rng.Row).Value
                dH.Range("S" & Rng.Row).Value = dV.Range("D" & Rng.Row).Value
            End If
        Next
End If
       
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[Fluff]

Only adding the .Value after the .Offsets.

What about correcting the sheet references

The problem is that you are pulling info from the dV sheet based on the Rng.row (ie current row)from the dH sheet, whereas you need to retrieve the item from the dictionary.
try

If RngList.Exists(Rng.Value & "|" & Rng.Offset(0, -4).Value) Then
    dH.Range("R" & Rng.Row).Resize(, 2).Value = RngList(Rng.Value & "|" & Rng.Offset(0, -4).Value).Offset(, 2).Resize(, 2).Value
End If

 

[reberryjr]

@Fluff THANK YOU!!! That works like a charm! I don't understand the .Resize part. From what I've seen through googling in the past, it alters the size of the range, but we have (,2) which would be changing it by 2 columns. I'm not sure I follow that.