0

I have 3 dictionaries: one with the codes, the other one with currencies and S_Keys that should contain both values. The values are retrieved from the table in one of the worksheets. S_Keys dictionary doesn't work as expected and whenever I have the code value, it fails to find a matching value of a currency that corresponds to the code. How do I fix it?

Example:

Dic1: Codes

Dic2: Currencies

Dic 3: S_Keys: Codes: Currencies.

I know the mapping from the table attachedTable1. Let me know if you have any questions and thank you for your help!

Set W_Wbs = ActiveWorkbook
Set W_Curr = W_Wbs.Sheets("2a")
Set W_Property = W_Wbs.Sheets("2b")
For i = 1 To tblAttributes.DataBodyRange.Rows.Count
    Dim propertyName As String
    propertyName = tblAttributes.DataBodyRange.Cells(i, tblAttributes.ListColumns("Name").index).Value
    Dim propertyCode As String
    propertyCode = tblAttributes.DataBodyRange.Cells(i, tblAttributes.ListColumns("Code").index).Value
    Dim baseCurrency As String
    baseCurrency = tblAttributes.DataBodyRange.Cells(i, tblAttributes.ListColumns("Currency").index).Value
    
    If Application.CountIf(wsPackage.Columns(4),Name) > 0 Then
        S_Keys = Code & "¦" & Currency
        If Not D_Dict.Exists("Currency") Then Set D_Dict("Currency") = CreateObject("Scripting.Dictionary")
        If Not D_Dict.Exists("Keys") Then Set D_Dict("Keys") = CreateObject("Scripting.Dictionary")
        If Not D_Dict.Exists("Name") Then Set D_Dict("Name") = CreateObject("Scripting.Dictionary")
        If Not D_Dict("Code").Exists(Code) Then Set D_Dict("Code")(Code) = CreateObject("Scripting.Dictionary")
        If Not D_Dict("Currency").Exists(Currency) Then Set D_Dict("Currency")(Currency) = CreateObject("Scripting.Dictionary")
        If Not D_Dict("Keys").Exists(S_Keys) Then Set D_Dict("Keys")(S_Keys) = CreateObject("Scripting.Dictionary")
        If Not D_Dict("Name").Exists(propertyName) Then Set D_Dict("Name")(Name) = CreateObject("Scripting.Dictionary")
    End If

    If Not dict.Exists(Name) Then
        dict.Add Name, Array(Code, Currency)
    Else
        Dim existingValues As Variant
        existingValues = dict(Name)
        ReDim Preserve existingValues(0 To UBound(existingValues) + 2)
        existingValues(UBound(existingValues) - 1) = Code
        existingValues(UBound(existingValues)) = Currency
        dict(Name) = existingValues
    End If
Next i
Dim Codes As Variant
vbabeginnercs
  • 9
  • 4
  • Your code seems to contain a few undeclared variables? Where do `Name`, `Code` and `Currency` come from? – Tim Williams Jul 04 '23 at 07:09
  • "S_Keys dictionary doesn't work as expected" - you would need to tell us what you expect... – Tim Williams Jul 04 '23 at 07:11
  • I only included the snippet from the dictionary declaration. I do have Name, Code and Currency declared as strings. I think for S_Keys it should change to the variable but I am not sure – vbabeginnercs Jul 04 '23 at 07:13
  • Thanks for your comments! I go through the loop where I go through a certain number of codes, whenever the code value is found, i.e. a123, I would like to find a matching currency from the table1. If I search by the code values for a123, I can see it's equal to EUR – vbabeginnercs Jul 04 '23 at 07:14
  • Why not perform a match directly on the table? This seems over-complex for what you need to do. – Tim Williams Jul 04 '23 at 07:16
  • Because I need to generate worksheets based on how many codes I loop through. This is another step that is working fine. I would like to include the currency value for each generated worksheet – vbabeginnercs Jul 04 '23 at 07:19
  • Still seems you're over-complicating things.. I've never seen so many CreateObjects in a row for what should technically be a maximum of two dictionaries (code -> currency, name -> code) ? In the way you've explained with the 123 -> EUR, you'd even only need one dictionary. Key = 123, value = EUR and then use `dict.Count` to see how many codes you've added. – Notus_Panda Jul 04 '23 at 08:27
  • @Notus_Panda, thank you for your reply! I am new to dictionaries. How to I create a link between them and later retrieve the value of currency if I know the code value? – vbabeginnercs Jul 04 '23 at 08:32
  • See [this answer](https://stackoverflow.com/a/915333/19353309) for dictionaries. To get the value of the linked key, it'd be something like `If dict.Exists(currencyCode) Then currencyFromCode = dict(currencyCode)`. Be sure you use the if-clause or you'll be adding it (empty) to the dictionary if it doesn't exist yet. – Notus_Panda Jul 04 '23 at 10:17

0 Answers0