VBA Find variable in another set of variables

CharlieRog

New Member
Joined
Sep 13, 2019
Messages
14
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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?
 
Upvote 0
Re: VBA Find varibale in another set of variables

I have this list (list 1)...

[TABLE="width: 77"]
<colgroup><col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <tbody>[TR]
[TD="width: 102, bgcolor: transparent"]NMABAK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NAAEFW[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMABLS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NAAEIW[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIJW[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NAABZC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMCBHO[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAAIY[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAACH[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 58"]
<colgroup><col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <tbody>[TR]
[TD="width: 77, bgcolor: transparent"]NMAIJT[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIJU[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIJV[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIJW[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIJX[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIJY[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIJZ[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIKA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIKB[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NMAIKC[/TD]
[/TR]
</tbody>[/TABLE]

until it finds a match. Once it finds a match I need it to copy the cell next to it
 
Upvote 0
Re: VBA Find varibale in another set of variables

Where exactly are the two lists, in terms of sheets & ranges?
 
Upvote 0
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
 
Upvote 0
Re: VBA Find varibale in another set of variables

Ok, how about
Code:
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
 
Upvote 0
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)
 
Upvote 0
Re: VBA Find varibale in another set of variables

How about
Code:
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
 
Upvote 0
Re: VBA Find varibale in another set of variables

You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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