David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
Hello everybody,

I am currently managing a stock (inventory management) portfolio in Excel. In this, I have my current inventory quantity in column I and two columns in column J & K which I can use to subtract & add amounts from my quantity, so:

Column I = Current quantity
Column J = Enter any number and this will be subtracted from the quantity in column I, after which the number will disappear from column J, thus leaving new room for a new number to be entered in the future.
Column K = Enter any number and this will be added from the quantity in column I, after which the number will disappear from column J, thus leaving new room for a new number to be entered in the future.

My current VBA code looks like this:

1598423091864.png


For good measurement, I have it copy pasted here too:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Columns("J:K"))
If Not r Is Nothing Then
Application.EnableEvents = False
For Each c In r
With Cells(c.Row, "I")
If IsNumeric(.Value) Then
.Value = .Value + IIf(c.Column = 10, -c.Value, c.Value)
Cells(c.Row, "L").Value = Now
Cells(c.Row, "L").NumberFormat = "dd-mm-yyyy, hh:mm:ss"
c.ClearContents
End If
End With
Next c
Application.EnableEvents = True
End If
End Sub

I was given this code to me by another member on this forum some time ago and it works wonders. However, I have trouble understanding some aspects of it. Specifically, in the 8th line, I don't understand what the number "10" means in this sentence:

.Value = .Value + IIf(c.Column = 10, -c.Value, c.Value)

Can anybody please help me understand this? I want to understand in case I want to move my formula from the current columns to future ones somewhere else in the sheet.

Thank you so much everybody!

PS. If anybody can explain to me what application.enableevents = true or = false means, that would also be greatly appreciated :) as well as the "Dim r as range, c as range" parts.

Best regards,
David
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
= IIf(c.Column = 10, -c.Value, c.Value)
If c is in the 10th column ( column I ) then change the sign of the value being used otherwise return the value

Variables should be declared so that VBA knows how they will be used
r is used as a range in
Set r = Intersect(Target, Columns("J:K"))
c is used as a range in For Each c In r

Your macro is an event macro
triggered when changes happen in your sheet
To prevent cell changes made by the macro triggering the macro in an endless loop the trigger is switched off with Application.EnableEvents = False and then switched back on after all changes have been made
 
Last edited:

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
= IIf(c.Column = 10, -c.Value, c.Value)
If c is in the 10th column ( column I ) then change the sign of the value being used otherwise return the value

Variables should be declared so that VBA knows how they will be used
r is used as a range in
Set r = Intersect(Target, Columns("J:K"))
c is used as a range in For Each c In r

Your macro is an event macro
triggered when changes happen in your sheet
To prevent cell changes made by the macro triggering the macro in an endless loop the trigger is switched off with Application.EnableEvents = False and then switched back on after all changes have been made
Hello Yongle,

Apologies for my late response and thank you very much. Your answer truly helped me a lot!

One last question tho, I did not understand this part:

"= IIf(c.Column = 10, -c.Value, c.Value)
If c is in the 10th column ( column I ) then change the sign of the value being used otherwise return the value "

What do you mean "if c is in the 10th column"? My 10th column is column J by the way :)

I truly don't understand this part of the formula :(
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,342
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
c is a single cell, the .Column gives the column number of the cell so if c is in column J it will return 10.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,573
Messages
5,765,182
Members
425,266
Latest member
CPAgirl

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
Top