VBA Find variable in another set of variables

[CharlieRog]

I have 2 lists of data and I need excel to go through one list and search for the contents of each cell in the other list and when it finds it, to then copy the cell next to it.

I've started with...

Sheets("Example").Select

MyARange = "A1:A134933"
For each MyACell in Range(MyARange)
Sheets("Example").Select
If MyACell.Value =


But not sure how to define value as a multi value variable. E.g range from start to end of list

 

[Fluff]

Re: VBA Find varibale in another set of variables

Hi & welcome to MrExcel.
Where are your two lists & which list do you want to copy from?

 

[CharlieRog]

Re: VBA Find varibale in another set of variables

I have this list (list 1)...

NMABAK

NAAEFW

NMABLS

NAAEIW

NMAIJW

NAABZC

NMCBHO

NMAAIY

NMAACH

<colgroup><col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <tbody> </tbody>

which I need excel to tab through each one and search list 2...

NMAIJT

NMAIJU

NMAIJV

NMAIJW

NMAIJX

NMAIJY

NMAIJZ

NMAIKA

NMAIKB

NMAIKC

<colgroup><col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <tbody> </tbody>

until it finds a match. Once it finds a match I need it to copy the cell next to it

 

[Fluff]

Re: VBA Find varibale in another set of variables

Where exactly are the two lists, in terms of sheets & ranges?

 

[CharlieRog]

Re: VBA Find varibale in another set of variables

Ah sorry. Both are on the same sheet ("Example") and list 1 is in column I and list 2 is in column A

 

[Fluff]

Re: VBA Find varibale in another set of variables

Ok, how about

Sub CharlieRog()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 1)).Value2
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         .Item(Ary(i, 1)) = Ary(i, 2)
      Next i
      Ary = Range("I1", Range("I" & Rows.Count).End(xlUp).Offset(, 1)).Value2
      For i = 1 To UBound(Ary)
         If .exists(Ary(i, 1)) Then Ary(i, 2) = .Item(Ary(i, 1))
      Next i
   End With
   Range("I1").Resize(UBound(Ary), 2).Value = Ary
End Sub

This will add the value from col B to col J where col I =col A

 

[CharlieRog]

Re: VBA Find varibale in another set of variables

Works Perfectly Thanks!!

Would it be possible to also get it to copy contents of Column F when it finds a match and paste it next to the data it has already pasted (Column K)? Column F is 5 columns away from list 2 (column A)

 

[Fluff]

Re: VBA Find varibale in another set of variables

How about

Sub CharlieRog()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 5)).Value2
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         .Item(Ary(i, 1)) = Array(Ary(i, 2), Ary(i, 6))
      Next i
      Ary = Range("I1", Range("I" & Rows.Count).End(xlUp).Offset(, 2)).Value2
      For i = 1 To UBound(Ary)
         If .exists(Ary(i, 1)) Then
            Ary(i, 2) = .Item(Ary(i, 1))(0)
            Ary(i, 3) = .Item(Ary(i, 1))(1)
         End If
      Next i
   End With
   Range("I1").Resize(UBound(Ary), 3).Value = Ary
End Sub

 

[CharlieRog]

Re: VBA Find varibale in another set of variables

Perfect! Life saver. Thanks!!

 

[Fluff]

Re: VBA Find varibale in another set of variables

You're welcome & thanks for the feedback