Looping through Excel cell values to find a specific value and then once found add new row that contain sum calculations for all columns using VBA.

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
Need help with Microsoft Excel VBA macro that will add a row based on value found.  This is a 1 of 3 part series of questions I have.
Please note ee sample.xlsx (Sheet1 only)) file as I ask this question.   The ee samplefinished.xlsx is the final version that completes as my answer.

Basically I identify the latest year in column A. by finding the cell value of 'Curr' - storing the value of what the year is in a text variable - then add a new row.
Within the new row created, I add a plus +1 to that year and place it in column A.  and then ensure the totals below the new row contain the =sum equation.

You'll notice the =SUM equation in ee samplefinished.xlsx contains the newly created row 10 in Excel vs in contract ee sample.xlsx.  This is what I'm trying to accomplish.

Can someone please help me with some sample Excel VBA macro code that will make this work?  I'm including Microsoft Access VBA experts as well that probably has done this before.   Right now at this time do not want links to other internet forums/ articles that may guide me in the right direction to solve this but just purely some sample code I can copy and paste and see if this will work.   (Currently researching other solutions for other coding problems and don't have time to look it up).
ee-sample.xlsx
ee-samplefinished.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Use an Excel Table and you can have an automtic Total Row

Overview of Excel tables

Where is the data coming from? If it's only one line per year you could have a formula to increment the year which would run when you add a new row.
Try this macro

Cells.Find("Curr").EntireRow.Insert
Cells.Find("Curr").Offset(, 1).Resize(, 3).Formula2R1C1 = "=sum(r" & Cells.Find("Prior").Row & "c:r[-1]c)"

Author

Commented:
Roy, the data is static.  Just a column with values of years 1987 to 2018.

I tried changing it as Format as Table - however when I added the row - it doesn't automatically carry the Sum = values for the last row.  
For example in the sample file above:

If I insert a new row under year 2017 - that formula won't change to =SUM(B5:B10) - <B10 being the new row whether I have it formatted as a table or not.
Roy CoxGroup Finance Manager

Commented:
can you attach the file. You need to add the Total Row

  1. Click anywhere inside the table.
  2. Go to Table Tools > Design, and select the check box for Total Row.
  3. The Total Row is inserted at the bottom of your table. ...
  4. Select the column you want to total, then select an option from the drop-down list.

Author

Commented:
Roy, I attached the file above, ee-sample (not the one finished)
Did you try my macro?

Author

Commented:
I will today.  Sorry been busy with new job and having to move.

Author

Commented:
Thank you for your replies.  I was not able to get your suggestion to work Roy.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial