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

TheGrandPooba

New Member
Joined
Jul 1, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1
2"Next order" button here
3
4
5Oil R/S Lookup 
6
7Order#266584OrderDate4/15/22
8CustomerCONAGRA BRANDS INC Req. Ship6/30/22
9Ship ToMSI EXPRESS, INC. Carrier-Carrier Not Found-
10PO#3884852-10-1
11
12Item #Item DescriptionQuantityPkng SizeTotal LBItem Instructions
1343081100# TOTE R&S 24/64 30110033000Salmonella 2x 375g each
14
15
16
17
18Order Instructions:List PO#
19Extra Order Instructions:None
20List of Orders
21266835
22266584
23266585
24266971
25266846
26266788
27266790
28266789
29
30
31
32
Dry-Orders
Cell Formulas
RangeFormula
G5:G6G5=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))
H7H7=XLOOKUP($D$7, OImport[Order'#],OImport[OrderDate],Not Found,0,1)
H8H8=XLOOKUP($D$7, OImport[Order'#],OImport[ShipDate],Not Found,0,1)
H9H9=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))
D8D8=XLOOKUP($D$7, OImport[Order'#],OImport[Bill To Name],Not Found,0,1)
D9D9=XLOOKUP($D$7, OImport[Order'#],OImport[ShipTo],Not Found,0,1)
D10D10=XLOOKUP($D$7, OImport[Order'#],OImport[PO'#],Not Found,0,1)
C13:F13C13=FILTER(FILTER(OImport[[Item'#]:[Pk Wght]],(OImport[Type]="Dry")*(OImport[Order'#]=D7),""),{1,1,0,0,0,1,0,0,0,0,1},"")
G13G13=IF($E13*$F13=0,"",$E13*$F13)
H13H13=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"))
E18E18=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"))
E19E19=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:A28A21=UNIQUE(FILTER('Dry-Sched'!$B$23:$B$67,'Dry-Sched'!$B$23:$B$67>1,""),FALSE,FALSE)
Dynamic array formulas.


Here is the macro:
VBA Code:
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
@TheGrandPooba Does something like below help?

VBA Code:
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
 
Upvote 0
VBA Code:
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
 
Upvote 0
Solution
@TheGrandPooba Does something like below help?

VBA Code:
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!
 
Upvote 0

Forum statistics

Threads
1,223,887
Messages
6,175,199
Members
452,617
Latest member
Narendra Babu D

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