if item exists in array of scripting dictionary

[tomleitch]

Hi all,

I have some code that makes a scripting dictionary but I'm having a bit of a problem with trying to get it to do what I want.

Here's my code that's throwing up an error:

   With CreateObject("scripting.dictionary")
  
 Set ws2 = Sheets("UPDATE TOOL3")
  
   For Each cl In ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp))
        
        If Not .exists(cl.Value) Then
                .Add cl.Value, Array(cl.Offset(, 1).Value, cl.Offset(, 2).Value, cl.Offset(, 3).Value)
                
                
           End If
          
          
            Next cl
 
                Set ws1 = Sheets("MAINT")


                For Each cl2 In ws1.Range("A10", ws1.Range("A" & Rows.Count).End(xlUp))
                
                If .exists.Item(cl.Value)(0)) Then
                
                etc...........

It's this bit that is giving me the trouble " If .exists.Item(cl.Value)(0)) Then"


Any help much appreciated,

Thanks
Tom

 

[tomleitch]

I've probably not explained this one very well....

What I want to do is go down from A10 to the end of my sheet and see if that item is present as item 0 in the array that I've made


Thanks
Tom

 

[Eric W]

The .exists method tells you if a key exists in your dictionary. It does not tell you if a value exists the in array you've saved as the dictionary item. To do that, you have several options. You could save all your values as keys

.Add cl.Value, 0
.Add cl.Offset(, 1).Value, 1
.Add cl.Offset(, 2).Value, 2
.Add cl.Offset(, 3).Value), 3

You have to worry about duplicates though. If you don't care about duplicates, or keeping track of the column that the values came from, then you could add a

On Error Resume Next

line before that. If you want to maintain the structure you've already got, you might need to do something like:

For Each cl2 In ws1.Range("A10", ws1.Range("A" & Rows.Count).End(xlUp))              
    For each X in .keys
        For i = 0 to 3
            If cl2.Value = MyDict(X)(i) Then Goto FoundOne:
        Next i
    Next X
' Did not find a match
   . . .
FoundOne:

A bit more awkward. Finding an appropriate data structure is a major skill in programming.


Edit: Having read your latest post, you could do something like:

.Add cl.Value & "|0", 0
.Add cl.Offset(, 1).Value & "|1", 0
.Add cl.Offset(, 2).Value & "|2", 0
.Add cl.Offset(, 3).Value) & "|3" , 0

and check for existence with:

If .exists.Item(cl.Value & "|0") Then

 

[tomleitch]

Eric, thanks very much for your reply.

So I originally had a set of data that contained duplicate numbers (sometimes many) in the column that I wanted to reference.

I thought that I couldn't add these to the dictionary - so I made a way around it by adding a first column which just counted sequentially from 1 to 40,000 so they all had unique numbers in .

So basically column B is the numbers that I want to match on the other worksheet - and then update other values from that array.


Not very experienced in this at all so might not be going about it in the right way..... so any more pointers much appreciated.


I wondered if I could use something like this...

   With CreateObject("scripting.dictionary")
  
 Set ws2 = Sheets("UPDATE TOOL3")
  
   For Each cl In ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp))
        
        If Not .exists(cl.Value) Then
                .Add cl.Value, Array(cl.Offset(, 1).Value, cl.Offset(, 2).Value, cl.Offset(, 3).Value)
                
                
           End If
          
          
            Next cl
 
                Set ws1 = Sheets("MAINT")


                For Each cl2 In ws1.Range("A10", ws1.Range("A" & Rows.Count).End(xlUp))
                
                
                  For Each Ky In .keys(0)
        
                        
1               If .exists(cl2.Value) Then.............etc

Thanks
Tom

 

[Eric W]

Well, there's a LOT we could talk about here! First, looking at your latest code, I assume that column A is the value you want to match, not the 1-40,000 value. If so, then you can remove the "For Each Ky" line altogether, and it should work OK. (And you don't need the (0) on that line anyway.)

Next, you might want to consider performance. Reading/writing to the worksheet is one of the slowest things you can do. And you're potentially doing it 4 times on 1 line, in a loop of 40,000, for a total of 160,000. But you can read a whole range at one time, saving a lot of time. It's usually much faster to read the entire range into an internal array, then refer to that. For example:

Sub test1()

    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
  
    updarr = ws1.Range("A1:D" & ws1.Cells(Rows.Count, "A").End(xlUp).Row).Value
    chkarr = ws2.Range("A1:D" & ws2.Cells(Rows.Count, "A").End(xlUp).Row).Value
  
    On Error GoTo NotFound:
    For i = 2 To UBound(chkarr)
        x = WorksheetFunction.VLookup(chkarr(i, 1), updarr, 2, 0)
' Do something
NextOne:
    Next i
      
    Exit Sub

NotFound:
    ' Do something
    Resume NextOne:
      
End Sub

This reads both your reference table and your lookup table into arrays. Then it uses the worksheet function VLOOKUP on the table, not the sheet, to find your value. Duplicate values won't matter with the 0 parameter on VLookup. I used the On Error line to check for not found values, but there are several ways to handle that. For performance, you still might want a dictionary, but it would take some experimentation to figure out what is fastest.

Hopefully this gives you some ideas!

 

[tomleitch]

Thanks for your time Eric, very helpful indeed.

 

[tomleitch]

Eric any chance you could help a little more with this..... sorry to be having to be so basic with it but I'm not very experienced with writing code.

What I want to do is match the row where it finds the match x

And then update column values based on that row with data from updarr columns B, C and D.

Would you mind giving a little bit more steer.


Thanks again
Tom

 

[DanteAmor]

You could explain with examples what you need. Because you mention the code and something very basic of what you need.

But you could put about 10 records of what you have on each sheet (replace confidential information with generic data), and explain what you have on each sheet and what you expect from the result and where you want the result.

To Upload an excel range use this tool:
XL2BB - Excel Range to BBCode

 

[tomleitch]

Ok I will try and explain a bit better and show what I have got so far:

I couldn't really work out how to work with the example that Eric suggested using vlookup so:


Sheet 1 has columns A B and C that I want data from

Column B is all unique numbers (along with some empty cells inbetween - but I don't want the emptys anyway).... so I am using column B to build a dictionary with array items in

Sheet 2 has data that I want to update/do stuff with

    With CreateObject("scripting.dictionary")
  
   Set ws2 = Sheets("UPDATE TOOL2")
  
Lrow = Sheets("UPDATE TOOL2").Cells(Rows.Count, 1).End(xlUp).Row

 
   For Each cl In ws2.Range("B2", ws2.Range("B" & Lrow))
        
        If Not .exists(cl.Value) Then
                .Add cl.Value, Array(cl.Offset(, -1).Value, cl.Offset(, 1).Value)

                
           End If

So that builds my dictionary and what I want to do is match things from the offset(,-1) array - .Item(0) with the same number item in column A on sheet 2

So I want to go through all the item(0) entries and when any match with Sheet2 column A then: do stuff


Does that make it a bit clearer?

I might be going down completely the wrong way with this!


Thanks
Tom

 

[DanteAmor]

That is absolutely not clear, it would help a lot if you give examples of your sheet1 and the sheet2.
Forget the code a bit.
Give the examples and explain what you want as a result.
Also write the results you expect.