scripting.dictionary

[Bagsy]

I'm hoping that somebody may be able to help me
I have data in columns E, F & G
I am attempting to modify some code that Fluff has written
Is the Key for the dictionary using the values in column E and the line of code below is offsetting to next column and adding the value that is in that cell to the dictionary

For Each Cl In .Range("E2", .Range("E" & Rows.Count).End(xlUp))
Dic(Cl.Value) = Array(Cl, Cl.Offset(, 1).Value)

If it is and I have data in columns E, F & G can I use column E as the dictionary key, offset and add the data from the next two columns (F & G) to the dictionary (which are dates) then add to sheet 1 offsets as below.
I have tried this code but obviously it doesn't work, but it’s an example of what I would like to achieve hopefully
Any help is always appreciated

Sub Test()
    Dim Cl As Range
    Dim Dic As Object

    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet2")
        For Each Cl In .Range("E2", .Range("E" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Array(Cl, Cl.Offset(, 1).Value)
            Dic(Cl.Value) = Array(Cl, Cl.Offset(, 2).Value)' not sure if this will work
        Next Cl
    End With
    With Sheets("Sheet1")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               Dic(Cl.Value) = Array(Cl, Cl.Offset(, 8).Value)' not sure if this will work
               Dic(Cl.Value) = Array(Cl, Cl.Offset(, 9).Value)' not sure if this will work
               
            End If
        Next Cl
    End With
End Sub

 

[Fluff]

Which columns on sheet2 should the values go in?

 

[Bagsy]

H Fluff
Thanks for your help
The data is on sheet2 in columns E,F & G, this is populated by some code I run which it gets from another spread sheet.
I want to offset and add columns F & G on sheet2 to the dictionary (if that is possible)
Then it would look for the appropriate value on sheet1 column A then offset from column A to columns I & J and enter the data from the dictionary (this is just modifying availability dates as these can change occasionally)

 

[Fluff]

Ok, how about

Sub Test()
    Dim Cl As Range
    Dim Dic As Object

    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet2")
        For Each Cl In .Range("E2", .Range("E" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Array(Cl.Offset(, 1).Value, Cl.Offset(, 2).Value)
        Next Cl
    End With
    With Sheets("Sheet1")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If Dic.Exists(Cl.Value) Then
               Cl.Offset(, 8).Value = Dic(Cl.Value)(0)
               Cl.Offset(, 9).Value = Dic(Cl.Value)(1)
            End If
        Next Cl
    End With
End Sub

 

[Bagsy]

Thanks Fluff
That's great
Not quite working, this line of code is not working

Cl.Offset(, 8).Value = Dic(Cl.Value)(0)

it's not entering any values

 

[Fluff]

Do you have any values in col F of sheet 2?

 

[Bagsy]

Hi Fluff
Just for testing
I had numbers 1 to 637 in column F and letters in column G
I have now put dates in and it is working just fine, not sure why that made any difference but sorry about that.
Thank you so much for your help

 

[Fluff]

Glad it's sorted & thanks for the feedback.