VBA Scripting Dictionary - Basic Code Just Starting Out

[OilEconomist]

Thanks in advance for any assistance provided by anyone.
I am new to Arrays and Scripting Dictionaries.
I would like to read the following values into a Scripting Dictionary and then run some code based on that data. If that data does not exist in my data set, I want to perform an operation.


Read the following into a dictionary

Stocks - Tool, Analysis - Get Data Macro Update - (Active).xlsm
	A
1	Region
2	Region 1
3	Region 2
4	Region 4
5	Region 6
6	Region 7
Data

If any of those values exists in my data set, where my data set is as follows

Stocks - Tool, Analysis - Get Data Macro Update - (Active).xlsm
	A
1	Region
2	Region 1
3	Region 2
4	Region 3
5	Region 4
6	Region 5
7	Region 6
8	Region 7
SA

I do not want to perform say an operation as such (sample code):

Option Explicit
'***************************************************************************************************************
Sub MACRO_MXX_Script_Dic_Test()



  '_______________________________________________________________________________________________________________
  'Turn off alerts, screen updates, and automatic calculation
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
            
            
  '_______________________________________________________________________________________________________________
  'Dimensioning

    'Dim
     Dim i As Long, j As Long
     Dim aDictionary As Object
     

    
  '_________________________________________________________________________________________________________________
  'Code -

    Set aDictionary = CreateObject("Scripting.Dictionary")
    
    With Sheets("Data")
        For j = 2 To 6
            aDictionary(j, 1) = .Range(j, 1).Value
        Next j
    End With
    
    
    With Sheets("SA")
        For i = 2 To 8
            'code - If (values does not exist in the Dictionary) then MsgBox .Range(i, 1)
        Next j
    End With
    

  '_________________________________________________________________________________________________________________
  'Turn on alerts and screen updates, and calculate
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Calculate


 '_________________________________________________________________________________________________________________
 '
 
End Sub

 

[rlv01]

You will save yourself a lot of time learning about scripting dictionaries by reading though the page below and trying some of the examples there.

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com

 

[OilEconomist]

You will save yourself a lot of time learning about scripting dictionaries by reading though the page below and trying some of the examples there.

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com

Thanks @rlv01. I did review that and other websites with similar info prior to starting this post. That's what I used for what I have thus far. Still looking for some additional guidance.

 

[Fluff]

How about

    Set aDictionary = CreateObject("Scripting.Dictionary")
    
    With Sheets("Data")
        For j = 2 To 6
            aDictionary(.Cells(j, 1).Value) = Empty
        Next j
    End With
    
    
    With Sheets("SA")
        For i = 2 To 8
            If Not aDictionary.Exists(.Cells(i, 1).Value) Then MsgBox .Cells(i, 1)
        Next j
    End With

 

[OilEconomist]

How about

    Set aDictionary = CreateObject("Scripting.Dictionary")
   
    With Sheets("Data")
        For j = 2 To 6
            aDictionary(.Cells(j, 1).Value) = Empty
        Next j
    End With
   
   
    With Sheets("SA")
        For i = 2 To 8
            If Not aDictionary.Exists(.Cells(i, 1).Value) Then MsgBox .Cells(i, 1)
        Next j
    End With

@Fluff Thanks! At a first quick check, it seems like it works. Once I get to look at it closer I will let you know and mark the solution. Just wanted to get back to you ASAP!
Thanks @Fluff for all the assistance you provide on this platform.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[OilEconomist]

Glad to help & thanks for the feedback.

As I am learning and if you have time to answer the following, if not, no worries.

The following line of code just stores all the values that I do not want included. You set it to be empty since the compare mode is going to be used?

aDictionary(.Cells(j, 1).Value) = Empty

So in the following line, the only thing that has to do with the dictionary is the:

aDictionary

The

.Cells(i, 1).Value

from

 If Not aDictionary.Exists(.Cells(i, 1).Value) Then MsgBox .Cells(i, 1)

is the value in the Sheets("SA"). It's just saying if .Cells(i, 1).Value from Sheet("SA") is not in the dictionary, then perform the operation?

Now if I wanted to move the following line of code to a function,

With Sheets("SA")
        For i = 2 To 8
            If Not aDictionary.Exists(.Cells(i, 1).Value) Then MsgBox .Cells(i, 1)
        Next j
End With

set it up as follows?

Function TestDic(aDictionary As Dictionary) as Variant

Dim i as Long

        For i = 2 To 8
            If Not aDictionary.Exists(.Cells(i, 1).Value) Then MsgBox .Cells(i, 1)
        Next i
End Function

 

[Fluff]

The following line of code just stores all the values that I do not want included.

I have no idea what you mean by that.

That line adds all the data to the dictionary as the key & leaves the Item empty.
This aDictionary is not a line of code, so once again I have no idea what you mean.

I would suggest that you follow the link from post#2 & thoroughly read it.