How to repeat VBA to the next 4th column on right if there is values on that column and how to highlight text and negative numbers

[rouzacct]

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.

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

 

[Peter_SSs]

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

You can't upload actual files to the forum but there are several methods for posting small screen shots. Click the 'Look Here' link in my signature block below to find them.
We do not approve sharing files privately - refer to #4 of the Forum Rules