Clear value in cell when the "enter" key is pressed

[ksfarmer]

I have a work area of 4 columns wide and 60 rows long
I need the following macro

1. I enter numbers in column A1
2. Accumulate them in Column B1
3. Clear the value in Column A1 when "enter" key is pressed
4. Enter a new number in Column A1
5. Acumulate that in Column B1
6. Clear the value in Column A1 when the "enter" key is pressed
7 repeat as data entry requires.

This same routine would be applied to A2-B2 through row A60-B60

Any help appreciated..

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A60")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value
Target.Value = ""
Application.EnableEvents = True
Else
'optional line to delete any text entered in range
'Target.Value = ""
End If
End If
End Sub

Mike

 

[ksfarmer]

Hello Mike,
Thank your for the code!
I am not sure what the right click on the "sheet tab" is referring to.

Sorry to be so dumb!
Frank

 

[Mike H]

Hi,

At the bottom of your worksheet is a tab with the sheet name on it. Right
click this tab and you get a popup menu. Click 'View code' on this menu and
paste the code I gave you into the white space on the right.

Close VB editor (That's what your now in) by clicking the red cross and
start entering your values in column A

Mike

 

[ksfarmer]

Mike,
I got there and pasted the code in then closed the code window.
It does not seem to change the operation of the sheet.
I could email you a copy of the sheet I have developed so you could see what
it is doing.
Frank

 

[Mike H]

upload the file here and post the link, it's free

http://www.savefile.com/

Mike

 

[ksfarmer]

Mike,
Link to file
http://savefile.com/projects/808744698
Hope I did it right.
The Green column is the quantity entry column
Frank

 

[Mike H]

Hi,

the problem was you had pasted the code with each line beginning with >> so
every line was a syntax error. I've ammended the code such that if you enter
data in the green column (C) it accumulates in column d. I've also removed
all the formula from column d because they were circular references.

http://www.savefile.com/files/2019846

Mike

 

[ksfarmer]

Mike,
Works like a champ!!

One glitch was the Macro Security. I had to set it to minimum because the
macro was not certified (certificate).

Little info on why we wanted this routine. My son is in residential and
commercial construction. He goes through plans sheet by sheet entering
individual quantities of items that are required.

He was doing all this with a calculator for 5 years. I have minimal working
knowledge of excel so got tagged to help him. Was way above my head.

So, I do sincerely THANK you for your generous help!
Frank

 

[ksfarmer]

Mike,
One other question. Can you recommend a good book/s for learning Visual
Basic and writing macros for excel?
Again - Thanks

 

[Mike H]

Hi,

First off I'm glad I could help and thanks for the feedback, a good book ?
Look here

http://www.j-walk.com/

Mike