Adding cells from 2 sheets to a third cell without value in cell changing

FDPetey

FDPetey

New Member
Joined
Aug 14, 2023
Messages
20
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hello all, first time I've posted and asked for help. I'm trying to make a spreadsheet for end users where they can enter a value into a cell on sheet 1 and in sheet 2, the values are added together for review by admin. That part is simple and working however, the problem I'm looking to fix is that sheet 1 will have new values entered weekly and the value in sheet 2 will need to be added onto so a monthly and quarterly running total is produced where other formulas can be run against that number. I basically just can't figure out how to keep a running total on sheet 2 as weekly entries are made on sheet 1.

Hope I explained this well.

Thanks in advance for any help.
 
Sort by date Sort by votes
Sorry, I'm only posting a small snapshot because the forum came up saying I had too many characters. It starts at the beginning, ends at $E$10, picks back up at $E$190, and finishes at $E$200 to End Sub the last line.


VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$3" Then
On Error GoTo M
Dim ans As Variant
ans = Sheets("Training Hour Entry Sheet").Range("E3").Value
Dim anns As Long
anns = Sheets("Incentive Pay Calculation Sheet").Range("H2").Value
Sheets("Incentive Pay Calculation Sheet").Range("H2").Value = anns + ans
End If
If Target.Address = "$F$3" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F3").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H2").Value
Sheets("Incentive Pay Calculation Sheet").Range("H2").Value = anns + ans
End If

If Target.Address = "$E$4" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E4").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H3").Value
Sheets("Incentive Pay Calculation Sheet").Range("H3").Value = anns + ans
End If
If Target.Address = "$F$4" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F4").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H3").Value
Sheets("Incentive Pay Calculation Sheet").Range("H3").Value = anns + ans
End If

If Target.Address = "$E$5" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E5").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H4").Value
Sheets("Incentive Pay Calculation Sheet").Range("H4").Value = anns + ans
End If
If Target.Address = "$F$5" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F5").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H4").Value
Sheets("Incentive Pay Calculation Sheet").Range("H4").Value = anns + ans
End If

If Target.Address = "$E$6" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E6").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H5").Value
Sheets("Incentive Pay Calculation Sheet").Range("H5").Value = anns + ans
End If
If Target.Address = "$F$6" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F6").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H5").Value
Sheets("Incentive Pay Calculation Sheet").Range("H5").Value = anns + ans
End If

If Target.Address = "$E$7" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E7").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H6").Value
Sheets("Incentive Pay Calculation Sheet").Range("H6").Value = anns + ans
End If
If Target.Address = "$F$7" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F7").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H6").Value
Sheets("Incentive Pay Calculation Sheet").Range("H6").Value = anns + ans
End If

If Target.Address = "$E$8" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E8").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H7").Value
Sheets("Incentive Pay Calculation Sheet").Range("H7").Value = anns + ans
End If
If Target.Address = "$F$8" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F8").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H7").Value
Sheets("Incentive Pay Calculation Sheet").Range("H7").Value = anns + ans
End If

If Target.Address = "$E$9" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E9").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H8").Value
Sheets("Incentive Pay Calculation Sheet").Range("H8").Value = anns + ans
End If
If Target.Address = "$F$9" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F9").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H8").Value
Sheets("Incentive Pay Calculation Sheet").Range("H8").Value = anns + ans
End If

If Target.Address = "$E$10" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E10").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H9").Value
Sheets("Incentive Pay Calculation Sheet").Range("H9").Value = anns + ans
End If
If Target.Address = "$F$10" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F10").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H9").Value
Sheets("Incentive Pay Calculation Sheet").Range("H9").Value = anns + ans
End If


If Target.Address = "$E$190" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E190").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H189").Value
Sheets("Incentive Pay Calculation Sheet").Range("H189").Value = anns + ans
End If
If Target.Address = "$F$190" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F190").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H189").Value
Sheets("Incentive Pay Calculation Sheet").Range("H189").Value = anns + ans
End If

If Target.Address = "$E$191" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E191").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H190").Value
Sheets("Incentive Pay Calculation Sheet").Range("H190").Value = anns + ans
End If
If Target.Address = "$F$191" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F191").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H190").Value
Sheets("Incentive Pay Calculation Sheet").Range("H190").Value = anns + ans
End If

If Target.Address = "$E$192" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E192").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H191").Value
Sheets("Incentive Pay Calculation Sheet").Range("H191").Value = anns + ans
End If
If Target.Address = "$F$192" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F192").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H191").Value
Sheets("Incentive Pay Calculation Sheet").Range("H191").Value = anns + ans
End If

