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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
= 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:
Upvote 0
= 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 :(
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,585
Messages
6,179,696
Members
452,938
Latest member
babeneker

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