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
My Aswer Is This said:
You need to tell me how you want this to work.
We have talked about a sheet cell change event then a double click event and a script which will do all 800 of them at one time.
The script runs down column A for example and copies this rows data the sheet named "Summary".
But each way may cause you problems.
The sheet change event could cause a problem if a user enters a value like Alpha instead of a number like 56

This new feature on this forum where it does spell check and then tries to anticipate what you want to write drives me crazy.
Click to expand...
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.
 
Upvote 0
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
You must log in or register to reply here.

Similar threads

L
  • Solved
Formula that will change which cells are added depending when data is entered
Replies
9
Views
171
Peter_SSs
Peter_SSs
B
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
239
Burrgogi
B
A
  • Question
VBA to change cell colour based on content
Replies
3
Views
315
Snakehips
Snakehips
T
  • Solved
Adding additional text to a value pulled from another sheet
Replies
8
Views
187
Snakehips
Snakehips
KlausW
  • Solved
Change destination between 2 sheets
Replies
2
Views
201
KlausW
KlausW

Forum statistics

Threads
1,216,115
Messages
6,128,923
Members
449,479
Latest member
nana abanyin

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