Macro to Automatically Calculate a New Row?

I have this formula for one of my columns =COUNTIFS($B$2:$B2,$B2,$A$2:$A2,">="&C2)

How can I create a VBA/macro so that when I enter a new row of data, it will automatically calculate the cell in this column? I don't want to have to keep dragging down the formula... Thanks

Originally Posted by JieJenn

You can probably just do it with formula. Take a look at this post.
http://www.ozgrid.com/forum/showthread.php?t=150363

I need the macro prompt...and probably some step by step instructions. I'm new to VBA/macros :-/

Originally Posted by sourabhg98

Attachment 489951

Whenever any changes occur in column A, B or C, column D is auto-Calculated. It works well for me.

So I got this to work, but it will only add a row above my last row of data, not after like I want. If I run the macro and click in the D column cell in the row in which I want to add data, nothing happens.. what am I doing wrong?


Sub AddRowCopyFormulaInColumnI()
ActiveCell.EntireRow.Insert
Cells(ActiveCell.Row + 1, "D").Copy Cells(ActiveCell.Row, "D")
End Sub

I have 19 rows of data. For Cell D2, my formula is =COUNTIFS($B$2:$B2,$B2,$A$2:$A2,">="&C2). For Cell D20, my formula is =COUNTIFS($B$2:$B20,$B20,$A$2:$A20,">="&C20)... so every new D column cell formula adjusts accordingly. I want to create a macro that when I enter a word into B21, cell D21 will automatically be calculated (so I don't have to drag down the formula every single time). I hope this better clarifies what I'm trying to do.

Originally Posted by sourabhg98

So in the file I attached, we have 4 rows of data, and when we enter anything in Cell A6, B6 or C6 the new result gets calculated in cell D6.

Isn't it what you are trying to achieve?

This is the data I'm working with (see attached). I'm wanted D to be automatically calculated everytime I enter a new product code row.

Originally Posted by sourabhg98

Copy paste this data in my sheet and try adding new data in the next row. The result will be auto-calculated.

I got that to work. One last question... and then thank you so much. Say I add two more columns of data. How can I run my macro so that it calculates BOTH D and F at the same time?

Originally Posted by raeinla

I got that to work. One last question... and then thank you so much. Say I add two more columns of data. How can I run my macro so that it calculates BOTH D and F at the same time?

Please Login or Register  to view this content.