FindNext and Do loop problem

L

LMjdm

New Member
Joined
Oct 20, 2014
Messages
38
Code: 
Do
 
    
 
    Set dayPlan = rowA.FindNext(After:=dayPlan)
 
        If Not dayPlan Is Nothing Then
 
            If dayCheck.Address = dayPlan.Address Then
    
                Exit Do
    
            End If
            
        Else
        
            Exit Do
        
        End If
        
    offsetOfToday = dayPlan.Offset(0, 3)
 
    offsetOfDue = dayPlan.Offset(0, 4)
 Set asOf = offsetCol.Find(What:=offsetOfToday, LookIn:=xlValues, _
                         LookAt:=xlWhole, SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
                         
 Set dueDay = offsetCol.Find(What:=offsetOfDue, LookIn:=xlValues, _
                         LookAt:=xlWhole, SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    Letters = Split(Replace(dayPlan.Offset(0, 1).Value, " ", ""), ",")
    
    For X = 0 To UBound(Letters)
        Select Case Letters(X)
  
            Case "C"
            
                'bizRhyth.Range("g9").Offset(testestest, 0) = dayPlan.Offset(0, 2) these statements are used for testing box
                
                Application.ScreenUpdating = False
                message = dayPlan.Offset(0, 2)
                Set lync = Messenger.InstantMessage(CAMx)
                lync.SendText (message)
                
                message = dayPlan.Offset(0, 2)
                lync.SendText (message)
                
                If dayPlan.Offset(0, 3) = 0 Then
                    message = "As of: Today"
                
                ElseIf dayPlan.Offset(0, 3) = -1 Then
                
                    message = "As of: Yesterday"
                
                Else
                
                    message = "As of: " & asOf
                
                End If
                
                lync.SendText (message)
                
                If dayPlan.Offset(0, 4) = 1 Then
                
                    message = "Due: Close of business"
                    
                ElseIf dayPlan.Offset(0, 4) = 2 Then
                
                    message = "Due: Tomorrow"
                    
                Else
                
                    message = "Due: " & dueDay
                
                End If
                
                lync.SendText (message)
                
                On Error Resume Next
                lync.Close
                Application.ScreenUpdating = True
                
            Case "F"
            
                'bizRhyth.Range("H9").Offset(testestest, 0) = dayPlan.Offset(0, 2)
                
                Application.ScreenUpdating = False
                message = dayPlan.Offset(0, 2)
                Set lync = Messenger.InstantMessage(FinPMx)
                lync.SendText (message)
                
                message = dayPlan.Offset(0, 2)
                lync.SendText (message)
                
                If dayPlan.Offset(0, 3) = 0 Then
                    message = "As of: Today"
                
                ElseIf dayPlan.Offset(0, 3) = -1 Then
                
                    message = "As of: Yesterday"
                
                Else
                
                    message = "As of: " & asOf
                
                End If
                
                lync.SendText (message)
                
                If dayPlan.Offset(0, 4) = 1 Then
                
                    message = "Due: Close of business"
                    
                ElseIf dayPlan.Offset(0, 4) = 2 Then
                
                    message = "Due: Tomorrow"
                    
                Else
                
                    message = "Due: " & dueDay
                
                End If
                
                lync.SendText (message)
                
                On Error Resume Next
                lync.Close
                Application.ScreenUpdating = True
            
            Case "P"
            
                'bizRhyth.Range("I9").Offset(testestest, 0) = dayPlan.Offset(0, 2)
                
                Application.ScreenUpdating = False
                message = dayPlan.Offset(0, 2)
                Set lync = Messenger.InstantMessage(Planningx)
                lync.SendText (message)
                
                message = dayPlan.Offset(0, 2)
                lync.SendText (message)
                
                If dayPlan.Offset(0, 3) = 0 Then
                    message = "As of: Today"
                
                ElseIf dayPlan.Offset(0, 3) = -1 Then
                
                    message = "As of: Yesterday"
                
                Else
                
                    message = "As of: " & asOf
                
                End If
                
                lync.SendText (message)
                
                If dayPlan.Offset(0, 4) = 1 Then
                
                    message = "Due: Close of business"
                    
                ElseIf dayPlan.Offset(0, 4) = 2 Then
                
                    message = "Due: Tomorrow"
                    
                Else
                
                    message = "Due: " & dueDay
                
                End If
                
                lync.SendText (message)
                
                On Error Resume Next
                lync.Close
                Application.ScreenUpdating = True
                
            Case "S"
            
                'bizRhyth.Range("J9").Offset(testestest, 0) = dayPlan.Offset(0, 2)
                
                Application.ScreenUpdating = False
                message = dayPlan.Offset(0, 2)
                Set lync = Messenger.InstantMessage(Subx)
                lync.SendText (message)
                
                message = dayPlan.Offset(0, 2)
                lync.SendText (message)
                
                If dayPlan.Offset(0, 3) = 0 Then
                    message = "As of: Today"
                
                ElseIf dayPlan.Offset(0, 3) = -1 Then
                
                    message = "As of: Yesterday"
                
                Else
                
                    message = "As of: " & asOf
                
                End If
                
                lync.SendText (message)
                
                If dayPlan.Offset(0, 4) = 1 Then
                
                    message = "Due: Close of business"
                    
                ElseIf dayPlan.Offset(0, 4) = 2 Then
                
                    message = "Due: Tomorrow"
                    
                Else
                
                    message = "Due: " & dueDay
                
                End If
                
                lync.SendText (message)
                
                On Error Resume Next
                lync.Close
                Application.ScreenUpdating = True
                
            Case "0"
            
                'bizRhyth.Range("K9").Offset(testestest, 0) = dayPlan.Offset(0, 2)
                
                Application.ScreenUpdating = False
                message = dayPlan.Offset(0, 2)
                Set lync = Messenger.InstantMessage(FinOx)
                lync.SendText (message)
                
                message = dayPlan.Offset(0, 2)
                lync.SendText (message)
                
                If dayPlan.Offset(0, 3) = 0 Then
                    message = "As of: Today"
                
                ElseIf dayPlan.Offset(0, 3) = -1 Then
                
                    message = "As of: Yesterday"
                
                Else
                
                    message = "As of: " & asOf
                
                End If
                
                lync.SendText (message)
                
                If dayPlan.Offset(0, 4) = 1 Then
                
                    message = "Due: Close of business"
                    
                ElseIf dayPlan.Offset(0, 4) = 2 Then
                
                    message = "Due: Tomorrow"
                    
                Else
                
                    message = "Due: " & dueDay
                
                End If
                
                lync.SendText (message)
                
                On Error Resume Next
                lync.Close
                Application.ScreenUpdating = True
            
        End Select
  
    Next
 
 Loop
All my code works, there are a lot of variables declared before the do loop, but I wanted to keep it simple. The ONLY problem I see at this moment is that the find next returns nothing when it should return a value. It is looking in the right spot and the .find function worked fine. Its simply the findnext that is not working.... Thanks in advance
 
Sort by date Sort by votes
It's hard to figure out without knowing what you want to achieve, but I think you want something like this structure.

Code: 
[COLOR=darkblue]Set[/COLOR] dayCheck = dayPlan
    
[COLOR=darkblue]Do[/COLOR]
 
    offsetOfToday = dayPlan.Offset(0, 3)
    
    offsetOfDue = dayPlan.Offset(0, 4)   
        
    [COLOR=green]'[/COLOR]
    [COLOR=green]'[/COLOR]
    [COLOR=green]'[/COLOR]
    [COLOR=green]'[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] dayPlan = rowA.FindNext(After:=dayPlan)
    
[COLOR=darkblue]Loop[/COLOR] [COLOR=darkblue]While[/COLOR] [COLOR=darkblue]Not[/COLOR] dayCheck [COLOR=darkblue]Is[/COLOR] dayPlan
 
Upvote 0
Im basically trying to make a loop that will check a certain cell for letters. If it contains one or more of the letters it will execute messages(lync IM) to certain groups based on the letters. The identifier is the day in a fiscal month. So if its fiscal day 1, then it will find all instances of it and find the relevant information next to "day 1" only problem is I run the same code in the loop (just the select case part basically) and I use the .find function and it works but the second it gets the to the findNext function as seen above. It starts returning the value nothing when it should return the next day 1. rowA is defined correctly as a row on another spreadsheet because like I said it works with the .find command fine.
 
Last edited:
Upvote 0
The FindNext command uses the argument settings of the last used Find command. Your code is finding other matches (asOf and dueDay). So FindNext is finding the next dueDay

Replace the FindNext command with a Find command and qualify all the necessary arguments including After:=dayplan.
 
Last edited:
Upvote 0
So would this work if I used
Code: 
Set dayPlan = rowA.Find(After:=dayPlan)
this instead of
Code: 
Set dayPlan = rowA.FindNext(After:=dayPlan)
this???
 
Upvote 0
You would need the What argument as well. And any of the other arguments that are changed.

Set dayPlan = rowA.Find(What:=????, After:=dayPlan)

It's probably best just to duplicate your original dayPlan Find and change just the After:=dayPlan.
 
Upvote 0
im confused at what the After:=dayPlan actually does? but heres my original code
Code: 
Set dayPlan = rowA.Find(What:=fiscalDayNum, LookIn:=xlValues, _
                         LookAt:=xlWhole, SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
and this would be the new code???
Code: 
Set dayPlan = rowA.Find(What:=fiscalDayNum, LookIn:=xlValues, _
                         LookAt:=xlWhole, SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False, After:=dayPlan)[/
let me know if that works
 
Upvote 0
The first one would be for the first dayPlan find.

The second one would be for the FindNext-type dayPlan finds

Highlight the Find keyword in the VBA code and press F1 for help and an argument description.

After - Optional Variant. The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell. If you don't specify this argument, the search starts after the cell in the upper-left corner of the range.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Finding the problem with the code that gives the message "The picture is too large and will be truncated" ...
2
Replies
14
Views
339
NdNoviceHlp
NdNoviceHlp
I
  • Solved
Remove value from drop down list then enters wrong value in worksheet cell
Replies
2
Views
102
ipbr21054
I
I
  • Question Question
Advice for Could Not Find The Specific Object message
2
Replies
12
Views
244
ipbr21054
I
G
  • Solved
Skip Row When Column Word Meet
2
Replies
14
Views
471
Peter_SSs
Peter_SSs
G
  • Solved
Button for a For Loop
Replies
3
Views
168
Joe4
Joe4

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top