VBA Summing up a range of values for every user entered

I have 3 columns A,B,C,D,E,F-  Column A is a name, and Column B ,C ,D,E are the values, and Column F should have a summation of all the values in it. So every time I enter a new user in column a one after the other along with new set of values column f should calculate the value automatically in VBA.  I know a change event and sum application would drive this. can you help me with the code.

Top Contributor Asked on July 26, 2016 in VBA.
Add Comment
11 Answer(s)
Best answer

The below statement is wrong:

Cells(Target.Row, 6).Formula >= 4

It should be  Cells(Target.Row, 6) >= 4

And use formula, instead of calculation, the below code should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)
'Perform this task only if any changes in Column A
If Target.Column = 1 Then
'Clear the if name is empty
If Cells(Target.Row, 1) = "" Then
Cells(Target.Row, 6).ClearContents
Cells(Target.Row, 7).ClearContents
Else
'This will enter the formula
Cells(Target.Row, 6).Formula = "=SUM(B" & Target.Row & ":E" & Target.Row & ")"
Cells(Target.Row, 7).Formula = "=IF(F" & Target.Row & ">4,""Pass"",""Fail"")"
End If
End If
End Sub

Thanks-PNRao!

Keymaster Answered on July 26, 2016.
Add Comment

Hi, Copy the below code and paste in the required worksheet module:

This code will see if any changes in Column A to E and Sum up the values and print in Column F. I have also added a statement to not show zeros, you can change as per your requirement.

Private Sub Worksheet_Change(ByVal Target As Range)
'Perform this task only if any changes in Column A to E and Name should not be blank
If Target.Column < 5 And Cells(Target.Row, 1) <> "" Then
Cells(Target.Row, 6) = _
Cells(Target.Row, 2) + Cells(Target.Row, 3) + Cells(Target.Row, 4) _
+ Cells(Target.Row, 5)
'if do not show if sum is zero
If Cells(Target.Row, 6) = 0 Then Cells(Target.Row, 6).ClearContents
End If
End Sub

Thanks-PNRao!

Keymaster Answered on July 26, 2016.
Add Comment

And this code will be much better than the previous one, as it will use formula and it will trigger if only changes in Column A:

Private Sub Worksheet_Change(ByVal Target As Range)
'Perform this task only if any changes in Column A
If Target.Column = 1 Then
'Clear the if name is empty
If Cells(Target.Row, 1) = "" Then
Cells(Target.Row, 6).ClearContents
Else
'This will enter the formula
Cells(Target.Row, 6).Formula = "=SUM(B" & Target.Row & ":E" & Target.Row & ")"
End If
End If
End Sub

Thanks-PNRao!

Keymaster Answered on July 26, 2016.
Add Comment

 

Can you tell me why this code is not working properly:

Private Sub Worksheet_Change(ByVal Target As Range)
'Perform this task only if any changes in Column A
If Target.Column = 1 Then
'Clear the if name is empty
If Cells(Target.Row, 1) = "" Then
Cells(Target.Row, 6).ClearContents
Cells(Target.Row, 7).ClearContents
Else
'This will enter the formula
Cells(Target.Row, 6).Formula = "=SUM(B" & Target.Row & ":E" & Target.Row & ")"
If Cells(Target.Row, 6).Formula >= 4 Then
Cells(Target.Row, 7) = "pass"
Else
Cells(Target.Row, 7) = "fail"
End If
End If
End If
End Sub
Top Contributor Answered on July 26, 2016.
Add Comment

Thank you so much!!

Top Contributor Answered on July 26, 2016.
Add Comment

Can you please provide us a link where you can try to learn how to use these kind of formulas in VBA like you had used above??

Top Contributor Answered on July 26, 2016.
Add Comment

You can google for formulas, but no one will teach the scenarios. However it will come with experience, keep on experimenting.

Best of Luck!

Thanks-PNRaoo!

Keymaster Answered on July 27, 2016.
Add Comment

I am not asking you to teach me the scenarios(no offence)…since you had written the code using sum and if statement, i was curious to know if the formulas like these can be written for other functions and statements. I thought your site might be having a link to this. anyways thanks!

Top Contributor Answered on July 27, 2016.
Add Comment

We have few topics on selected formulas with simple examples. Please check the below link:

http://analysistabs.com/excelformulas/

Thanks-PNRao!

Keymaster Answered on July 27, 2016.
Add Comment
 
Cells(Target.Row, 6).Formula = "=SUM(B" & Target.Row & ":E" & Target.Row & ")" 
Cells(Target.Row, 7).Formula = "=IF(F" & Target.Row & ">4,""Pass"",""Fail"")"
 
I was looking for these kind of  formulas in VBA and not the simple kind of arithmetic  excel formulas. anyways that’s ok!!
Top Contributor Answered on July 27, 2016.
Add Comment

Thanks, we will try to cover an article on this when we get the time.

Thanks-PNRao!

Keymaster Answered on July 27, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.