If Target.Address = "$E$193" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E193").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H192").Value
Sheets("Incentive Pay Calculation Sheet").Range("H192").Value = anns + ans
End If
If Target.Address = "$F$193" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F193").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H192").Value
Sheets("Incentive Pay Calculation Sheet").Range("H192").Value = anns + ans
End If

If Target.Address = "$E$194" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E194").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H193").Value
Sheets("Incentive Pay Calculation Sheet").Range("H193").Value = anns + ans
End If
If Target.Address = "$F$194" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F194").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H193").Value
Sheets("Incentive Pay Calculation Sheet").Range("H193").Value = anns + ans
End If

If Target.Address = "$E$195" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E195").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H194").Value
Sheets("Incentive Pay Calculation Sheet").Range("H194").Value = anns + ans
End If
If Target.Address = "$F$195" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F195").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H194").Value
Sheets("Incentive Pay Calculation Sheet").Range("H194").Value = anns + ans
End If

If Target.Address = "$E$196" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E196").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H195").Value
Sheets("Incentive Pay Calculation Sheet").Range("H195").Value = anns + ans
End If
If Target.Address = "$F$196" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F196").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H195").Value
Sheets("Incentive Pay Calculation Sheet").Range("H195").Value = anns + ans
End If

If Target.Address = "$E$197" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E197").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H196").Value
Sheets("Incentive Pay Calculation Sheet").Range("H196").Value = anns + ans
End If
If Target.Address = "$F$197" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F197").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H196").Value
Sheets("Incentive Pay Calculation Sheet").Range("H196").Value = anns + ans
End If

If Target.Address = "$E$198" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E198").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H197").Value
Sheets("Incentive Pay Calculation Sheet").Range("H197").Value = anns + ans
End If
If Target.Address = "$F$198" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F198").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H197").Value
Sheets("Incentive Pay Calculation Sheet").Range("H197").Value = anns + ans
End If

If Target.Address = "$E$199" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E199").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H198").Value
Sheets("Incentive Pay Calculation Sheet").Range("H198").Value = anns + ans
End If
If Target.Address = "$F$199" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F199").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H198").Value
Sheets("Incentive Pay Calculation Sheet").Range("H198").Value = anns + ans
End If

If Target.Address = "$E$200" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E200").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H199").Value
Sheets("Incentive Pay Calculation Sheet").Range("H199").Value = anns + ans
End If
If Target.Address = "$F$200" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F200").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H199").Value
Sheets("Incentive Pay Calculation Sheet").Range("H199").Value = anns + ans
End If

Exit Sub
M:
MsgBox "You entered " & ans & vbNewLine & "this is not a number" & vbNewLine & "Try again"
End Sub
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
FDPetey said:
Sorry, thought I should state the exact error - Compile error: Procedure too large. Also, when the error occurs, the first line - Private Sub Worksheet_Change (ByVal Target As Range) - is highlighted in yellow and - End Sub - is highlighted blue until you click of to the side.
Click to expand...
Again, you need to post the code here.
Is there some reason why you do not want to post the code here.
I cannot help unless you post the code here.
 
Upvote 0
My Aswer Is This said:
If you have problems with code you need to post the code here so I can look at it.
If you look you will see an icon which Says VBA in this forum
You will also see B I and on and on Click on Vba and then post your code there.
Click to expand...
Sorry, I'm only posting a small snapshot because the forum came up saying I had too many characters. It starts at the beginning, ends at $E$10, picks back up at $E$190, and finishes at $E$200 to End Sub the last line.


VBA Code:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$3" Then
On Error GoTo M
Dim ans As Variant
ans = Sheets("Training Hour Entry Sheet").Range("E3").Value
Dim anns As Long
anns = Sheets("Incentive Pay Calculation Sheet").Range("H2").Value
Sheets("Incentive Pay Calculation Sheet").Range("H2").Value = anns + ans
End If
If Target.Address = "$F$3" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F3").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H2").Value
Sheets("Incentive Pay Calculation Sheet").Range("H2").Value = anns + ans
End If

If Target.Address = "$E$4" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E4").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H3").Value
Sheets("Incentive Pay Calculation Sheet").Range("H3").Value = anns + ans
End If
If Target.Address = "$F$4" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F4").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H3").Value
Sheets("Incentive Pay Calculation Sheet").Range("H3").Value = anns + ans
End If

