Help me understand my VBA code please (easy) - thanks!

[David77]

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:

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

 

[Yongle]

= 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

 

[David77]

= 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]

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.