[EXCEL] Scripting Dictionary - Values not quite right

[LemonBear]

Hi all

I have a VBA script that grabs data from a sheet sticks it in a dictionary and then itterates over all the rows to see if any match, if they do it adds 1 to the key/item - if it doesn't it creates a new key and continues.

At the end of the script I have a number of keys and a value which is = to the number of times it found a matching row - that all works.

The problem I have is when I then output the value from the dictionary.. it sometimes is +1 over the actual dictionary, any ideas?

If oList.Exists(sCurrService) = True Then
                                            With oList
                                                iCurrService = 0
                                                iCurrService = .items()(0)
                                                iCurrService = iCurrService + 1
                                                .Remove (sCurrService)
                                                .Add sCurrService, iCurrService
                                            End With
                                        
                                        Else
                                            oList.Add sCurrService, iCurrService
                                            With oList
'                                                Debug.Print .keys()(0), .Items()(0)
'                                                Debug.Print .items()(0)
                                            End With
                                        End If
                                    End If
                            iCurrService = 0
                            Next

That creates the dictionary and all is good:
Output in intermediate window:

Basic collaboration with Cisco Webex 17
Busy Lamp Field (BLF) 17
HV Select User License 17
Yealink T46U - Recurring 17

So the Key is the name of a service/product and the number is the amount found in the sheet based on a MATCH for a client name in a specified column (^^^ that works as expected)

I then take the dictionary and out put to a range of cells:

'Copy Customer Services to B Column - 
With Worksheets(sDestWorksheetName)

'    .Range("D" & iStartCell).Resize(, oList.Count) = oList.items #Have tried with and without this line

    .Range("D" & iStartCell).Resize(oList.Count) = Application.Transpose(oList.items)

'    .Range("B" & iStartCell).Resize(, oList.Count) = oList.keys #Have tried with and without this line

    .Range("B" & iStartCell).Resize(oList.Count) = Application.Transpose(oList.keys)
                    Debug.Print oList.keys()(0), oList.items()(0) = Confirms Item/Value is correct
                    Debug.Print oList.keys()(1), oList.items()(0) = Confirms Item/Value is correct
                    Debug.Print oList.keys()(2), oList.items()(0) = Confirms Item/Value is correct
                    Debug.Print oList.keys()(3), oList.items()(0) = Confirms Item/Value is correct
End With

However the output onto the sheet:

Basic collaboration with Cisco Webex 17
Busy Lamp Field (BLF) 17
HV Select User License 17
Yealink T46U - Recurring 18

The Title and Value are in 2 columns - but as you can see "Yealink T46 - Recurring" = 18 and not 17 as the dictionary shows

[AdorablePlum]

Are you able to post an example workbook?

[Zvoni]

Are you able to post an example workbook?

Nevermind there is missing code

'For-Loop missing
    'If-Statement missing
        If oList.Exists(sCurrService) = True Then
            With oList
                iCurrService = 0
                iCurrService = .items()(0)
                iCurrService = iCurrService + 1
                .Remove (sCurrService)
                .Add sCurrService, iCurrService
            End With        
        Else
            oList.Add sCurrService, iCurrService
            With oList
'                                                Debug.Print .keys()(0), .Items()(0)
'                                                Debug.Print .items()(0)
            End With
        End If
    End If 'Missing If-Statement
    iCurrService = 0
Next  'Missing For-Loop

All said: I don't understand, why you check for existence and go through that convoluted mess just to add up how many times a specific key occurs

My approach (not tested)

'For-Loop missing
    'If-Statement missing
        If oList.Exists(sCurrService) = True Then
            oList.Item(sCurrService) = CLng(oList.Item(sCurrService)) + 1 'No Error-Trap!!
        Else           
            oList.Add sCurrService, 1  'First (=1) occurence of Key found
        End If
    End If
Next

And as for "transfering it to a Worksheet":
Just run through the Keys and Items-Array

Along the lines of

For i = Lbound(oList.Keys) To UBound(oList.Keys)
Debug.Print "Key " & i & "=" & oList.Keys(i) & " - Item " & i & "=" & oListItems(i)
Next

Instead of Debug.Print you can use

MyWorksheet.Cells(i+RowOffSet,"B") = oList.Keys(i)
MyWorksheet.Cells(i+RowOffSet,"C") = oList.Items(i)

RowOffset is say
Your Keys/Items-Array starts at 0, but your first Row in your Sheet is in Row 7
So RowOffset would be 7