scripting.dictionary

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
467
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
VBA Code:
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Which columns on sheet2 should the values go in?
 
Upvote 0
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)
 
Upvote 0
Ok, how about
VBA Code:
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
 
Upvote 0
Solution
Thanks Fluff
That's great
Not quite working, this line of code is not working
VBA Code:
Cl.Offset(, 8).Value = Dic(Cl.Value)(0)
it's not entering any values
 
Upvote 0
Do you have any values in col F of sheet 2?
 
Upvote 0
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
 
Upvote 0
Glad it's sorted & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,247
Messages
6,171,004
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top