Simple stock control (2024)

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 (

) 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.

mumps said:

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.

Click to expand...

See Also

Shapeways Holdings Inc (SHPW) Stock Message Board | InvestorsHubInvestor Village: Stock Message Boards | Stock Quotes | Market NewsStockholders Re-Elect All Seven LifeVantage Incumbent Directors at Fiscal Year 2024 Annual Meeting, by @GlobeNewswireWealth Minerals Announces Agreement for Acquisition of Ignace REE Lithium Property, by @newsfile

Sorry for that mistake.

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

Thanks!
Warit

Try the below with 1 small change :

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

Sub addStock()Dim balanceStock As DoubleDim Col As Integer'Check imput Applies columns E & F only ??If ActiveCell.Column < 5 Or ActiveCell.Column > 6 Then Exit SubCol = Month(Now) + 13balanceStock = InputBox("Amount?")With ActiveCell .Value = .Value + balanceStock If balanceStock < 0 Then Col = Col + 1 Cells(.Row, Col) = Cells(.Row, Col) + balanceStockEnd WithEnd Sub

Instead of above, please change to :

Sub addStock()Dim balanceStock As DoubleDim Col As Integer'Check imput Applies columns E & F only ??If ActiveCell.Column < 5 Or ActiveCell.Column > 6 Then Exit SubCol = 2 * Month(Now) + 6balanceStock = InputBox("Amount?")With ActiveCell .Value = .Value + balanceStock If balanceStock < 0 Then Col = Col + 1 Cells(.Row, Col) = Cells(.Row, Col) + balanceStockEnd WithEnd Sub

Sanjeev1976 said:

Try the below with 1 small change :

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

VBA Code:

Sub addStock()Dim balanceStock As DoubleDim Col As Integer'Check imput Applies columns E & F only ??If ActiveCell.Column < 5 Or ActiveCell.Column > 6 Then Exit SubCol = Month(Now) + 13balanceStock = InputBox("Amount?")With ActiveCell .Value = .Value + balanceStock If balanceStock < 0 Then Col = Col + 1 Cells(.Row, Col) = Cells(.Row, Col) + balanceStockEnd WithEnd Sub

Click to expand...

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.

Sanjeev1976 said:

Instead of above, please change to :

VBA Code:

Sub addStock()Dim balanceStock As DoubleDim Col As Integer'Check imput Applies columns E & F only ??If ActiveCell.Column < 5 Or ActiveCell.Column > 6 Then Exit SubCol = 2 * Month(Now) + 6balanceStock = InputBox("Amount?")With ActiveCell .Value = .Value + balanceStock If balanceStock < 0 Then Col = Col + 1 Cells(.Row, Col) = Cells(.Row, Col) + balanceStockEnd WithEnd Sub

Click to expand...

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?

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 LongPrivate 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 = TrueEnd SubPrivate 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 = TrueEnd Sub