VBA - Searching and Returning IF Multiple Query has been met

[Razor_Rob]

Hi I've got a VBA code that uses the Dictionary Object
wsAvailabilityCodes - worksheet where it search for certain records
wsDest - destination worksheet where the data gets copied into

wsAvailabilityCodes has multiple columns (table below has limited number of columns)

A	B	C	D	I	....	K	P	Q	R
1234	CPC123456	Product 1	New York				User	Office	G2
5678	UEE123456	Product 2	Los Angeles				User	Office	G2
9112	MEM12345-TEST	Product 3	Denver				Standard	Work	G6

My VBA below looks for the value in Column B then returns the value in Column A, I & K.
Now what I want to add is add multiple query like if value of B and = "User" from P and also = "Office" from Q and also = "G2" from R
then return the Value of A, I and K to wsDest
Thanks in advance

    Dim Col2 As Range
    Dim Dict1 As Object
    ' Dim Dict2 As Object  <-- has been removed (not required)
    Dim Col3 As Range
    Dim Dict3 As Object
    Dim Dict4 As Object
        
    Set Dict1 = CreateObject("scripting.dictionary")
    'Set Dict2 = CreateObject("scripting.dictionary")
    Set Dict3 = CreateObject("scripting.dictionary")
    Set Dict4 = CreateObject("scripting.dictionary")
                
    With wsAvailabilityCodes
        For Each Col2 In .Range("B5", .Range("B" & Rows.Count).End(xlUp))
            Dict1(Col2.Value) = Col2.Value            
            Dict3(Col2.Value) = Col2.Offset(, 7).Value
            Dict4(Col2.Value) = Col2.Offset(, 8).Value
        Next Col2
    End With
    With wsDest
        For Each Col2 In .Range("Y3", .Range("Y" & Rows.Count).End(xlUp))
            If Dict1.exists(Col2.Value) Then Col2.Offset(, 1).Value = Dict1(Col2.Value)            
            If Dict3.exists(Col2.Value) Then Col2.Offset(, 2).Value = Dict3(Col2.Value)
            If Dict4.exists(Col2.Value) Then Col2.Offset(, 4).Value = Dict4(Col2.Value)
        Next Col2
    End With

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.
As you have probably gathered being able to see a representive sample of data that lets us see patterns and data types (numbers, text, numbers stored as text) makes a big difference in being able to come up with possible solutions. Even column headings are useful and allow us to tailor the solution more specifically to your needs.
It is quite common to get an oversimplified sample that loses those aspects.
Appreciate you being so responsive in answering questions yesterday.

 

[Razor_Rob]

Thanks again, I will make sure what you said above pointers for future questions/topics.

Thanks so much, Im really appreciative that you've taken the time and patience to help me out.
Cheers