ExcelセルにVBAでINDEX関数を入力

質問

ExcelでINDEX関数とMATCH関数で"B11:E13"セルに以下の数式データがあります。

数式は
"B11"=IF(ISERROR(INDEX($A$1:$A$9,MATCH(1,B$15:B$23,0))),"",INDEX($A$1:$A$9,MATCH(1,B$15:B$23,0)))

"B12"=IF(ISERROR(INDEX($A$1:$A$9,MATCH(2,B$15:B$23,0))),"",INDEX($A$1:$A$9,MATCH(2,B$15:B$23,0)))

"B13"=IF(ISERROR(INDEX($A$1:$A$9,MATCH(3,B$15:B$23,0))),"",INDEX($A$1:$A$9,MATCH(3,B$15:B$23,0)))

"C11"==IF(ISERROR(INDEX($A$1:$A$9,MATCH(1,C$15:C$23,0))),"",INDEX($A$1:$A$9,MATCH(1,C$15:C$23,0)))

"B15～B23"に数値1～3を入力した場合イニシャルA～I が入力される式ですが、列を連続で入力できるような処理をVBAのWorksheetFunctionで行うにはどのようにコードを記述すればよいのでしょうか。
ご回答のほどよろしくお願いします。

回答No.3

tom04 · ベストアンサー

続けてお邪魔します。

エラー処理について・・・

安直な方法ですが
↓のコードにしてみてください。

Sub Sample2()
Dim i As Long, j As Long
On Error Resume Next '←おまじないでこの行を追加
For j = 2 To 5
For i = 1 To 3
Cells(i + 10, j) = WorksheetFunction.Index(Range("A1:A9"), WorksheetFunction.Match _
(i, Range(Cells(15, j), Cells(23, j)), False))
Next i
Next j
End Sub

これで1～3のデータがない場合でも何とか対処できると思います。m(_ _)m

お礼

有難うございます。非常に勉強になり助かりました。

回答No.2

tom04

No.1です！

＞WorksheetFunctionで行うにはどのように・・・

を見逃していました。
WorksheetFunctionでもできなくはないですが、数式を入れる範囲の行・列の変数を宣言し
For～Nextでループしてやる必要があるのではないかと思います。

Sub Sample2()
Dim i As Long, j As Long
For j = 2 To 5
For i = 1 To 3
Cells(i + 10, j) = WorksheetFunction.Index(Range("A1:A9"), WorksheetFunction.Match _
(i, Range(Cells(15, j), Cells(23, j)), False))
Next i
Next j
End Sub

といった具合です。
エラー処理はしていません。
それよりもNo.1のようにワークシート関数をそのまま利用するほうが簡単だと思います。

ただし、前回のコードでは実データではなく関数によって表示されているデータになりますので、
実データにする場合は↓のコードに変更してみてください。

Sub Sample1()
With Range("B11:E13")
.Formula = "=IF(ISERROR(INDEX($A$1:$A$9,MATCH(ROW(A1),B$15:B$23,0))),"""",INDEX($A$1:$A$9,MATCH(ROW(A1),B$15:B$23,0)))"
.Value = .Value
End With
End Sub

何度も失礼しました。m(_ _)m

お礼

有難うございます。勉強になりました。

補足

上のコードFor～Nextの場合がセルに式が入らず次のセル値参照時に進めますが"B15"セル等にに数値が入っていなかった場合デバックエラーが表示されます。
"B11"セルは空白でも他のセルは入力可能になるようにエラー処理ができないものでしょうか。

回答No.1

tom04

こんばんは！
列・行をオートフィルでコピーした場合、
行番号・列番号も増えていく関数をそのまま記載すればOKだと思います。

まず、B11セルに入れる数式を考えます。
色々方法はありますが、仮に
=IF(ISERROR(INDEX($A$1:$A$9,MATCH(ROW(A1),B$15:B$23,0))),"",INDEX($A$1:$A$9,MATCH(ROW(A1),B$15:B$23,0)))
という数式にすれば行・列方向にオートフィルでお望みに結果になると思いますので、
それをそのまま利用します。

コードにすれば
Sub Sample1()
Range("B11:E13").Formula = "=IF(ISERROR(INDEX($A$1:$A$9,MATCH(ROW(A1),B$15:B$23,0))),"""",INDEX($A$1:$A$9,MATCH(ROW(A1),B$15:B$23,0)))"
End Sub

といった感じになると思います。

※　注意点としては　普通の関数の「空白」に！のところは「ダブルクォーテーション」を4個続けます。
こんなんではどうでしょうか？m(_ _)m

お礼

有難うございます。勉強になりました。

補足

できればセル自体に式のデータではなく値が入力したいのですが。