VBA Dictionary

[JessP]

I was helped here a while back to create some code to look at each cell in column C of Query that is equal to a cell in column C of SC. SC values show up multiple times in Query, so it loops through until the values either don't match and column G is blank or an instance is found and the value in column G of SC is changed.

Basically, col C on both sheets contains multiple instances of a project name, one for each task associated with it. Query col G is blank only if the task is incomplete. For each project, I want the next incomplete task (Query col H) in SC col M. This much works, thanks to Fluff.

However, I want to also add other information associated with that task, which led to the following attempt.

Dim Cl as Range
Dim SC as Worksheet
Dim Query as Worksheet

Set SC = Sheets("Scorecard")
Set Query = Sheets("Query")

With CreateObject("scripting.dictionary")
    For Each Cl In SC.Range("C2", SC.Range("C" & Rows.Count).End(xlUp))
        If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 10)
    Next Cl
    For Each Cl In Query.Range("C2", Query.Range("C" & Rows.Count).End(xlUp))
        If .exists(Cl.Value) And Cl.Offset(, 4) = "" Then
            .Item(Cl.Value).Value = Cl.Offset(, 5).Value
            .Item(Cl.Value).Offset(, -8).Value = Cl.Offset(, 7).Value
            .Item(Cl.Value).Offset(, -7).Value = Cl.Offset(, 8).Value
            .Item(Cl.Value).Offset(, -6).Value = Cl.Offset(, 9).Value
            .Item(Cl.Value).Offset(, -5).Value = Cl.Offset(, 10).Value
            .Item(Cl.Value).Offset(, -4).Value = Cl.Offset(, 11).Value
            .Item(Cl.Value).Offset(, -3).Value = Cl.Offset(, 12).Value
            .Item(Cl.Value).Offset(, -2).Value = Cl.Offset(, 13).Value
            .Item(Cl.Value).Offset(, -1).Value = Cl.Offset(, 14).Value
        End If
    Next Cl
End With

It seems to only work when there is only one incomplete task for a project. I'm quite new to VBA dictionaries, so this may be a silly question, but could anyone help me understand why it doesn't work for the rest of the cases and how to remedy that?

Thanks

 

[Fluff]

Could you please explain, what you are trying to do?

 

[JessP]

Could you please explain, what you are trying to do?

Once the code finds the incomplete task for a project and transfers the task to the SC sheet, I want to take values from the same row in Query (columns J:Q, for now at least) and put them in the SC row in columns E:L. For instance, Query J:M contain names of people associated with the particular task - I want them in SC columns E:H. Does that make sense?

 

[Fluff]

In what way is it not working?

 

[JessP]

It doesn't work when there are multiple incomplete tasks. Instead of taking the values from the same row as the first incomplete task, it takes them from whatever the last task is. The tasks are in order of due date, with earliest first, so the first one while going down the list is what I want. What strikes me as odd is that it does take the first incomplete task, but the last instance of all the other information.

 

[Fluff]

Ok, try adding the line in blue as shown

            .Item(Cl.Value).Offset(, -2).Value = Cl.Offset(, 13).Value
            .Item(Cl.Value).Offset(, -1).Value = Cl.Offset(, 14).Value
           [COLOR=#0000ff] .Remove Cl.Value[/COLOR]
         End If

 

[JessP]

That works, thanks! Does it just stop the code from looking at future instances of the project (cl.value)?

 

[Fluff]

Yes, when it finds a project for the first time it removes that project from the dictionary, so every time it finds that project again this line will return False

If .exists(Cl.Value) And Cl.Offset(, 4) = "" Then

 

[JessP]

Thanks for explaining! I appreciate it.

 

[Fluff]

You're welcome