On Going Calculation

[justme1052]

I’m trying to write a code to do whole a sheet with , On Going Calculation . I have uploaded a worksheet sample. it is easier for me to show than me trying to explain. my trying to explain gets everyone confused .

I also posted this on Excel.com Forum, a couple days ago but never receive a reply back.

I was wanting a sheet to sheet, but I have changed my spread sheet and put everything on the same sheet
I tried writing a code for each column from BR6 to DP20 to go to each section Cell and column starting at GU3 . One of the members at excel.com a while back show me how to write a on- going calculation to go To one cell , which is what I had asked for and it worked. But trying to write it for more than one cell, not only did my attempt to write code not work, it clear all the value figures in the entire section. So I exited out and clicked do not save. So I need the experts’ advice again.
Below was my attempt at writing a code. I put the Value +1 in it so would just add 1 to the the cell it was going to and not the box number that won the race.
Example on sample sheet the number (value) in BY7 is 5 and it would go to sample B sheet 2 AK3 as 1. All the numbers in sheet 1 from BY7 to DP20 are just box numbers, my goal is to see who many times each box comes in.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      With Target
         If .Address(False, False) = "BY6:DP20" Then
            If IsNumeric(.Value) Then
               Application.EnableEvents = False
               Range("GU3").Value = Range("HB3").Value + 1
               Range("GB4").Value = Range("HB4").Value + 1
               Range("GB5").Value = Range("HB5").Value + 1
                Range("GB6").Value = Range("HB6").Value + 1
               Range("GB12").Value = Range("HB12").Value + 1
              Range("GB13").Value = Range("HB13").Value + 1
               Range("GB14").Value = Range("HB14").Value + 1
              Range("GB15").Value = Range("HB15").Value + 1
             Application.EnableEvents = True
            End If
            End If
           End With
            End Sub

I only tried 2 sections but the codes did not work. I would like to know how to write a proper code starting at GU3, so it will send the correct value to each cell . Grade A will go to Grade A info section Grade B to Grade B ,etc.
Also is there a way to make this a 30 day results so after 30 days when it add day 31 it will go back to the first day so it becomes a continues 30 days results.

Due to size only part of sample 1 is shown. but BY6 to DP20 is where the results number appear after I download the results each day.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Below is a copy of the help I received from the excel.com member. his help worked for what I asked for, he did a great job. I was hoping after I saw how to get started I
could go from there and complete the whole sheet on my own. Sorry to say that did not happen.

The title of my Thread on excelforum.com is Sheet to Sheet On Going calculation .
----------------------------------------------------------------------------------------
ongoing calculator Formula

Here are the step by step instructions on using the VBA event macro. This is your best option for the task at hand.

Cell A1 is where the new entry is made and cell B8 is the running total.

In Excel 2007 (as your profile indicates)...

Open the file where this is to be used
Select the sheet where this is to be used
Right click the sheet tab
Select: View Code (this will open the VB editor)
Copy the code below and paste it into the window that opens on the right hand side of the screen:

[Select Code] copy to clipboard

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
          With Target
             If .Address(False, False) = "BY6" Then
                If IsNumeric(.Value) Then
                   Application.EnableEvents = False
                   Range("BG").Value = Range("BG7").Value + .Value
                   Application.EnableEvents = True
                End If
             End If
          End With
    End Sub

Close the VB editor to return to Excel

Try it out. Enter some numbers in cell A1 and observe cell B8.

If you want to reset the total just delete the contents of cell B8.

You'll have to save the file as a macro enabled file in the *.xlsm file format.
-----------------------------------------------------------------------------------------------------------

one page sample

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
          With Target
             If .Address(False, False) = "AP1" Then
                If IsNumeric(.Value) Then
                   Application.EnableEvents = False
                   Range("AN1").Value = Range("AN1").Value + .Value
                   Application.EnableEvents = True
                End If
             End If
          End With
    End Sub