Replies

Maudibe and RetiredGeek,

Thanks for your help. My knowledge is very limited when it comes to VBA. So, I took the non-VBA approach. That formula was exactly what I needed.

Maudibe, I did play with the worksheet you attached and I like the approach. It was much cleaner. But, I have a couple questions.

(1) If I made a change in column D (Yes or No) to test the code, I had to use the Shift F3 function to have the cell recalculate. I checked to ensure I had auto calculation turned-on and it was. So I’m not sure why the code does not auto calculate.

(2) I decided to insert a couple of columns before column A and the code stopped working. Again, just to get a feel for the impact it would have on the UDF. I assumed the line of code: If Cells(I,4)=”Yes” referenced the fourth column (column D) so I changed it to 6 to represent column F, but that did not work. I checked to ensure that the formula in the cell where I wanted the results did change the date range to reflect =RevCode(C1:C20).

Again, thanks to all for your help and quick response.

RetiredGeek,

Thank you. This isexactly what I was looking for.

bjulien,

Thank you for your comments. I understand the COUNT function; however, in my formula (OFFSET AND COUNT) I’m not counting the number of cells with numbers, I’m getting the value of the last cell in Column B. I can have 50 cells with numbers, but if the last cell (let’s say B51) has a value of 200, then is returned. When I insert the rows, my formula moves up to cell B49.

Thanks for the suggestions