Worksheet_Change(ByVal Target As Range)

[Kazz61]

I am trying to write code to store total stock recieved, total stock sent out, total stock on hand.
Below is the excel sheet
A B C D E F G H I
1 Product Number Stock In Stock Out Held Pro. No. Add to Stock In Add to Stock Out
2 20487 20487
3 61400 61400
4 61200 61200
5 7007 7007
6 70523 70523
7 7005 7005

The below code works fine for Stock In, and will add to Col B from user entry in Col H but I need it to do this for all the rows for each product number & also the same for Stock Out.
The held column just deducts the cell in Col C from the cell in Col B, i.e.forumla in Col D Row 2 is =B2-C2.
I know I need to use Range as Range("H2:H7") to incorporate all cells required in that column; however I am not sure of the code that should go in instead of " In2.Value = In2.Value + In1.Value" I have tried
"Target.Offset(0, -6).Value = Target.Offset(0, -6).Value + Target.Value", nothing happens can't figure it out nor can I figure out how to have it do the same for stock out. Any help is appreciated.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Set In1 = Range("H2")
Set In2 = Range("B2")
If Intersect(In1, Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    In2.Value = In2.Value + In1.Value
    Application.EnableEvents = True 
End Sub


[Kazz61]

B.xlsm

Stock.tif

Oops sorry first time posting didn't realise it was all going to scrunch over to the left when there was suppose to be blank spaces in my re hash of the excel.
I have attached the actual file instead.
Cheers Kazz

[Sisyphus]

Hello Kazz61,
The macro recorder is teaching how to address ranges using reference strings like Range("H1:H7"). A single cell is also a range. Therefore you can address it as Range("H1"). To answer your question:

Code: Select all

Range("H7").Value = Range("H6").Value + Range("A7").Value - Range("E7").Value

will calculate a new total stock if A7 has the incoming and E7 has the outgoing quantities.
Value is the default property of the Range object. You don't have to write it, but I always do, as a matter of principle. It helps to avoid or find omission errors where Value isn't the default. Meaning, it is easier to read what you see than what you don't see.

I dislike the above kind of addressing because calculating such addresses is messy. The cell below H5 is
Range("H" & 5+1), and it is even worse if you have to calculate the column as well. My preferred way of addressing a cell is like this:

Code: Select all

ActiveSheet.Cells(7, 8)

Activesheet is the defult. You can omit it (but I don't ...). In this manner of addressing the Row is always first, the column last. Cells(7, 8), therefore, is H7. You can "mix" the referencing, like Cells(7, "H")

ActiveSheet as a whole is a Range. Within that range A1 is Cells(1, 1). Now, if you declare your row as a range:

Code: Select all

Set Rng = Range("A7:H7")     ' You must use the word Set to set an object.

The first cell is A7 but you can address it as Rng.Cells(1, 1).

In fact, you can address the cells within any range by their number. If you have a range of 10 cells, say A1:B5, you can address them by their numbers 1 to 10 or by their row and column reference starting from the top left.
Rng.Cells(2, 5) would be the same as Rng.Cells(10).
Therefore, if you have a Range like Set Rng = Range("A7:H7") you can address the cells by their number 1 thru 7 and:

Code: Select all

Dim R as Long    ' Row number
R = 7
With Rng
    .Cells(8).Value = Activesheet.Cells(R, "H").Value + .Cells(1).Value - .Cells(5).Value
End With

This would have the same result as first above explained.
Observe the difference between "ActivSheet.Cells ..." and " .Cells ...". The leading period stands for the omitted "Rng" (in the With statement) and the cell is counted within that range. "ActiveSheet.Cells" counts cells within the ActiveSheet which is quite a different range, of course.

I hope this helps you on your way.

[Sisyphus]

Hello Kazz61,
You posted this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Set In1 = Range("H2")
Set In2 = Range("B2")
If Intersect(In1, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
In2.Value = In2.Value + In1.Value
Application.EnableEvents = True
End Sub

and I didn't respond to it. Sorry about that.
Your code will run whenever there is a change H2. I have tried it and it does do what you want.
Nothing will happen if you change B2 becasue you have specified only the Intersect with H2.

[Kazz61]

Hello Sisyphus,
Thankyou so much your knowledge has expanded mine.
I am very grateful for the help & explanations.
I have not used macros in excel before & I can now see a whole new world opening up with my use of them.
It has also been many years since writing any VB at all and the cobwebs had taken over where the code skills use to live.
I am enjoying cleaning them out all be it frustrating at times.

Ever grateful
Kazz61