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

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" wrote:

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

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" wrote:

> 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" wrote:
>
> > 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..

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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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,
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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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..

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

http://www.savefile.com/

Mike

"ksfarmer" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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,
Link to file
http://savefile.com/projects/808744698
Hope I did it right.
The Green column is the quantity entry column
Frank

"Mike H" wrote:

> upload the file here and post the link, it's free
>
> http://www.savefile.com/
>
> Mike
>
> "ksfarmer" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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..

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" wrote:

> 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" wrote:
>
> > upload the file here and post the link, it's free
> >
> > http://www.savefile.com/
> >
> > Mike
> >
> > "ksfarmer" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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" wrote:
> > > > > >
> > > > > > > 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,
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

"Mike H" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > upload the file here and post the link, it's free
> > >
> > > http://www.savefile.com/
> > >
> > > Mike
> > >
> > > "ksfarmer" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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" wrote:
> > > > > >
> > > > > > > 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" wrote:
> > > > > > >
> > > > > > > > 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,
One other question. Can you recommend a good book/s for learning Visual
Basic and writing macros for excel?
Again - Thanks

"ksfarmer" wrote:

> 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
>
> "Mike H" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > upload the file here and post the link, it's free
> > > >
> > > > http://www.savefile.com/
> > > >
> > > > Mike
> > > >
> > > > "ksfarmer" wrote:
> > > >
> > > > > 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" wrote:
> > > > >
> > > > > > 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" wrote:
> > > > > >
> > > > > > > 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" wrote:
> > > > > > >
> > > > > > > > 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" wrote:
> > > > > > > >
> > > > > > > > > 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..