scripting dictionary with 2 Keys and 1 Item

[Gtabtr1]

hello all,
Normally I would use Index/Match but I'm looking for a VBA solution.

In column A I have a list of numbers.
In column B I have a list of names.
In column C I have a date.

Can a scripting dictionary have 2 keys (in column A & B) and 1 item (in column C)? Match the value in columns A & B and retrieve date value in column C?

Thank you

 

[Fluff]

Depending on what you are trying to do, you could concatenate col A & B values then use that as the key

 

[ZVI]

Can a scripting dictionary have 2 keys (in column A & B) and 1 item (in column C)? Match the value in columns A & B and retrieve date value in column C?

Hi,
Sure, for example like this:

Option Explicit
 
Dim Dic As Object
 
Sub InitDic()
  Dim a()
  Dim i As Long
  Dim key As String
  a() = Sheets(1).UsedRange.Range("A:C").Value  ' Change Sheets(1) to suit
  Set Dic = CreateObject("Scripting.Dictionary")
  With Dic
    .CompareMode = 1
    For i = 2 To UBound(a)  ' 2  is for skipping the 1st title row
      key = Trim(a(i, 1)) & vbTab & Trim(a(i, 2))
      If Len(key) > 0 Then .Item(key) = a(i, 3)
    Next
  End With
End Sub
 
Function GetDic(Val1, Val2)
  Dim key As String
  key = Trim(Val1) & vbTab & Trim(Val2)
  If Dic Is Nothing Then InitDic
  If Dic.Exists(key) Then
    GetDic = Dic(key)
  Else
    GetDic = vbNullString
  End If
End Function
 
Sub Test()
  MsgBox GetDic(Sheets(1).Range("A2"), Sheets(1).Range("B2"))
End Sub

Regards

 

[ZVI]

The correction: If Len(key) > 1 Then .Item(key) = a(i, 3)