If Target.Address = "$E$5" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E5").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H4").Value
Sheets("Incentive Pay Calculation Sheet").Range("H4").Value = anns + ans
End If
If Target.Address = "$F$5" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F5").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H4").Value
Sheets("Incentive Pay Calculation Sheet").Range("H4").Value = anns + ans
End If

If Target.Address = "$E$6" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E6").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H5").Value
Sheets("Incentive Pay Calculation Sheet").Range("H5").Value = anns + ans
End If
If Target.Address = "$F$6" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F6").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H5").Value
Sheets("Incentive Pay Calculation Sheet").Range("H5").Value = anns + ans
End If

If Target.Address = "$E$7" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E7").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H6").Value
Sheets("Incentive Pay Calculation Sheet").Range("H6").Value = anns + ans
End If
If Target.Address = "$F$7" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F7").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H6").Value
Sheets("Incentive Pay Calculation Sheet").Range("H6").Value = anns + ans
End If

If Target.Address = "$E$8" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E8").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H7").Value
Sheets("Incentive Pay Calculation Sheet").Range("H7").Value = anns + ans
End If
If Target.Address = "$F$8" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F8").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H7").Value
Sheets("Incentive Pay Calculation Sheet").Range("H7").Value = anns + ans
End If

If Target.Address = "$E$9" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E9").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H8").Value
Sheets("Incentive Pay Calculation Sheet").Range("H8").Value = anns + ans
End If
If Target.Address = "$F$9" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F9").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H8").Value
Sheets("Incentive Pay Calculation Sheet").Range("H8").Value = anns + ans
End If

If Target.Address = "$E$10" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E10").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H9").Value
Sheets("Incentive Pay Calculation Sheet").Range("H9").Value = anns + ans
End If
If Target.Address = "$F$10" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F10").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H9").Value
Sheets("Incentive Pay Calculation Sheet").Range("H9").Value = anns + ans
End If


If Target.Address = "$E$190" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E190").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H189").Value
Sheets("Incentive Pay Calculation Sheet").Range("H189").Value = anns + ans
End If
If Target.Address = "$F$190" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F190").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H189").Value
Sheets("Incentive Pay Calculation Sheet").Range("H189").Value = anns + ans
End If

If Target.Address = "$E$191" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E191").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H190").Value
Sheets("Incentive Pay Calculation Sheet").Range("H190").Value = anns + ans
End If
If Target.Address = "$F$191" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F191").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H190").Value
Sheets("Incentive Pay Calculation Sheet").Range("H190").Value = anns + ans
End If

If Target.Address = "$E$192" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E192").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H191").Value
Sheets("Incentive Pay Calculation Sheet").Range("H191").Value = anns + ans
End If
If Target.Address = "$F$192" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F192").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H191").Value
Sheets("Incentive Pay Calculation Sheet").Range("H191").Value = anns + ans
End If

If Target.Address = "$E$193" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E193").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H192").Value
Sheets("Incentive Pay Calculation Sheet").Range("H192").Value = anns + ans
End If
If Target.Address = "$F$193" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F193").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H192").Value
Sheets("Incentive Pay Calculation Sheet").Range("H192").Value = anns + ans
End If

If Target.Address = "$E$194" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E194").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H193").Value
Sheets("Incentive Pay Calculation Sheet").Range("H193").Value = anns + ans
End If
If Target.Address = "$F$194" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F194").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H193").Value
Sheets("Incentive Pay Calculation Sheet").Range("H193").Value = anns + ans
End If

If Target.Address = "$E$195" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E195").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H194").Value
Sheets("Incentive Pay Calculation Sheet").Range("H194").Value = anns + ans
End If
If Target.Address = "$F$195" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F195").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H194").Value
Sheets("Incentive Pay Calculation Sheet").Range("H194").Value = anns + ans
End If

If Target.Address = "$E$196" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E196").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H195").Value
Sheets("Incentive Pay Calculation Sheet").Range("H195").Value = anns + ans
End If
If Target.Address = "$F$196" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F196").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H195").Value
Sheets("Incentive Pay Calculation Sheet").Range("H195").Value = anns + ans
End If

If Target.Address = "$E$197" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E197").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H196").Value
Sheets("Incentive Pay Calculation Sheet").Range("H196").Value = anns + ans
End If
If Target.Address = "$F$197" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F197").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H196").Value
Sheets("Incentive Pay Calculation Sheet").Range("H196").Value = anns + ans
End If

If Target.Address = "$E$198" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E198").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H197").Value
Sheets("Incentive Pay Calculation Sheet").Range("H197").Value = anns + ans
End If
If Target.Address = "$F$198" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F198").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H197").Value
Sheets("Incentive Pay Calculation Sheet").Range("H197").Value = anns + ans
End If

