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

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!

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!

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!

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

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

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!

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!

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

http://analysistabs.com/excelformulas/

Thanks-PNRao!

Cells(Target.Row, 6).Formula = "=SUM(B" & Target.Row & ":E" & Target.Row & ")" Cells(Target.Row, 7).Formula = "=IF(F" & Target.Row & ">4,""Pass"",""Fail"")"

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

Thanks-PNRao!