refilling data in same cell after user input in another cell

[Guest]

How can I reduce the value in a cell by certain number which I enter in
another cell.
eg: operator enters value 3 in cell D2 and press enter - I want the value
say 55 in cell B5 to be reduced by 3 and have 3 from D2 to be cleared and 55
becomes 52 in B5.

 

[Guest]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D2" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Range("B5") = Range("B5") - .Value
.Value = ""
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[Guest]

Thank You Topper -- But how I will implement this in an worksheet.
Can it be simple. Or Where I should start learnig how to use this visual
basic things without big hasstle in short time and without taking lot of
courses and spending lots of money.

 

[Gord Dibben]

Right-click on the sheet tab and "View Code"

Copy/paste Topper's code into that sheet module.

Start pounding numbers into D2.

I strongly suggest you not use this method.

You have no way of correcting any errors made when entering a new number into
D2.

i.e. you have no "paper trail" to follow to see where an error occurred.


Gord Dibben MS Excel MVP

 

[Guest]

Hi Gord
I created Module in a new worksheet - & tried entering data in D2 & pressing
enter -- nothing worked. I tried F5 etc ---.
I have very complicated (for me) sheet with long conditional formulas
working for maintaining my stock for machines parts. I am using Excel 2003
sp2. At present I have to update stock quantity manually. But I want to
automate. If this first step works then I can introduce what machine a
particular stock is required with some machine code check in another column &
if true then it will subtract it othervise not.
& so on it need to proceed to next row for another check
I thought I can limit the formulas calculations so I tried limiting formula
calculations to 1 time only & using F9 -- It is not that good too.
Are these modules - macros.
I need to get a nack of it & once I understand I will work more complicated.
can someone get me going to start on these modules / macros / vb thing in a
simple manner.

 

[Guest]

First, I think you should consider carefully Gordon's advice about using this
approach which leaves no room for error and as he says, no audit trail.

As for learning VBA, the cheapest way is to buy a good book (John
Walkenbach's are highly recommended - he has a web site: www.j-walk.com) and
then get lots of practice. Plus use these NGs as an excellent source of
expertise (none better!).

Without seeing your w/book it is impossible to advise you on what to do
next. if you can send a sample to me with clear explanation of
requirement(s), I am willing to offer you my solution(s).

E-mail: toppers at REMOVETHISjohntopley.fsnet.co.uk

 

[Guest]

Hi Topper/Gord,
I am emailing the excell file t you both and here is a little explanatin of
what i am trying.

I want INSTOCK items to be reduced in the required cells after input from
user in
XX and YY e.g. #of machines = 2 of type “A†in cell “McCodeInput†and press
Enter on key board.


Look for “ A ‘s “ in column “ MACHINE CODE “ ( in this case first one is
Barrel Mount ) then in the concerned sheet e.g. in this case GX-A look how
many parts of that stock are required per machine --- then multiply that by
the (# of machines to take out = XX i.e. 2 ) in this case in cell “ NumMcs “
and subtract it from that IN STOCK quantity.

This work book I want it like this final. So far I am using similar thing
but doing this all this stock reducing manually because I know which machine
takes how many of which parts and I simply multiply that in my head and
reduce it every now and then from “ ALL “ work sheet.

For printing I go to that particular machine and print it if required. Its
easy but I want it easier so that I fill only “ XX “ and “ YY “ and press
Enter & the job is done.

I started learning Visual Basic & it does seems very easy. So I think with
help / suggestion from some of you experts will help a lot.

H Singh

 

[Guest]

I just sent three emails but I got the Toppers yours private one thru but
others which I tried to send you both on your names at
discussions.microsoft.com failed. Is there some way for sending this file to
Gord also so that he can also see it.
Thx.