Programming SendKeys

[Conan Kelly]

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

 

[Keith74]

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

Should get you started.

hth

Keith

 

[Dave D-C]

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

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

 

[SeanC UK]

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.

 

[DomThePom]

Hi Conan

You should generally avoid using sendkeys unless absolutely necessary - very
dodgy!

Here you don't need it anyway

Copy the code into a standard module of you workbook project and run the
insertValues sub using tools / macro / run

***********************************************

Sub InsertValues()

'Assumptions: selection contains formulas which commence
'=sum(range name or address)
'Action: replaces range name or address in sum function
'with constituaent values)

Dim cell As Range 'individual cells of selection
Dim rngCell As Range 'individual cells on summed range
Dim strRange As String 'summed range address
Dim rngRange 'summed range range
Dim intRightBrPos As Integer 'position of first right bracket in
formula

'for each cell selected
For Each cell In Selection.Cells

'if cell has a formula which starts with a sum
If Left(cell.Formula, 5) = "=SUM(" Then

'find the position of the right bracket to the sum function
intRightBrPos = InStr(1, cell.Formula, ")")

'extract the range name or address from the cell's formula
strRange = Mid(cell.Formula, 6, intRightBrPos - 6)

'define the range of values that we want to extract
Set rngRange = Range(strRange)
strRange = ""

'extract the values in teh range and substite the range or
address in the
'sum function
'with these values
For Each rngCell In rngRange.Cells
strRange = strRange & rngCell.Value & ", "
Next rngCell
strRange = Left(strRange, Len(strRange) - 2)
cell.Formula = "=sum(" & strRange & _
Right(cell.Formula, Len(cell.Formula) -
intRightBrPos + 1)
End If
Next cell
End Sub

*****************************************************

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