Hi Diddi

Rick's solution works but I'm still trying to learn VBA so if it does not take too long I would love to see your solution. To be honest Rick's code was too advance for me to understand and try to manipulate in the future, but it works.

Let me see if I can explain what my formula is doing. First off, the Evaluate function simply processes Excel formulas within the VBA environment, but before I can go on, we need to understand a little about Excel formulas. If you give a formula a range of values to deal with, Excel will perform whatever calculation applies to the range one cell at a time and return an array of values. Now you cannot always see that this is the case because formulas return single values to cells, but an array of values is still calculated behind the scenes. Here is an easy demonstration of this. Put a 1 in cell A1, a 2 in cell A2 and a 3 in cell A3. Next put this formula in cell B1...

=5*A1:A3

What is returned by the formula is 5, but the formula actually calculated 5 times each value in the three cells of A1:A3. You can see this by selecting cell B1 and then selecting everything except for the equal sign in the Formula Bar after which you should press the F9 Key. What you will see is this...

{5;10;15}

That is the array of values that Excel calculated for the formula, but it did not know how to show you all three values, so it showed you just the first value. Press the ESC Key to remove the displayed array and return the formula to the Formula Bar. Now select cells B1:B3, then click into the end of the formula in the Formula Bar and press CTRL+SHIFT+ENTER (that keystroke tells the formula to return its array values to the selected array of cells)... voila, the three values of the array you saw when you pressed the F9 Key are now displayed individually in the (array of) cells you had selected.

Okay, with that backdrop, let's return to my code. As I said earlier, the Evaluate function simply processes Excel formulas within the VBA environment. Since the Selection in the With statement is a range of vertical cells, Evaluate will calculate an array of values, one value for each of those cells in Selection. Those calculated values will be placed right back into the cells that the original values came from in a manner similar to what I showed you for the selection B1:B3 after pressing the CTRL+SHIFT+ENTER keystroke.

Now let's look at the formula itself as I assume those @ signs are confusing you somewhat. They are simply place-holders for the Selection's address. Instead of having to concatenate .Address into the formula three times, I put an @ symbol where the address would go and then used VBA's Replace function to put the actual Selection address directly into the formula at each position with the place-holder @ symbol was located.

I hope the above helps you in understanding how my code works.