Simple stock control (2024)

Sort by dateSort by votes

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
13,764
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
  • Friday at 8:28 AM
  • #2

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.

Upvote0

M

maiwarits

New Member
Joined
Jul 17, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  • Friday at 8:54 PM
  • #3

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.

Sorry for that mistake.

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

Thanks!
Warit

Upvote0

Sanjeev1976

Active Member
Joined
Dec 25, 2008
Messages
430
Office Version
  1. 365
Platform
  1. Windows
  • Saturday at 1:58 AM
  • #4

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

Upvote0

Sanjeev1976

Active Member
Joined
Dec 25, 2008
Messages
430
Office Version
  1. 365
Platform
  1. Windows
  • Saturday at 2:12 AM
  • #5

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

Upvote0

Solution

M

maiwarits

New Member
Joined
Jul 17, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  • Saturday at 2:23 AM
  • #6

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

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.

Upvote0

M

maiwarits

New Member
Joined
Jul 17, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  • Saturday at 2:28 AM
  • #7

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

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?

Upvote0

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
13,764
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
  • Saturday at 11:33 AM
  • #8

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.

VBA Code:

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

Upvote0

You must log in or register to reply here.

Simple stock control (2024)
Top Articles
Latest Posts
Article information

Author: Lakeisha Bayer VM

Last Updated:

Views: 6173

Rating: 4.9 / 5 (49 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Lakeisha Bayer VM

Birthday: 1997-10-17

Address: Suite 835 34136 Adrian Mountains, Floydton, UT 81036

Phone: +3571527672278

Job: Manufacturing Agent

Hobby: Skimboarding, Photography, Roller skating, Knife making, Paintball, Embroidery, Gunsmithing

Introduction: My name is Lakeisha Bayer VM, I am a brainy, kind, enchanting, healthy, lovely, clean, witty person who loves writing and wants to share my knowledge and understanding with you.