SendKeys question?

[Tommy Flynn]

What's wrong with this sub?.....Sometimes it works, sometimes it doesn't.
I'm sure there is something obvious that I'm overlooking, but I don't see
it.

Thanks,
Tommy Flynn

Sub Send_Keys4()
Set myRng = Sheets(1).Range("B1:B36")
With myRng
.Select
.Formula = "=SQRT(A1:A36)"
End With
Application.SendKeys "{F2}^+~"
End Sub

 

[Bob Phillips]

Hi Tommy,

What are you trying to do?

SQRT(A1:A36) doesn't make sense, as far as I can see it will only
effectively do a SQRT(A1), even with making it an array formula.

By the way, it 'works' every time I tried it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tommy Flynn]

I was trying to enter an array formula with the SendKeys Method. I just
used the SQRT formula as an example. It works for me most of the time, but
sometimes for no reason, I get an error. I figured there was something
simple that I was missing.

 

[Ron de Bruin]

Hi Tommy

I was trying to enter an array formula

.FormulaArray = "=SQRT(A1:A36)"

 

[Bob Phillips]

Using your example, why not just use

Sub Send_Keys4()
Dim myRng As Range
Set myRng = Sheets(1).Range("B1:B36")
With myRng
.Select
.FormulaArray = "=SQRT(A1:A36)"
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tommy Flynn]

I was trying to help someone else with this. They told me that FormulaArray
would not work (for whatever reason) and I said that I would see if I could
do it with the SendKeys Method..... thus my question.

 

[Dick Kusleika]

Tommy

They're right, but it's because using SQRT as an array formula won't work.
What do they want to do? If they want to sum the square roots of a range
they need

..FormulaArray = "=SUM(SQRT(B1:B36))"

If they want to take the square root of the sum of the range, they need

..Formula = "=SQRT(SUM(B1:B36))"

 

[Tommy Flynn]

My question is how to enter array formulas using the SendKeys Method. The
particular formula is irrelevant. I just used the SQRT as a formula
example. Maybe that was a poor choice.

 

[Dick Kusleika]

Tommy

Gotcha. It worked for me, what happens when it doesn't work for you?

 

[Tommy Flynn]

I get an error message: "Search string must be specified" and it then opens
the Object Browser. It works most of the time, but every now and then I get
this message. Someone else in my workgroup uses this sub, and the same
thing happens to him occasionally, so it's not just me .

 

[teatree]

do you have the vbe open when you get the error? say, are you "Steppin
through" the macro with the vbe minimized in front of the worksheet?

if so, then the alt keys will be activating corresponding commands i
the vbe, not in excel..

if i run your macro in excel without "stepping thru" no problem,,, if
step thru, it opens the object browser in vbe

 

[Tommy Flynn]

Yep........ That's what's happening. I thought it had to be something
simple that I was overlooking.

Thanks,

Tommy Flynn