Having issues with .FindNext

lockarde

Board Regular
Joined
Oct 23, 2016
Messages
63
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:
VBA Code:
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,673
Office Version
2019
Platform
Windows
Hi,
try modifying your code as follows as see if resolves your issue

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
FindNext will repeat the last Find, so these lines
VBA Code:
        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
VBA Code:
 Set jobfind = Sheets(x).Range("A1:A250").Find("Item 1", lookat:=xlWhole, LookIn:=xlFormulas)
 

lockarde

Board Regular
Joined
Oct 23, 2016
Messages
63
FindNext will repeat the last Find, so these lines
VBA Code:
        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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
Missed that one :(
You will need to do a normal find, but start from the last found cell
 

lockarde

Board Regular
Joined
Oct 23, 2016
Messages
63
Missed that one :(
You will need to do a normal find, but start from the last found cell
So that'd look like?:
VBA Code:
Set jobfind = Sheets(x).Range("A1:A250").Find("Item1", lookat:=xlWhole, after:=jobfind.Address)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
You shouldn't need the .Address part
 

lockarde

Board Regular
Joined
Oct 23, 2016
Messages
63
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,095,173
Messages
5,442,828
Members
405,199
Latest member
mkarnout

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top