Simple stock control

[maiwarits]

Hello Sir,

I try to create simple stock control for my gas cylinder in my work to control stock IN and OUT.

Sub addStock()

Dim balanceStock As Double
Dim Col As Integer
'Check imput Applies columns E & F only ??
If ActiveCell.Column < 5 Or ActiveCell.Column > 6 Then Exit Sub

Col = Month(Now) + 12

balanceStock = InputBox("Amount?")

With ActiveCell
    .Value = .Value + balanceStock
    If balanceStock < 0 Then Col = Col + 1
    Cells(.Row, Col) = Cells(.Row, Col) + balanceStock
End With

End Sub

I will always input the number of gas cylinders in column E(LAB1) and column F(LAB2) by massage box.
when Positive value = stock in, Negative value = stock out

The program should be finding current month to add stock IN and OUT after place OK in massage box

Example1 : Stock in LAB1 at Jul

When Active Cell = E5
If I input 5 in text box, 5 should go in E5 by ActiveCell.Value = ActiveCell.Value + 5.
At current month (Jul-Column T) 5 value should go in the cell T5 of July by ActiveCell.Value = ActiveCell.Value + 5.also.

Expected result

Example2 : Stock out LAB2 at Jul

When Active Cell = F6
If I input -2 in text box, -2 should go in U6 by ActiveCell.Value = ActiveCell.Value + (-2).
At current month (Jul-Column U) -2 value should go in the cell U6 of July by ActiveCell.Value = ActiveCell.Value + (-2).also.
Expected result

Example3 : Stock out LAB1 at Aug

When Active Cell = E5
If I input -1 in text box, -1 should go in E5 by ActiveCell.Value = ActiveCell.Value + (-1).
At current month (Aug-Column W) -1 value should go in the cell W5 of Aug by ActiveCell.Value = ActiveCell.Value + (-1).also.

Expected result

If you require more information, please let me know.
Thanks!

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[maiwarits]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Sorry for that mistake.

Here, I have upload it to google drive.
2024 Gas in out control

Thanks!
Warit

 

[Sanjeev1976]

Try the below with 1 small change :

i.e. Col = Month(Now) + 13

Sub addStock()

Dim balanceStock As Double
Dim Col As Integer
'Check imput Applies columns E & F only ??
If ActiveCell.Column < 5 Or ActiveCell.Column > 6 Then Exit Sub

Col = Month(Now) + 13

balanceStock = InputBox("Amount?")

With ActiveCell
    .Value = .Value + balanceStock
    If balanceStock < 0 Then Col = Col + 1
    Cells(.Row, Col) = Cells(.Row, Col) + balanceStock
End With

End Sub

 

[Sanjeev1976]

Instead of above, please change to :

Sub addStock()

Dim balanceStock As Double
Dim Col As Integer
'Check imput Applies columns E & F only ??
If ActiveCell.Column < 5 Or ActiveCell.Column > 6 Then Exit Sub

Col = 2 * Month(Now) + 6

balanceStock = InputBox("Amount?")

With ActiveCell
    .Value = .Value + balanceStock
    If balanceStock < 0 Then Col = Col + 1
    Cells(.Row, Col) = Cells(.Row, Col) + balanceStock
End With

End Sub

 

[maiwarits]

Try the below with 1 small change :

i.e. Col = Month(Now) + 13

Sub addStock()

Dim balanceStock As Double
Dim Col As Integer
'Check imput Applies columns E & F only ??
If ActiveCell.Column < 5 Or ActiveCell.Column > 6 Then Exit Sub

Col = Month(Now) + 13

balanceStock = InputBox("Amount?")

With ActiveCell
    .Value = .Value + balanceStock
    If balanceStock < 0 Then Col = Col + 1
    Cells(.Row, Col) = Cells(.Row, Col) + balanceStock
End With

End Sub

Hello Sir,

I have tried this but the result same as Col = Month(Now) + 12.

When the month has change to Aug it moves only one column to OUT column of July but I expected it to move to IN column of Aug.

 

[maiwarits]

Instead of above, please change to :

Sub addStock()

Dim balanceStock As Double
Dim Col As Integer
'Check imput Applies columns E & F only ??
If ActiveCell.Column < 5 Or ActiveCell.Column > 6 Then Exit Sub

Col = 2 * Month(Now) + 6

balanceStock = InputBox("Amount?")

With ActiveCell
    .Value = .Value + balanceStock
    If balanceStock < 0 Then Col = Col + 1
    Cells(.Row, Col) = Cells(.Row, Col) + balanceStock
End With

End Sub

This one work was I have expected.
However, could you please explain what happened with the previous code why it has move only one column?

 

[mumps]

Copy and paste this code into the worksheet code module replacing your current code. Do the following: right click the tab name for your Yearly Stock sheet and click 'View Code'. Paste the code into the empty code window that opens up. Close the code window to return to your sheet. Enter values in column E or column F and press the ENTER key.

Dim Val As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("E:F")) Is Nothing Or Not Target.Row < 5 Then
        Val = Target.Value
    End If
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    'Clear the color of all cells
    Cells.Interior.ColorIndex = 0
    With Target
      'Highlight row and column of the selected cell
      .EntireRow.Interior.ColorIndex = 22
      .EntireColumn.Interior.ColorIndex = 24
    End With
  Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("E:F")) Is Nothing Or Target.Row < 5 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim oldVal As Long, NewVal As Long
    NewVal = Target.Value
    Target.Value = Val + NewVal
    Set foundDate = Rows(3).Find(Format(Date, "mmm"), LookIn:=xlValues, lookat:=xlWhole)
    If NewVal > 0 Then
        Cells(Target.Row, foundDate.Column).Value = Cells(Target.Row, foundDate.Column).Value + NewVal
    Else
        Cells(Target.Row, foundDate.Column + 1).Value = Cells(Target.Row, foundDate.Column + 1).Value + NewVal
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub