entering array formulae 1

[N1GHTEYES]

A colleague of mine has an odd problem with array formulae.

He is using a twin PC setup with single screen and single keyboard. He has a "KBM" switch to shift between the two PC's.

The system also monitors the keyboard for certain key combinations. Specifically, it gets into a muddle if he enters Ctrl<>Shift<>Enter. Consequently he can't use that method to enter array formulae.

Is there any other way of doing so?

Tony

 

[SkipVought]

Patient: "Doctor, my head hurts when I hit it with a hammer. Can you help me?"

Doctor: "Stop hitting your head with a hammer. Now that will be $150, please!"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Great minds think alike Skip. My first advice to him was "change your set-up".

Unfortunately, we are at the mercy of the company's choice of IT godlets. So I offered to see if any of the clever folk on TT knew of a subtle and little-known alternative way of entering AFs.

I'm perfectly prepared to go with my usual position of "if Skip can't do it, it can't be done" and just repeat my first advice.

Banging his head against the company procedures might be more useful than wishing Excel did something it does not do.

Tony

 

[SkipVought]

Well, let me say that your usual trust is not necessarily well founded, although it is deeply appreciated.

Just my shoot-from-the-lip Pavlovian and a bit irony as you revolve on the Ferris wheel of life.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

My experience has generally been that it is fairly well founded. What is appreciated is the help you've given over the years.

Tony

 

[N1GHTEYES]

Ahh - except in this case.

How about this:

1 Put the following macro behind a button:

Sub EnterArrayFormula()
with selection
  .formulaarray=cells(.row,.column).formula
end with
end sub

2 Type the formula you want in the top-left cell of the range you want to use
3 Select the entire range
4 Hit the button.

I've just tried it and it seems to work

Tony

 

[SkipVought]

And it has been a great encouragement for me, on this side of the pond, to see that your contributions have increased over the years, as well.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Just standing on the shoulders of giants. ;-)


Tony

(the worst part is all the dandruff...)