If Target.Address = "$E$199" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E199").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H198").Value
Sheets("Incentive Pay Calculation Sheet").Range("H198").Value = anns + ans
End If
If Target.Address = "$F$199" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F199").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H198").Value
Sheets("Incentive Pay Calculation Sheet").Range("H198").Value = anns + ans
End If

If Target.Address = "$E$200" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("E200").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H199").Value
Sheets("Incentive Pay Calculation Sheet").Range("H199").Value = anns + ans
End If
If Target.Address = "$F$200" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F200").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H199").Value
Sheets("Incentive Pay Calculation Sheet").Range("H199").Value = anns + ans
End If

Exit Sub
M:
MsgBox "You entered " & ans & vbNewLine & "this is not a number" & vbNewLine & "Try again"
End Sub
 
Upvote 0
Yes you need to post code the way I mentiond inside Vba icon.

And if you are going to do this 800 times you mentioned earlier you may have as many as 800 this will never work. We will need to have another plan.
 
Upvote 0
You still did not post it correctly:
It should look like This:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$3" Then
On Error GoTo M
Dim ans As Variant
ans = Sheets("Training Hour Entry Sheet").Range("E3").Value
Dim anns As Long
anns = Sheets("Incentive Pay Calculation Sheet").Range("H2").Value
Sheets("Incentive Pay Calculation Sheet").Range("H2").Value = anns + ans
End If
If Target.Address = "$F$3" Then
On Error GoTo M
ans = Sheets("Training Hour Entry Sheet").Range("F3").Value
anns = Sheets("Incentive Pay Calculation Sheet").Range("H2").Value
Sheets("Incentive Pay Calculation Sheet").Range("H2").Value = anns + ans
End If
 
Upvote 0
Hello. When you first posted this, you never mentioned needing to do this 800 times.
so, give some time looking at two of your codes and I will get back with you.
 
Upvote 0
I'm not able to copy your code because it's way to much.
Just post like two of them inside Vba brackets.
 
Upvote 0
You will need to get help from someone else.
I asked that you only show me about 3 or 4
And you show me 100 or more.
And code like this will never work. With this much code.

And you're still not posting the code here properly.
If you post the code using the Vba button and then click on the little square Icon on the right after posting the code, you be able to copy the code into the clipboard.
And again, the script will error out when you try to do this too many times that's why I need to come back with a new plan if I can see what the script is doing but I cannot do that when you are trying to do this 800 times.
 
Upvote 0
Since we are trying to do this about 800 times
I believe you should enter the value you want in column F.
and then when you enter the value you want in column E the script runs.
This eliminates a lot of code.
Any reason this would not work?
And I may be able to write a simple little script with only about 20 lines of code to do all 800 if any cell in column E changes.
And I see you have changed the sheet names again.
My code will use the sheet names "Alpha" The sheet where we enter the value in
And Bravo the sheet name where the value will be copied to.
If my script works, then you can then simply change the sheet names.
 
Upvote 0
This is a better way to perform this task with only about 20 lines of code.
and will do as many rows as needed.
I have the script being able to start on row 3 till row 900
You can change this as needed. I figured the first three rows may be headers.

I want you to enter your value into column F and then when you enter you enter your value into column E the script will run.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 8/21/23 4:15 AM EDT
If Target.Column = 5 Then
Dim From As String
From = "Training Hour Entry Sheet"
Dim Too As String
Too = "Incentive Pay Calculation Sheet"
Dim i As Long
Dim ans As Long
Dim anss As Long

For i = 3 To 900
Select Case Target.Row
Case i
Sheets(Too).Cells(i, 6).Value = Sheets(From).Cells(i, 5).Value + Sheets(Too).Cells(i, 6).Value
Sheets(Too).Cells(i, 7).Value = Sheets(From).Cells(i, 6).Value + Sheets(Too).Cells(i, 7).Value
End Select
Next
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Adding weekly totals from one cell to an overall total
3 4 5
Replies
48
Views
1K
Logit
Logit
M
  • Question Question
Add extra line of code to VBA to copy cell value across sheet
Replies
1
Views
76
myall_blues
myall_blues
T
  • Question Question
enter time without colon
Replies
5
Views
201
etaf
etaf
C
  • Solved
Macro/VBA for lookups
Replies
9
Views
183
mumps
mumps
L
  • Solved
Formula that will change which cells are added depending when data is entered
Replies
9
Views
340
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top