Find with loop

[UnsolvedCoding]

I am working on a series of worksheets that all contain the same information in different locations on a tab and there are over 30 different layouts. The only thing consistant is that certain words populate more often than other words.

What I would like to do is use a find to locate words in a workbook and if they are found change a counter up by one, if not found go to the next word in the list.

Normall the code for a find is cells.find(What:="Burgers"

I used the macro recorder and came up with this -

Dim sWord as string
sWord = "Field Name"

Cells.Find(What:=sWord, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

Does anyone know a better way to check a workbook to find a word and how to change a counter if the word is found?

I haven't been having very much luck.

 

[SkipVought]

hi,

    Dim sWord As String, rFound As Range, iCnt As Integer, sPrev As String
    
    sWord = "Field Name"

    Set rFound = Cells.Find(What:=sWord, After:=[A1], LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)


    If Not rFound Is Nothing Then
        Do
            sPrev = rFound.Address
            iCnt = iCnt + 1
            
            Set rFound = Cells.FindNext(After:=rFound)
            
        Loop Until sPrev < rFound.Address
        
    End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops

    Dim sWord As String, rFound As Range, iCnt As Integer, lPrev As Long
    
    sWord = "Field Name"

    Set rFound = Cells.Find(What:=sWord, After:=[A1], LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)


    If Not rFound Is Nothing Then
        Do
            lPrev = rFound.Row
            iCnt = iCnt + 1
            
            Set rFound = Cells.FindNext(After:=rFound)
            
        Loop Until lPrev > rFound.Row
        
    End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Do I have to loop through the worksheets or can I do the workbook at one time?

 

[SkipVought]

all sheets
[/code]
Dim sWord As String, rFound As Range, iCnt As Integer, lPrev As Long
Dim ws as worksheet

sWord = "Field Name"

with Sheets(1)
Set rFound = [highlight].[/highlight]Cells.Find(What:=sWord, After:=[highlight].[/highlight][A1], LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
end with

for each ws in worksheeets

If Not rFound Is Nothing Then
Do
lPrev = rFound.Row
iCnt = iCnt + 1
Set rFound = ws.Cells.FindNext(After:=rFound)

Loop Until lPrev > rFound.Row

End If
next
[/code]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}