Having issues with .FindNext

[lockarde]

Hello all,

I have a macro that runs through a column looking for text - it successfully finds the first instance of the text, however, when I use the .FindNext, the range comes up empty, even though there are 7 more instances of the text. I've tried playing with the search parameters, re-entering the text to make sure the value in the cell is correct... I can't seem to get it to work! The idea here is I have monthly sheets tracking jobs, and I also have a calendar sheet that, once it completes "building" the calendar, searchs the monthly sheets for jobs that occurred during that month, then it puts job details on the calendar.

Item(s)
Item 1
Notes:
Notes:
Notes:
Notes:
Item(s)
Item 1
Notes:
Item(s)
Item 1
Notes:
Notes:
Notes:
Item(s)
Item 1
Notes:
Item(s)
Item 1
Notes:
Item(s)
Item 1
Notes:
Notes:
Item(s)
Item 1
Notes:
Item(s)
Item 1
Notes:

That's what the colum looks like, and my code is below:

For x = 8 To shtcnt
    With Sheets(x)
        xshtname = Sheets(x).Name
        Set lastjob = Sheets(x).Cells.Find(what:="Notes:", searchorder:=xlByColumns, searchdirection:=xlPrevious)
        lastrow = lastjob.Row + 1
                
        Set jobfind = Sheets(x).Range("A1:A250").Find("Item 1", lookat:=xlWhole, LookIn:=xlFormulas)
        first_jf_Address = jobfind.Address
        
        Set c = Sheets(x).Cells.Find("Req Date")
        Set fc = Sheets(x).Cells.Find("Location")
        Set cs = Sheets(x).Cells.Find("Crate Style")
    
        Do
              
            duedate = .Cells(jobfind.Row, c.Column).Value
            francode = .Cells(jobfind.Row, fc.Column).Value
            crtstyle = .Cells(jobfind.Row, cs.Column).Value
            jobdet = francode & " - " & crtstyle
            duemos = Month(duedate)
            dueyr = Year(duedate)
            dueday = day(duedate)
            
            If duemos = i And dueyr = yr Then
                Set duerng = Sheets("Calendar").Cells.Find(dueday, lookat:=xlWhole)
                Set duerng = duerng.Offset(1, 0)
                If Not duerng.Value2 = "" Then
                duerng.Value2 = duerng.Value2 & Chr(10) & Chr(10) & jobdet
                Else
                duerng.Value2 = jobdet
                End If
                'r = Sheets(x).Cells.FindNext(what:="Item 1", searchorder:=xlByColumns, searchdirection:=xlNext).Row
                
            Else
                
            End If
        Set jobfind = Sheets(x).Range("A1:A250").FindNext(jobfind)
'this is where the issue occurs, after this line, jobfind = Empty
        next_jf_Address = jobfind.Address
        Loop While jobfind.Address <> first_jf_Address
    End With

 

[dmt32]

Hi,
try modifying your code as follows as see if resolves your issue

Set jobfind = Sheets(x).Range("A1:A250").Find("Item 1", lookat:=xlWhole, LookIn:=xlFormulas)
    If Not jobfinf Is Nothing Then
        first_jf_Address = jobfind.Address
        
        Do
' rest of your code





            
            Set jobfind = Sheets(x).Range("A1:A250").FindNext(jobfind)
            If jobfind Is Nothing Then Exit Do
            
        Loop While jobfind.Address <> first_jf_Address
        
    Else
        
        MsgBox "Record not Found", 48, "Not Found"
    End If

Dave

 

[Fluff]

FindNext will repeat the last Find, so these lines

        Set c = Sheets(x).Cells.Find("Req Date")
        Set fc = Sheets(x).Cells.Find("Location")
        Set cs = Sheets(x).Cells.Find("Crate Style")

must go before

 Set jobfind = Sheets(x).Range("A1:A250").Find("Item 1", lookat:=xlWhole, LookIn:=xlFormulas)

 

[lockarde]

FindNext will repeat the last Find, so these lines

        Set c = Sheets(x).Cells.Find("Req Date")
        Set fc = Sheets(x).Cells.Find("Location")
        Set cs = Sheets(x).Cells.Find("Crate Style")

must go before

Set jobfind = Sheets(x).Range("A1:A250").Find("Item 1", lookat:=xlWhole, LookIn:=xlFormulas)

Ahhh, that makes sense! How would you rectify it since I use .Find to search for the duedate in the Calendar sheet (middle of Do Loop)?

Simply re-adding the jobfind line would just make the code always find the second instance of "Item 1" correct? I'm having a brain fart on how to adequately keep track :/

 

[Fluff]

Missed that one
You will need to do a normal find, but start from the last found cell

 

[lockarde]

Missed that one
You will need to do a normal find, but start from the last found cell

So that'd look like?:

Set jobfind = Sheets(x).Range("A1:A250").Find("Item1", lookat:=xlWhole, after:=jobfind.Address)

 

[Fluff]

You shouldn't need the .Address part

 

[lockarde]

You shouldn't need the .Address part

Perfect! It mostly works now, I just need to go through and make sure there is consistency between all my sheets and what not. Thanks for your help Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback