excel配列関数ctrl+shift+ｅｎｔｅr

質問

いつも、大変お世話になっております。

vbaの記述で配列関数の部分が分からず投稿させていただきました。ご指導お願いいたします。

ご迷惑をおかけしないように、注意を払っておりますが、不行き等ございましたら、ご容赦ください。

具体的なコードと致しましては、下記です。

With ThisWorkbook.Worksheets("chart").Range("C4").Resize(myrows, 200)
.Formula = "=MIN(IF((作業NO=$A4)*(C$1<=作業日)*(作業日<=C$1+6),ＮＯ))"
End With

式を埋め込んで、一つ一つのセルを選択して「ctrl+shift+enter」ボタンを押せば、いいのですが手間なので、

自動的に一括して「ctrl+shift+enter」ボタンを押してもらえるような記述内容の変更の仕方をご指導いただけないでしょうか

当方、excel2000を使用しています。

よろしくお願いいたします。

回答No.3

keithin · ベストアンサー

とりあえず確実に簡単にできる方法としては，次のようなマクロにします。

with thisworkbook.worksheets("chart")
.range("C4").formulaarray = "=MIN(IF((作業NO=$A4)*(C$1<=作業日)*(作業日<=C$1+6),ＮＯ))"
.range("C4").copy destination:=.range("D4").resize(1, 199)
.range("C4").resize(1,200).copy destination:=.range("C5").resize(myrows-1, 200)
end with

マクロ以前にエクセル画面で，どうやってその式を使うかと言えば
１．Ｃ４セルに数式を記入してCtrl+Shift+Enterで入力する
２．右に下にコピー貼り付ける
という手順を行います。
上述は，この「出来る手順」をそのままマクロに仕立てたものです。

先に範囲を選択してその式を記入し，Ctrl+Shift+Enterしても，意図した計算結果は得られません。
つまり
セル範囲まとめて.formulaarray = "=今の配列数式"
というマクロを書いても，上手く行かないということです。

お礼

ありがとうございました。

同じセルにコピーの行き先(destination)を指定できればいいのに、と思いましたが。

やりたいことはできました。

ありがとうございました。

回答No.2

imogasi

ここの質問コーナーに答えている程度では、ＶＢＡで配列数式を問題にする例は無かった。
ＶＢＡでやるなら個々のセルをプログラムを組んで処理する、別の方法があるものだ。そちらを使うのが筋だろう。
配列数式の関数式をセルに埋め込むなど邪道だろう。
関数式だってＶＢＡでセルに埋め込もうとは思わない。
出来るとしても、凝った方法を使いすぎだと思う。質問者はそこまでＶＢＡの熟練者なのか。
他の方法ではできないのか。
ーー
配列数式を離れて、やりたいことをデータ例をあげて、説明してみたらどうすか。
読者に数式を読み解かせ、Ｒｅｓｉｚｅしてみたりして、なにをやりたいか意味がわかりにくい。
＞式を埋め込んで、一つ一つのセルを選択して「ctrl+shift+enter」ボタンを押せば、いいのですが手間なので、
なぜそうなるのか情況を説明してないのでは。？
ＭＩＮならＭＩＮを求めるセルだけで良いのでは。
ーーー
例えば、先日のわたしの回答例だが
Ａ２：Ｃ９の名前を「あ1」とつけた場合
Sub test01()
Worksheets("Sheet2").Range("a2:C9").FormulaArray = "=あ1"
End Sub
は、コピーと同じで
Sub test02()
Worksheets("Sheet1").Range("a2").CurrentRegion.Copy Worksheets("Sheet2").Range("a2")
End Sub
出できると思う。
ーー
よくある条件付き加算の場合
例データ
Ａ２：Ｂ１０
a 1
b 2
c 3
d 4
a 5
d 6
c 7
a 8
s 9
で、aの行のＢ列を足すのは、配列数式操作は　例えばＣ１２セルに、=SUM(IF(A2:A10="a",B2:B10,0))　と入れて　Ｓｈｉｆｔ＋ＣＴＲＬ＋ＥＮＴＥＲで１４になるが、
例えばＤ１２にＶＢＡでセットする場合は　
Sub test03()
Range("D12").FormulaArray = "=SUM(IF(A2:A10=""a"",B2:B10,0))"
End Sub
の実行で出来た。
こういうのは参考になるかな？
質問の場合はどううまく行かないのか。

お礼

ありがとうございました。参考になりました。

レベルを上げて、質問の仕方をあげていきたいと思います。

回答No.1

okormazd

.Formula = "=MIN(IF((作業NO=$A4)*(C$1<=作業日)*(作業日<=C$1+6),ＮＯ))"
↓
.FormulaArray = "=MIN(IF((作業NO=$A4)*(C$1<=作業日)*(作業日<=C$1+6),ＮＯ))"

ということとは違いますか。

お礼

解答ありがとうございます。私の説明不足で、申し訳ございません。

ご教授いただいた記述（.FormulaArray）ですと、resizeしたほかのセルも全て同じ値(絶対参照）になってしまいます。

これを、解消するためには、Formula で式を埋め込むしかないと思うのですが、その後で、まとめて 各セルに「ctrl+shift+enter」ボタンをおすなんて、記述ができればと思います。