Hello Yasser,
I am a bit lost as to what is actually wanted or being discussed here.. But – going back to the first post and title question…
Get existing item index in dictionary and as you said….
YasserKhalil wrote:.... I need to learn more about how to use the dictionaries in such situations, so I expect to find an answer using the dictionary approach....
In the macro below..
Rem 2
This is using your uploaded sample1 test data, (
https://imgur.com/y9mYRE9" onclick="window.open(this.href);return false; ) , just the first 6 rows to created a dictionary object,
Dik , which looks like your first post screen shot
https://imgur.com/cmRFkjK" onclick="window.open(this.href);return false;
https://imgur.com/0VwM44W" onclick="window.open(this.href);return false;
The last bit ,
2c) , also gets the Keys array, my variable
DikKeys() , ( which as Hans said is , like the Items array , 0-based, so the first item is number 0. ( But that is less relevant for what I am trying to show
** ) )
https://imgur.com/t4Zp5Fq" onclick="window.open(this.href);return false;
Note: Displayed in the VB Watch Window for the Dictionary is the
Keys, not the
Items
Rem 3 is
Geting existing item index in dictionary
‘3c) is the main bit I am trying to show
I don’t think there is an in built way of getting at the Item number that you want from a Dictionary. But it is easy to get from
Match
What
Match does simplified is …
Integer position along of …
Match([This] , [inside this 1 “width” thing] , [0]
)
(
** The integer number returned starts at 1, regardless of what the indicies are of the 1 “width” thing - so if , for example it was a 1 D array, it would make no difference if it started at indicie of 0 or 1 etc )
The
[0] is just telling
Match to look for the exact match
[This] would be in your case what Key you are looking the item number of
[inside this 1 “width” thing] is the second argument of Match which must be a one “width” thing. It can be a
column,
row,
a 1 row 2 D array,
a 1 column 2D array,
a 1 D array, like for example our keys array
DikKeys()
If , in my code example, the Key you give in the first argument of Match,
is in the second argument , the Keys array
DikKeys() ,
then match will return the position along of that key in
DikKeys()
The first key has the position along of 1
The second key has the position along of 2
The third key has the position along of 3
…etc….
_.____
I suppose the jist of all I am trying to explain here is
Question: Get existing item index in dictionary, with a key of
KeyX
Answer : like:
= Match( KeyX , Keys() , 0 )
( Similarly , you can get the item number of any item , say ,
ItemX
like:
= Match( ItemX , Items() , 0 ) )
Alan
Code: Select all
Sub CreatDikGetExistingItem() ' http://www.eileenslounge.com/viewtopic.php?f=27&t=34502
Rem 1 Create a Dik ' https://excelmacromastery.com/vba-dictionary/
Dim Dik As Object: Set Dik = CreateObject("Scripting.Dictionary"): Let Dik.CompareMode = vbTextCompare
Rem 2 Fill Dik
' 2a) Data for Dik
Dim Aey() As Variant
Let Aey() = Worksheets("Sheet1").Range("A1:C6").Value '
' 2b) Fill Dik with data
Dim Cnt
For Cnt = 1 To 6 ' sample data only - https://imgur.com/63acQNv - https://imgur.com/fcIrboC - https://imgur.com/EfGVMmd
Dik.Add Key:=CStr(Aey(Cnt, 2)), Item:=Aey(Cnt, 1) & " " & Aey(Cnt, 3)
Next Cnt
' 2c) The keys array
Dim DikKeys() As Variant
Let DikKeys() = Dik.keys() ' https://imgur.com/WcDjwVd
Rem 3 If exists get the index number of the item inside the dictionary
' 3a) Example
Dim Answer As Variant
Let Answer = InputBox(prompt:="Input key to get item number of if it exists", Default:="2019-09-28")
' 3b) Exists Method ' https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/exists-method
If Dik.exists(Answer) Then
' 3c) Use match to get at item number
Dim KeyItmNmbr As Long
Let KeyItmNmbr = Application.Match(Answer, DikKeys(), 0)
MsgBox prompt:="For key " & Answer & " the item number is " & KeyItmNmbr
Else
MsgBox prompt:="The key of " & Answer & " does not exist"
End If
End Sub
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also