Need to go through a range with as little macro as possible

[Bill_Biggs]

I have a psuedo code here I am trying to figure out an actual macro for. The macro needs to go through about a hundred words in a range, locate them, and perform a function on the first row after the term

Then it needs to do it over and over again, changing the word it is looking for each time.


For i = 1 To Range.Cells.Count

Cells.Find(What:="FREQ_High", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
Perform procedure
next i

End

What I really need help with is where to put the range and how so that each time the macro loops, it gathers the next term in the range? By the way, the range is located on Sheet 1 A1:A100.

Thanks

 

[VoG]

Will the search term occur only once (or do you need to find it only once)?

 

[VoG]

If it is only once then

Sub test3()
Dim LR As Long, i As Long, Found As Range
LR = Range("X" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Set Found = Columns(1).Find(What:=Range("X" & i).Value, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)
    If Not Found Is Nothing Then
        With Found.Offset(1, 0)
            'do your stuff
        End With
    End If
Next i
End Sub

This assumes that the list of search terms is in column X of the same sheet.

If you need to find the same term multiple times it becomes more complicated

 

[Bill_Biggs]

I apologize for the delayed response. Three day weekend over here. Thank you for the help thus far but the "X" term is eluding me. The macro is supposed to gets the first term it is to look up and then goes to the next page and finds it and then returns. But when it highlights the "Set Found" command, it says that the named argument can't be found. As far as I can tell though, it only looks at the first cell of the next page. So if cell A1 of the first page says "dog" and Cell A1 of the second page says "Gopher", when I place the cursor over <font face=Courier New>    <SPAN style="color:#00007F">Set</SPAN> Found = Columns(1).Find(What:=Range("A" & i).Value, LookIn:=xlValues, LookAt:= _</FONT> it shows the word "Gopher" in the bubble that appears over the cursor, but does not continue to look through the rest of the terms on the page. I suspect I what is really eluding me is my grasp of how the macro is supposed to work. Do you mind telling me how far off I am?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test3()<br><br><SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Found <SPAN style="color:#00007F">As</SPAN> Range<br>LR = Range("A" & Rows.Count).End(xlUp).Row<br><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>    Sheets("Sheet2").Select<br>    <SPAN style="color:#00007F">Set</SPAN> Found = Columns(1).Find(What:=Range("A" & i).Value, LookIn:=xlValues, LookAt:= _<br>    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _<br>    , SearchFormat:=False)<br>    <br>    <SPAN style="color:#007F00">'Carry out procedure</SPAN><br>        <br>   <br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Thanks,

 

[VoG]

I think that we neet to qualify which sheet contains the list of search terms (I am assuming Sheet1) and which has the values to search for (Sheet2). Try

Sub test3()
Dim LR As Long, i As Long, Found As Range
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        
        Set Found = Sheets("Sheet2").Columns(1).Find(What:=.Range("A" & i).Value, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
        
        'Carry out procedure
            
      
    Next i
End With
End Sub

 

[Bill_Biggs]

Thank you, sir!