How to stop VBA from going to next value in list if blank

[TheGrandPooba]

Hi all, my first post was this morning so I'm still feeling fairly new to this but here it goes again-

I have a VBA code that changes cell D7 to the next item on the list (A21:A100). However, once it reaches the end, the "NextOrder" macro continues going to the next cell. How do I stop my macro from changing D7 to nothing if D7 is currently the last item on the list?

Here is the sample sheet:

FargoPlan.xlsm
	A	B	C	D	E	F	G	H
1
2							"Next order" button here
3
4
5			Oil R/S Lookup
6
7			Order#	266584			OrderDate	4/15/22
8			Customer	CONAGRA BRANDS INC			Req. Ship	6/30/22
9			Ship To	MSI EXPRESS, INC.			Carrier	-Carrier Not Found-
10			PO#	3884852-10-1
11
12			Item #	Item Description	Quantity	Pkng Size	Total LB	Item Instructions
13			4308	1100# TOTE R&S 24/64	30	1100	33000	Salmonella 2x 375g each
14
15
16
17
18				Order Instructions:	List PO#
19				Extra Order Instructions:	None
20	List of Orders
21	266835
22	266584
23	266585
24	266971
25	266846
26	266788
27	266790
28	266789
29
30
31
32
Dry-Orders

Cell Formulas
Range	Formula
G5:G6	G5	=IF(XLOOKUP(C5,Fargo!$L$3:$BJ$3,Fargo!$L$1:$BJ$1,"",0,1)=0," ",XLOOKUP(C5,Fargo!$L$3:$BJ$3,Fargo!$L$1:$BJ$1,"",0,1))
H7	H7	=XLOOKUP($D$7, OImport[Order'#],OImport[OrderDate],Not Found,0,1)
H8	H8	=XLOOKUP($D$7, OImport[Order'#],OImport[ShipDate],Not Found,0,1)
H9	H9	=IF(XLOOKUP($D$7, Summary!$A$8:$A$300,Summary!$N$8:$N$300)=0,"-Carrier Not Found-",XLOOKUP($D$7, Summary!$A$8:$A$300,Summary!$N$8:$N$300))
D8	D8	=XLOOKUP($D$7, OImport[Order'#],OImport[Bill To Name],Not Found,0,1)
D9	D9	=XLOOKUP($D$7, OImport[Order'#],OImport[ShipTo],Not Found,0,1)
D10	D10	=XLOOKUP($D$7, OImport[Order'#],OImport[PO'#],Not Found,0,1)
C13:F13	C13	=FILTER(FILTER(OImport[[Item'#]:[Pk Wght]],(OImport[Type]="Dry")*(OImport[Order'#]=D7),""),{1,1,0,0,0,1,0,0,0,0,1},"")
G13	G13	=IF($E13*$F13=0,"",$E13*$F13)
H13	H13	=IF(FILTER(CustInfo[Item Instructions 1],(CustInfo[Ship To]=$D$8)*(CustInfo[Item Number]=$C13),"None")=0,"None",FILTER(CustInfo[Item Instructions 1],(CustInfo[Ship To]=$D$8)*(CustInfo[Item Number]=$C13),"None"))
E18	E18	=IF(XLOOKUP($D$8,CustInfo[Ship To],CustInfo[Order instructions 1],"None")=0,"None",XLOOKUP($D$8,CustInfo[Ship To],CustInfo[Order instructions 1],"None"))
E19	E19	=IF(XLOOKUP($D$8,CustInfo[Ship To],CustInfo[Order Instructions 2],"None")=0,"None",XLOOKUP($D$8,CustInfo[Ship To],CustInfo[Order Instructions 2],"None"))
A21:A28	A21	=UNIQUE(FILTER('Dry-Sched'!$B$23:$B$67,'Dry-Sched'!$B$23:$B$67>1,""),FALSE,FALSE)
Dynamic array formulas.

Here is the macro:

Sub NextOrderDry()

Dim found As Range
Set found = Sheets("Dry-Orders").Rows("21:100").Find(What:=Sheets("Dry-Orders").Cells(7, 4).Value, LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
Else
Sheets("Dry-Orders").Cells(7, 4) = found.Offset(1).Value
End If

End Sub

If you were to press the button at the top of the sample (which doesn't show on XL2BB for some reason,) the current Order# '266584' would change to the next item on the list, '266585.' However, once I reach the end (266789) the button would keep moving D7 down the blank list.
I imagine its an easy fix to 'do nothing' when the Next Order button is pressed if the next value on the list is empty- but I can not seem to figure out the solution.

Let me know if you guys need anything else, I appreciate you all!

 

[Snakehips]

@TheGrandPooba Does something like below help?

Sub NextOrderDry()
Dim Sht As Worksheet
Dim found As Range
Dim myrng As Range

Set Sht = Sheets("Dry-Orders")
Set myrng = Sht.Range("D7")
'Maybe  exit if D7  is empty ????
'If myrng = "" Then Exit Sub  '??????  comment out or un comment if required

'Or maybe find the first value in list if D7 is empty ?????
If myrng = "" Then
    myrng = Sht.Range("A21")  '??????  comment out or un comment if required
    Exit Sub
End If


Set found = Sht.Rows("21:100").Find(What:=myrng.Value, LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
Exit Sub
Else
If found.Offset(1).Value = "" Then Exit Sub  'check if next in list is empty and if so retain previous in D7
myrng = found.Offset(1).Value
End If

End Sub

 

[Ottsel]

Option Explicit
Sub NextOrderDry()

    Dim found As Range
  
    Set found = Sheets("Dry-Orders").Rows("21:100").Find(What:=Sheets("Dry-Orders").Cells(7, 4).Value, LookIn:=xlValues, LookAt:=xlWhole)
        If found.Offset(1).Value = "" Then
            msgbox "You've reached the last order, which is order# " & Sheets("Dry-Orders").Range("D7").Value
            Exit Sub
    Else
        If IsNumeric(found) = True Then
            Sheets("Dry-Orders").Cells(7, 4) = found.Offset(1).Value
    End If
    End If
  
End Sub

 

[TheGrandPooba]

@TheGrandPooba Does something like below help?

Sub NextOrderDry()
Dim Sht As Worksheet
Dim found As Range
Dim myrng As Range

Set Sht = Sheets("Dry-Orders")
Set myrng = Sht.Range("D7")
'Maybe  exit if D7  is empty ????
'If myrng = "" Then Exit Sub  '??????  comment out or un comment if required

'Or maybe find the first value in list if D7 is empty ?????
If myrng = "" Then
    myrng = Sht.Range("A21")  '??????  comment out or un comment if required
    Exit Sub
End If


Set found = Sht.Rows("21:100").Find(What:=myrng.Value, LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
Exit Sub
Else
If found.Offset(1).Value = "" Then Exit Sub  'check if next in list is empty and if so retain previous in D7
myrng = found.Offset(1).Value
End If

End Sub

This worked perfectly! I also have a 'Previous Order' button where i simply changed found.Offset(1).Value to found.Offset(-1).Value and your macro worked perfectly for both buttons! Thanks!