Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

[DocAElstein]

Continued from last post: Some notes from this question:
http://www.eileenslounge.com/viewtop...281312#p281312
Yasser Question.JPG https://excelfox.com/forum/showthrea...ll=1#post15418

Hans Solution. What’s he doing:

Rem 1 Make dictionary of Dictionaries2
There are two dictionary variables.
The first one contains all the unique name values from column A . So this is the unique names dictionary
We loop down to build that dictionary, and the solution is relying on a un unbroken sequential list of names, in other words no mixed up , but an order list like
Name1
Name1
Name1
Name2
Name2
..etc.
In that main loop , all the values, from column B are put in the Item ( which is itself a dictionary ) of each unique name in the unique names dictionary.
This is the clever line that does that. The line is done for each row in the data to be looked in ( column B )
Let dct1(rng1(r1, 1))(Int(rng1(r1, 2))) = 1
The 1 is arbitrary. What we are doing is like referring to ( trying to put 1 into ) the key of an item in the second dictionary that does not exist. When this is done, rather than error, the Scripting.Dictionary is programmed to make an item with that key.
The end result of all this is that we end up with a main dictionary that has a key for each unique name. The item for each name / Key has a second dictionary in it of all the Integer parts of the Date & time in column B. ( The dictionary will be seeing the basic Excel .Vaue2 of the date & time, so the Integer part will be just the date.
Here is a pseudo couple of code lines to demo that last bit

Dick1(Name1) ( 2021-02-19 ) = 1
Dick1(Name1) ( 2021-01-26 ) = 1

You see what’s going on is the following:
Dick1(Name1) will always return the same thing which is the Item in Dick1 with the Key of Name1
So Effectively those lines are pseudo

Dick2 ( 2021-02-19 ) = 1
Dick2 ( 2021-01-26 ) = 1

What those code lines try to do is put a 1 in the items of a Dick2 element that does not exist. As noted, the Scripting.Dictionary is programmed to make an item with that key rather than error if such an action is attempted.
So that is just a convenient way to make the second dictionaries – Note I said dictionaries

We end up with this:
Dick1 keys



Dick2KeysWichAreDicksInDick1Items.jpg


These lines give me that from doing a Shift F9 on any variable
Shift F9 on vTemps for Watch Window.JPG http://i.imgur.com/Ms7HmG6.jpg

---

Rem 2
We have an Outer loop and an inner loop in it.
__The outer loop is done once for each unique name, so for each key of the main dictionary
____The inner loop goes down the entire F column Check dates and does a write out any missing dates, that is to say dates not in the dictionary that is the item for that unique name, key of the main dictionary

---

Hans macro

Option Explicit
Sub ListMissing()  '  ' Hans    http://www.eileenslounge.com/viewtopic.php?p=281312#p281312
Dim vTemp1, vTemp2 ' For development and debug
Dim wsh1 As Worksheet
Dim wsh2 As Worksheet
Dim rng1 As Variant
Dim rng2 As Variant
Dim m1 As Long
Dim m2 As Long
Dim r1 As Long
Dim r2 As Long
Dim r3 As Long
Dim dct1 As Object
Dim dcTemp2 As Object
Dim n As Variant
 Set dct1 = CreateObject("Scripting.Dictionary")
 Set wsh1 = Worksheets("Sheet1")
 Let m1 = wsh1.Range("A" & wsh1.Rows.Count).End(xlUp).Row
 Let rng1 = wsh1.Range("A2:B" & m1).Value
Rem 1  Make dictionary of Dictionaries
    For r1 = 1 To UBound(rng1)
        If Not dct1.Exists(rng1(r1, 1)) Then ' this gives us  3 elements in  the  dct1  that have like key  aa  and  the item is an empty dictionary object
         Set dcTemp2 = CreateObject("Scripting.Dictionary") ' This effectively clears the variable used temporarily
         dct1.Add Key:=rng1(r1, 1), Item:=dcTemp2
        End If
     Let dct1(rng1(r1, 1))(Int(rng1(r1, 2))) = 1 ' the  1  is arbritrary, we effectively create a Key looking like  aa 2021-02-19 in the  second dictionary that is the item of the unique
    Next r1
 vTemp1 = dct1.keys()  '  Dick1.JPG                                http://i.imgur.com/zTWYpuy.jpg
 vTemp2 = dct1.items() '  Dick2KeysWichAreDicksInDick1Items.jpg    http://i.imgur.com/Jsd2kXS.jpg
'
 Let m2 = wsh1.Range("F" & wsh1.Rows.Count).End(xlUp).Row
 Let rng2 = wsh1.Range("F2:F" & m2).Value
'
 Set wsh2 = Worksheets("Sheet2Hans")
 wsh2.Range("A2:B" & wsh2.Rows.Count).Clear
 
 
 Rem 2  Go through checking for existance of an Item. For no existance , then that is missing data
  Let r3 = 1
    ' The outer loop is done once for each unique name, so for each key of the main dictionary ===========
    For Each n In dct1.keys '  this and next line make it  For Each  of .._
     Set dcTemp2 = dct1(n)  ' _.. the dictionries within each item of  Dick1  In other words  For Each  Name
        ' -----------------------------------------------
        ' The inner loop goes down the entire  F column Check dates and does a write out any  missing dates, that is to say dates not in the dictionary that is the item for that  unique name,  key of the main dictionary
        For r2 = 1 To UBound(rng2) ' Going down the entire   F  range
            If Not dcTemp2.Exists(rng2(r2, 1)) Then
             Let r3 = r3 + 1
             Let wsh2.Range("A" & r3).Value = n           '  n is the key, the unique name, in the main large dictionary
             Let wsh2.Range("B" & r3).Value = rng2(r2, 1) ' This will be the missing entry
            Else
            End If
        Next r2 ' ________________________________________
    
    Next n ' ==============================================================================================
End Sub