Macro to keep inserting table rows until #NA errors are reached in Columns K & L

[Dbroek]

Hi,
Is there a code or formula that will keep inserting formula rows below until #NA errors are reached in both columns K & L in Table2?

The idea here is to always keep an open Excel row in Table2 that is ready to receive data from Table1. I would like a solution that is dynamic or will automatically maintain an empty row at the bottom of Table2 as data is entered in the Table1 columns.

Right now in the attached worksheet, in row 8, there is a 2/12/20 date in cell J8 and #N/A errors in cells K8 and L8. As new rows with data are added to the bottom of Table1, row #8 of Table2 will eventually have numbers in the K & L column cells and I would like a new row #9 with #N/A errors in cells K9 and L9 to automatically appear.

I'm hoping knows of a macro solution that is able to keep adding on formulated table rows down the columns until both K and L column cells in the same row have #N/A errors in them.

[dangelor]

Possibly with the worksheet's change event...

Please Login or Register  to view this content.

or as a standard macro...

Please Login or Register  to view this content.

[Dbroek]

Hi dangelor,

The first macro works very well. Noticed, however, that I needed to make sure that the two targeted columns do NOT calculate with blanks or else the code will insert blank rows all the way to the bottom of the sheet.

I couldn't get the second one to work consistently. Not sure but it could be an error on my part in how I tried to work it.

[dangelor]

Not sure, but does this catch the blank rows?

Please Login or Register  to view this content.

[Dbroek]

Thanks for the suggestion.
It still works well when the table calculates to NA(). However, when the target cells calculate to blank, it still keeps inserting rows down.

[dangelor]

Any chance of uploading a workbook to analyze?


Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Dbroek]

Sorry this took so long. I was gone from the computer the past few days.


Here's the file. I had changed the formulas in the target columns to calculate to blank instead of NA(), then this happened to the worksheet.
If I kept it at NA(), it works fine.

Thanks for looking at this!

[dangelor]

This seems to work...

Please Login or Register  to view this content.

[Dbroek]

dangelor,

I appreciate your efforts. However, I'm not getting anything to happen with this code. Is it because I'm not doing something correctly? I keep going back to this but can't figure it out.

Here's the file I tried the macro with.

[dangelor]

The formulas for Count Avg and Duration Avg are different.

[Dbroek]

dangelor,
Turns out that the first macro you offered works just perfect for what I needed. Thank you!

The goal was to somehow get table 2 to update whenever data is entered in table 1. This macro does that. I finally figured out I should write the formulas in table 2 to calculate to either data or N/A errors. This allows Table2 to be used for charting purposes without having to manually enter data into Table2.

I hope others find this post useful for their purposes as well.

[dangelor]

Glad it's working!