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

Have a look at the VBA help for InStr and Mid.

Should get you started.

hth

Keith

Keith74 wrote:
Have a look at the VBA help for InStr and Mid.
Should get you started.
hth
Keith

[Your advice is right on, but I couldn't help
myself from seeing what it would take. So this
should be a GOOD start.]

Using SendKeys is the pits.
I would recommend parsing:

Sub testing()
Dim prngCell As Range, zCell2 As Range
Dim iPos1%, iPos2%, iPos3%, sFormula$
Dim sLeft$, sSheet$, sRange$, sMid$, sRight$, sSet$
For Each prngCell In Selection.Cells
sFormula = prngCell.Formula
iPos1 = InStr(1, sFormula, "'")
iPos2 = InStr(iPos1 + 1, sFormula, "'")
iPos3 = InStr(1, sFormula, ")")
sLeft = Left$(sFormula, iPos1 - 1)
sSheet = Mid$(sFormula, iPos1 + 1, iPos2 - iPos1 - 1)
sRange = Mid$(sFormula, iPos2 + 2, iPos3 - iPos2 - 2)
sRight = Mid$(sFormula, iPos3)
sSet = "" ' now build the set
For Each zCell2 In Sheets(sSheet).Range(sRange)
If sSet = "" Then
sSet = "{" & zCell2.Value
Else
sSet = sSet & "," & zCell2.Value
End If
Next zCell2
sSet = sSet & "}"
prngCell.Formula = sLeft & sSet & sRight
Next prngCell
End Sub ' Dave D-C

"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

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