Hey everyone,
This is a basic math inventory and order tracker.
Each week 1 inventory is to be imputed, I want the code to repeat as necessary until the next inventory column is empty
Also to highlight Negative numbers in RED and Text in ORANGE. anywhere in the big selection within L2 to FW1001 [L2:FW1001]
I wrote the code,
I am a bit worried about efficiency.
Is it a good approach to have the calculations done with a loop and IF/ELSEIF
The product rows will go down to a maximum of 1000 so Row 1001
on the left of Column L will be each product that is being inventoried.
I did create a repeat of L in column K ( =L2 "all the way down" ) formula inside K just to make sure it doesn't calculate in column M since the first inventory is starting in Column L so there technically wont be any SOLD calculated in Column M for the first Run
I read something about using Case is better than multiple Ifs but I am not familiar with Case
If you have any input regarding efficiency please by all means, all input is welcome.
Each inventory takes up a sets of 4 Columns
As follows
user will write in 2 columns (INVENTAIRE and COMMANDE)
INVENTAIRE 1 (Code User WILL WRIGHT THE NUMBER OF STOCK COUNTED)
STOCK EPUISE 1 (WILL WRIGHT THE NUMBER OF STOCK SOLD)
COMMANDE 1 (User WRIGHT THE NUMBER OF STOCK ORDERED)
TOTAL APRES COMMANDE 1 (Code WILL WRIGHT THE NUMBER OF STOCK COUNTED + NUMBER ORDERED)
then 2 and so on. until 42 sets of 4 columns ( 42 inventory calculations)
I did also place a few fail safes in case user types text in the (INVENTAIRE or COMMANDE) columns and not to calculate text and numbers to avoid an error.
besides that here is the code.
Where I am mostly stuck is how to get to change the range (R1) [inventory 1] to R2 and R3 and so all the way to R42 on as the next inventories are imputed
I am trying to attach the spreadsheet to help visualise the code.
but I am not able to find the upload a file button as I am posting at the moment. I could send you a copy if you want if i dont get to upload it
This is a basic math inventory and order tracker.
Each week 1 inventory is to be imputed, I want the code to repeat as necessary until the next inventory column is empty
Also to highlight Negative numbers in RED and Text in ORANGE. anywhere in the big selection within L2 to FW1001 [L2:FW1001]
I wrote the code,
I am a bit worried about efficiency.
Is it a good approach to have the calculations done with a loop and IF/ELSEIF
The product rows will go down to a maximum of 1000 so Row 1001
on the left of Column L will be each product that is being inventoried.
I did create a repeat of L in column K ( =L2 "all the way down" ) formula inside K just to make sure it doesn't calculate in column M since the first inventory is starting in Column L so there technically wont be any SOLD calculated in Column M for the first Run
I read something about using Case is better than multiple Ifs but I am not familiar with Case
If you have any input regarding efficiency please by all means, all input is welcome.
Each inventory takes up a sets of 4 Columns
As follows
user will write in 2 columns (INVENTAIRE and COMMANDE)
INVENTAIRE 1 (Code User WILL WRIGHT THE NUMBER OF STOCK COUNTED)
STOCK EPUISE 1 (WILL WRIGHT THE NUMBER OF STOCK SOLD)
COMMANDE 1 (User WRIGHT THE NUMBER OF STOCK ORDERED)
TOTAL APRES COMMANDE 1 (Code WILL WRIGHT THE NUMBER OF STOCK COUNTED + NUMBER ORDERED)
then 2 and so on. until 42 sets of 4 columns ( 42 inventory calculations)
I did also place a few fail safes in case user types text in the (INVENTAIRE or COMMANDE) columns and not to calculate text and numbers to avoid an error.
besides that here is the code.
Code:
Sub InventoryHelper_()
Dim R1 As Range
Set R1 = ThisWorkbook.Sheets("INV").Range("L2:L1000")
For Each cell In R1
' IF INVENTAIRE IS EMPTY AND COMMANDE IS EMPTY
If IsEmpty(cell.Value) And IsEmpty(cell.Offset(0, 2).Value) Then
'THEN
'EPUISE = ""
cell.Offset(0, 1).Value = ""
'TOTAL = ""
cell.Offset(0, 3).Value = ""
' IF INVENTAIRE IS NUMERIC AND COMMANDE IS NUMERIC AND LAST TOTAL IS NUMERIC
ElseIf IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) And IsNumeric(cell.Offset(0, -1).Value) Then
'THEN
'EPUISE = LAST TOTAL - INVENTAIRE
cell.Offset(0, 1).Value = cell.Offset(0, -1).Value - cell.Value
'TOTAL = INVENTAIRE + COMMANDE
cell.Offset(0, 3).Value = cell.Value + cell.Offset(0, 2).Value
' IF INVENTAIRE IS NUMERIC AND COMMANDE IS NUMERIC AND LAST TOTAL is either "NOUVEAU" or "nouveau"
ElseIf IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) And (cell.Offset(0, -1).Value = "NOUVEAU" Or cell.Offset(0, -1).Value = "nouveau") Then
'THEN
'EPUISE = "NOUVEAU"
cell.Offset(0, 1).Value = "NOUVEAU"
'TOTAL = INVENTAIRE + COMMANDE
cell.Offset(0, 3).Value = cell.Value + cell.Offset(0, 2).Value
' IF INVENTAIRE IS NUMERIC AND COMMANDE IS NUMERIC AND LAST TOTAL IS NOT NUMERIC
ElseIf IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) And Not IsNumeric(cell.Offset(0, -1).Value) Then
'THEN
'EPUISE = "TEXT dans TOTAL"
cell.Offset(0, 1).Value = "TEXT dans TOTAL"
'TOTAL = INVENTAIRE + COMMANDE
cell.Offset(0, 3).Value = cell.Value + cell.Offset(0, 2).Value
' IF INVENTAIRE IS NOT NUMERIC AND COMMANDE IS NUMERIC
ElseIf Not IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) Then
'THEN
'EPUISE = "TEXTE dans INV"
cell.Offset(0, 1).Value = "TEXTE dans INV"
'TOTAL =
cell.Offset(0, 3).Value = cell.Offset(0, 2).Value
' IF INVENTAIRE IS NUMERIC AND COMMANDE IS NOT NUMERIC AND LAST TOTAL IS NUMERIC
ElseIf IsNumeric(cell.Value) And Not IsNumeric(cell.Offset(0, 2).Value) And IsNumeric(cell.Offset(0, -1).Value) Then
'THEN
'EPUISE = LAST TOTAL - INVENTAIRE
cell.Offset(0, 1).Value = cell.Offset(0, -1).Value - cell.Value
'TOTAL = "TEXTE dans COMMANDE"
cell.Offset(0, 3).Value = "TEXTE dans COMMANDE"
'IF INVENTAIRE IS NOT NUMERIC OR COMMANDE IS NOT NUMERIC
ElseIf Not IsNumeric(cell.Value) Or Not IsNumeric(cell.Offset(0, 2).Value) Then
'THEN
'EPUISE = "TEXT"
cell.Offset(0, 1).Value = "TEXT"
'TOTAL = "TEXT"
cell.Offset(0, 3).Value = "TEXT"
End If
Next
End Sub
Where I am mostly stuck is how to get to change the range (R1) [inventory 1] to R2 and R3 and so all the way to R42 on as the next inventories are imputed
I am trying to attach the spreadsheet to help visualise the code.
but I am not able to find the upload a file button as I am posting at the moment. I could send you a copy if you want if i dont get to upload it
Last edited: