change from calculating vertically (each cell in Column) to horizontally (row)

rouzacct

Board Regular
Joined
Aug 31, 2010
Messages
65
Hello
I have this working code that calculates from row 2 to 1000 on column L and skips 4 columns to P then T and so on... skipping 4 columns at a time up to column 176 (PT) but I see it is taking too long to calculate

Here is the code that works vertically now
Code:
Sub CALC2_()

Dim rng As Range
Dim i As Long
Application.ScreenUpdating = False


    For i = 12 To 176 Step 4
    
Set rng = ThisWorkbook.Sheets("INV").Range(Cells(2, i), Cells(1001, i))


    For Each cell In rng

        '  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 NUMERIC
        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 = LAST TOTAL - INVENTAIRE
                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
                
            
                
            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"
                


                
            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
Next


Application.ScreenUpdating = True
End Sub

I would Like to change the code above to calculate horizontally and to activate itself when a change is made on specific columns ( L, P, T skipping 4 columns at a time up to Column 176 (PT) for rows 2 till 1000

To run the code only on that changed cell's Row and not the whole column + next columns one after the other. like [FOR EACH does]

this way I am trying to save calculation time for each time user imputes a new value.
instead of going For each cell from 2 to 1000 of Each Column selected one after the other,
it would just calculate to the right of the target cell up to the last INVENTORY column being (PT) so a set of 42 Inventory counts.


I tried something like this

first i placed the following code in "INV" sheet's code

Code:
Private Sub Worksheet_Change(ByVal Target As Range)    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("L2:L10") 'this is just a test sample rage to see it work and not freeze'
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


call Perrow
       
    End If
End Sub


then In my module I have the following code
Code:
Sub Perrow()


Application.ScreenUpdating = False


Dim rng As Range
Dim i As Long
Dim Colonnes As Long
Dim Article As Long


Set Colonnes = Target.Address.Column  ' this is useless at the moment but i was thinking it 'would be needed eventually to start from that column of the target and skip to the next 'column that is 4 columns away. 

Set Article = Target.Address.Row


For i = 12 To 176 Step 4 ' I kept this piece from my working code but I feel I dont know how 'to integrate it correctly 
    
Set rng = ThisWorkbook.Sheets("INV").Range(Cells(Article, i), Cells(Article, i))


    For Each cell In rng

        '  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 NUMERIC
        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 = LAST TOTAL - INVENTAIRE
                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
                
            
                
            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"
                


                
            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
Next


Application.ScreenUpdating = True
End Sub

But I get Object Required error on

Code:
Set Colonnes = Target.Address.Column
Set Article = Target.Address.Row

Each Inventory is a set of 4 columns
[L being the first column (the user writes the inventory in L) the next is P then T until column FT skipping 4 columns at a time for 42 sets of 4]
[M being the sold amount to be calculated by the code]
[N being the order imputed (the user writes the amount ordered in N) and that too has an equivalent in the next set of 4 columns]
[O Being the total of the inventory in stock + N new order amont)

[Also Column K on the left of L will be (=L:L) all the way down and will be hidden from view that way the first inventory wont give an error due to the code calculating the ITEM NAME with a NUMBER in L trying to count SOLD amount in (K-L = M)]

My problem is that I can seem to have the updates happen only for each item at a time for their row as user inputs a number and presses ENTER.

and Also now that I want calculate horizontally and not like my original code that calculates Vertically and that I want to trigger the code based on selective column's changes

I must take in consideration that there are two columns in each set of 4 columns that the user will write in.
Column L and N

so I must write a hole different set of code for when user inputs a new Order(Commande)
the N, R, V....columns
but that I can do once I have a working code for the first set (inventory count)
I will then do that part
I would just need to know where to paste a new code for the correct Offests for these for that part.

I decided to calculate this way so that when user updates or corrects an inputted inventory count for an item the whole row for that item is re calculated from where user changed a value up to where it is blank up to a maximum of column 176 (PT)

Any thought?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("L:L,P:P,T:T,X:X,AB:AB,AF:AF,AJ:AJ,AN:AN,AR:AR,AV:AV,AZ:AZ,BD:BD,BH:BH,BL:BL,BP:BP,BT:BT,BX:BX,CB:CB,CF:CF,CJ:CJ,CN:CN,CR:CR,CV:CV,CZ:CZ,DD:DD,DH:DH,DL:DL,DP:DP,DT:DT,DX:DX,EB:EB,EF:EF,EJ:EJ,EN:EN,ER:ER,EV:EV,EZ:EZ,FD:FD,FH:FH,FL:FL,FP:FP,FT:FT")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


Application.ScreenUpdating = False


Dim rng As Range
Dim i As Long
Dim InventaireActive As Long
Dim ArticleActive As Long


    For i = 12 To 176 Step 4
    
Set rng = ThisWorkbook.Sheets("INV").Range(Cells(Target.Row, i), Cells(Target.Row, i))


    For Each cell In rng




        '  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 NUMERIC
        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 = LAST TOTAL - INVENTAIRE
                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
                
            
                
            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"
                


                
            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
Next


Application.ScreenUpdating = True
End If
End Sub

With some trial and error, Got this working
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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