Find Function VBA

[hercules89]

Hi,
I have a spreadsheet and I am trying to write a macro that searches based on whether a value in column I on sheet 1 appears in any cell in column G on sheet 2 and loop through al visible rows on a filtered range.
- if value in “12345” in cell I2 matches any cell between G3 and then last cell in column G on sheet 2 (number of cells in column G may change daily) then populate K2 with ‘accepted’
- If value doesn’t appear then populate cell K2 with ‘not accepted’
- I need this to perform this search on each filtered value cell in column I

I don’t have a clue where to start with the code for this so sorry for the lack of code, hopefully some expert can help!

Thanks,
Herc

 

[NoSparks]

Google is your friend:

https://www.rondebruin.nl/win/s9/win006.htm
https://docs.microsoft.com/en-us/office/vba/api/excel.range.find
https://excelmacromastery.com/excel-vba-find/

 

[Fluff]

How about

Sub hercules89()
   Dim Cl As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("Sheet2")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws2.Range("G3", Ws2.Range("G" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      For Each Cl In Ws1.Range("I2", Ws1.Range("I" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
         If .Exists(Cl.Value) Then Cl.Offset(, 2).Value = "Accepted" Else Cl.Offset(, 2).Value = "Not Accepted"
      Next Cl
   End With
End Sub