search loop

[Hawjeen]

Hi,

I need some help with creating a loop macro, hope someone would be so kind and help me

i need to search all cells in a range (b2:y200)
if a value (value would be a name - so text) is found, i would like to go to sheet ("data") find the value in range (B2:B100) and copy the cell to the right (Column A) and paste that value
instead of the name found initially.

and of course search for next value in range (b2:y200)

Does it make sense?

Regards
Hawjeen

 

[Fluff]

How about

Sub SearchLoop()

   Dim Cl As Range
   Dim Rng As Range
   Dim Qty As Long
   Dim i As Long
   Dim Dic As Object
   Dim ky As Variant
   
   Set Rng = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("B2:Y200")
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Sheets("Data").Range("B2:B100")
      If Not Dic.exists(Cl.Value) Then Dic.Add Cl.Value, Cl.Offset(, -1).Value
   Next Cl
   For Each ky In Dic.keys
      Qty = Application.CountIf(Rng, ky)
      If Qty > 0 Then
         For i = 1 To Qty
            Rng.Find(ky, , , xlWhole, , , False, , False) = Dic(ky)
         Next i
      End If
   Next ky
End Sub

Change value in red to suit

 

[Yongle]

@Fluff
I am not familiar with Dictionary in VBA
Are you using Dictionary because it allows VBA to return EVERY match rather than only the first match?

 

[Fluff]

@Yongle
The idea of the dictionary is to put the values from the data sheet into memory & then loop through the dictionary to see if any of those values occur on the other sheet. It's a faster way of working than continually accessing the sheet.
For more info on Dictionaries look here https://excelmacromastery.com/vba-dictionary/

 

[Yongle]

@Fluff
Thanks - makes perfect sense. Lots more to learn always!

 

[Hawjeen]

Great!

If i want to modify it to paste it as a picture with a link, where do i need to change it?

 

[Fluff]

I'm afraid I've no idea.

 

[Hawjeen]

okay. but thx. for the initial macro, it worked as i needed>(