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
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
then In my module I have the following code
But I get Object Required error on
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?
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 SubI 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 Subthen 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 SubBut I get Object Required error on
Code:
Set Colonnes = Target.Address.Column
Set Article = Target.Address.RowEach 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?
