TheGrandPooba
New Member
- Joined
- Jul 1, 2022
- Messages
- 12
- Office Version
- 365
- Platform
- 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:
Here is the macro:
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!
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:
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!