Hi Conan,

Send Keys does have drawbacks, such as that described by yourself, and also
problems can occur when the window focus changes, so worth avoiding is you
can.

You can try this, I have tested it and it appears to do the job:

For Each prngCell In Selection.Cells
prngCell.Value = prngCell
Next

That should replace the formula in each cell with it's value, which is what
I believe you are trying to achieve.

An alternative would be to use the Paste Special Values method. When you
select the range of cells, copy them and then use Paste Special to paste the
values over the original range. This should be quicker than looping,
epsecially if you have many cells.

If I've got the wrong idea of what you are trying to achieve then let me
know and I'll try again!

Hope this helps,

Sean.

--
(please remember to click yes if replies you receive are helpful to you)


"Conan Kelly" wrote:

Hello all,

I have 20+ cells with fomulas relative to the following formula:

=SUM('Totals Inputs'!BH15:BJ15)-K15

I want to select the part that reads "'Totals Inputs'!BH15:BJ15" and hit the
[F9] key to replace the reference with the values in these 3 cells. I want
to do this for each cell, replacing the references relative to the one
given.

I tried using the following code to recreate the key sequence that would
accomplish this:





Sub testing()
Dim prngCell As Range

For Each prngCell In Selection.Cells
'prngCell.Activate
Application.SendKeys "{F2}", True
Application.SendKeys "^{RIGHT}", True
Application.SendKeys "{RIGHT}", True
Application.SendKeys "{RIGHT}", True
Application.SendKeys "+^{RIGHT}", True
Application.SendKeys "+^{RIGHT}", True
Application.SendKeys "+^{RIGHT}", True
Application.SendKeys "+^{RIGHT}", True
Application.SendKeys "{F9}", True
Application.SendKeys "{ENTER}", True
Next prngCell
End Sub





The results: XL ended up calculating the whole formula instead of just the
one reference (or it replaced the formula with the value).

I can't step through this code because the SendKeys command will go to the
VBE instead of XL.

Is this possible to do?

Thanks for any help anyone can provide,

Conan Kelly