encore les tableaux / dictionnaire

[gosselien]

Bonjour,
je tente d'aider une personne mais je rame encore et toujours avec les tableaux et dictionnaires:
le tableau de départ et celui souhaité sont dans mon exemple et le code VBA aussi, mais je ne m'en sors pas
Peut on m'aider à comprendre pourquoi ça ne va pas ?
Merci

le lien:
https://onedrive.live.com/redir?res...907&authkey=!AOyFP-iWzwuEf0g&ithint=file,xlsm

 

[RickXL]

Hi,

Try this:

Sub Regroupe3() ' It's better now !!!
    Sheets("foglio2").Select
    Set d = CreateObject("Scripting.Dictionary")
    For Each c In Range("a1", [a65000].End(xlUp))
        Key = c.Value & "|" & c.Offset(0, 1) & "|" & c.Offset(0, 2) & "|" & c.Offset(0, 3) _
                      & "|" & c.Offset(0, 4) & "|" & c.Offset(0, 6) & "|" & c.Offset(0, 7) & "|" & c.Offset(0, 8)
        If d.Exists(Key) Then d(Key) = d(Key) & " " & c.Offset(0, 5) Else d(Key) = c.Offset(0, 5)
    Next c
    'Sheets("foglio3").Select
    [K2].CurrentRegion.Clear
    For i = 0 To d.Count - 1
        Cells(i + 2, "L").Resize(, 7) = Split(d.Keys()(i), "|"): Cells(i + 2, "S") = d.Items()(i)
    Next
End Sub

 

[gosselien]

Hello

many many thanks

"It's better now !!!" yessssss !

for me, it remains to study it now

Patrick

 

[RickXL]

Hi, no problem.

My macro writing style is quite verbose - lots of statements and blank lines. Yours is very compact and you don't declare the variables. I tried to make my solution match your style. In fact, I made an even shorter one and came up with this:

Sub Regroupe2() ' It's better now !!!
    Sheets("foglio2").Select
    Set d = CreateObject("Scripting.Dictionary")
    For Each c In Range("a1", [a65000].End(xlUp))
        Key = c(1, 1) & "|" & c(1, 2) & "|" & c(1, 3) & "|" & c(1, 4) & "|" & c(1, 5) & "|" & c(1, 7) & "|" & c(1, 8)
        If d.Exists(Key) Then d(Key) = d(Key) & " " & c(1, 6) Else d(Key) = c(1, 6)
    Next c
    [K2].CurrentRegion.Clear
    For i = 0 To d.Count - 1
        Cells(i + 2, "L").Resize(, 7) = Split(d.Keys()(i), "|"): Cells(i + 2, "S") = d.Items()(i)
    Next
End Sub

Basically, it makes a key out of everything except the sizes.
If the key is a new one (Exists=False) the key is added and the item is set to the first size.
If the key is already there (Exists=True) then the new size is appended to (concatenated with) the item.
This output line is really two lines:

Cells(i + 2, "L").Resize(, 7) = Split(d.Keys()(i), "|")
Cells(i + 2, "S") = d.Items()(i)

The key is split and written out first then the sizes are added to the next cell.

I hope this helps.

Sorry I don't speak French (or Italian).

Regards,

 

[gosselien]

Thank you,

i speak english just a little... i'am new zeland citizen, and my name is scottish but i live in belgium , so, my english is very poor

I'll read your code for my VBA Culture

Patrick

 

[gosselien]

Thank you,

i speak english just a little... i'am new zeland citizen, and my name is scottish but i live in belgium , so, my english is very poor

I'll read your code for my VBA Culture

i don't declare the variables here because its a exemple

Patrick

ps: for me it's more "understandable" with "offset"


If d.Exists(Key) Then d(Key) = d(Key) & " " & c(1, 6) Else d(Key) = c(1, 6)

If d.Exists(Key) Then d(Key) = d(Key) & " " & c.Offset(0, 5) Else d(Key) = c.Offset(0, 5) ---> more flexible in my opinion

 

[RickXL]

Hi,

Thanks for the information, very interesting.

c(1, 5) is short for c.cells(1, 5).
Cells(1, 5) would normally be cell E1. However, by saying c.Cells you are starting the range from the address of c. The numbers are just 1 different from the Offset numbers.

Anyway, no problem.

Regards,