troubleshooting Question

In MS EXCEL VBA, I want to dynamically calculate count,sum,avg for a row as its added

Avatar of dmalovich
dmalovich asked on
Microsoft Excel
6 Comments1 Solution422 ViewsLast Modified:
In column A I have a list of names.  Columns B,C,D have AVG,SUM,COUNT.  COUNT and SUM get their values from column E,F,G,H and so on. Column B calculates the AVG from SUM and COUNT.   From time to time I add info to the sheet which includes names that already exist in column A and add new names if they don't exist in column A.  I would like to dynamically add the sum,avg,count to B,C,D as the sheet is updated.  I know this can be done with a cell formula but I don't want cells to show #DIV/0! or zeros if there is no information for them.  Can this be done with VBA instead?  here is sample vba:

      For y = 8 To last_cell
                If player_name = Worksheets("Current Year").Cells(y, 1) Then
                     Worksheets("Current Year").Cells(y, 5).Value = position
                     
                  '   what i would like to calculate
                      Worksheets("Current Year").Cells(y, 2).Value = AVG
                      Worksheets("Current Year").Cells(y, 3).Value = SUM
                      Worksheets("Current Year").Cells(y, 4).Value = COUNT of range ' E  "y" '  to  so on


                     player_exists = True
                End If
ASKER CERTIFIED SOLUTION
royhsiao

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros