Array attached to Dictionary Not Updating...

[bradmsg]

Hello all,

I have a dictionary from sheet 1. Im checking sheet 2 (Col W) with the first 2 parts of the key from the dictionary.

if the key exists, populate an array of counts for each type, per key.

In the end i would just like to output them to a sheet 3.

Problem:

The counts are not populating. in any of the debug statements, they are coming up blank.... Can anyone spot where I'm messing up?

Dim data2 As Variant
data2 = Sheets("Sheet2").Range("W1:W" & Sheets("Sheet2").Cells(Rows.Count, "W").End(xlUp).Row).value
Dim key As String
Dim value As String
Dim counts() As Long
For i = 2 To UBound(data2, 1)
    key = ""
    value = ""
    counts = Array(0, 0, 0, 0, 0)
    arr = Split(data2(i, 1), "|")
    key = arr(0) & "|" & arr(1)
    value = arr(2)

    If dict.Exists(key) Then
        counts = dict.Item(key)
        Debug.Print "Before update: key=" & key & ", value=" & value & ", counts=" & Join(counts, ", ")
        Select Case value
            Case "1-Emergency"
                counts(0) = counts(0) + 1
            Case "3A - High Sustain"
                counts(1) = counts(1) + 1
            Case "3B - Medium Sustain"
                counts(2) = counts(2) + 1
            Case "4A - High Enhance"
                counts(3) = counts(3) + 1
            Case "Type 5"
                counts(4) = counts(4) + 1
        End Select
        Debug.Print "After update: key=" & key & ", value=" & value & ", counts=" & Join(counts, ", ")
        dict.Item(key) = counts
        Debug.Print "After dict update: key=" & key & ", counts=" & Join(dict.Item(key), ", ")
    End If
Next i

 

[DanteAmor]

Hi @bradmsg, thanks for posting on the forum.

I'm sorry to tell you that you have several problems in your macro.

I list them below:

​

1. These variables need to be declared.​

  Dim i As Long
  Dim arr As Variant
  Dim dict As Object

​

2. Missing set dict object​

  Set dict = CreateObject("Scripting.Dictionary")

​

3. This condition is never met because you have not added any keys to the dictionary.​

So the macro does not perform any action.​

If dict.Exists(key) Then

I recommend using the Option Explicit statement at the beginning of your code, it will force you to declare all your variables, but it helps you check the syntax of some instructions, it also checks for writing errors in variables among other things.

To fix your code, you need to explain to me what you need, what is the end goal.
Put example data and the final result you need.

It is very important that you put the examples before the macro and after the macro.
Use XL2BB tool to put the examples.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[bradmsg]

DanteAmor,​

thank you for your time replying back to me. It seems i posted prematurely and solved my own issue!

Once again thank you for your response!!