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.1
- 回答日時:
こんばんは!
列・行をオートフィルでコピーした場合、
行番号・列番号も増えていく関数をそのまま記載すれば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
No.2
- 回答日時:
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.3ベストアンサー
- 回答日時:
続けてお邪魔します。
エラー処理について・・・
安直な方法ですが
↓のコードにしてみてください。
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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルでエラーを無視して一番左側のセルの値を返したい 2 2023/07/27 13:06
- Excel(エクセル) ExcelVBAの、Index matchについて 2 2023/04/18 16:57
- Visual Basic(VBA) ExcelVBAで、index、match関数を使用して、指定範囲に出力したい 3 2022/10/18 21:53
- Excel(エクセル) マクロを簡潔にしたい 6 2022/09/16 10:37
- Excel(エクセル) エラーの原因が分からない 1 2023/03/21 11:13
- その他(Microsoft Office) Excel 2列の値を返す数式についてです 1 2022/11/23 22:59
- Excel(エクセル) INDEX関数とMATCH関数を使用し値を返す数式についてです 2 2022/04/20 13:40
- Excel(エクセル) エクセルの関数について 5 2023/01/26 15:26
- Excel(エクセル) Excel 組み合わせ関数の使い方を教えてください。 1 2023/08/11 17:45
- Excel(エクセル) エクセルからスプレッドシートに数式を移植で起こるエラーについて。 2 2023/08/05 10:56
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelのマクロについて教えてく...
-
ユーザーフォームに別シートか...
-
エクセルVBAについて
-
VBA レジストリの値の読み方に...
-
VBAの計算で@が出てしまう件
-
FileCopy時のエラー
-
【VBA】マクロの入ったファイル...
-
ExcelのVBAです。フォルダ内の...
-
Excel-VBAのmsgBox()の不思議
-
Excelのマクロでワードのテキス...
-
VBA listBoxから
-
VBA 複数条件の分岐処理の上手...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
Excel マクロについての相談
-
VBA実行後に元のセルに戻りたい
-
VBA listBoxについて
-
CADシステムに図面番号を入力し...
-
【ExcelVBA】インデックスが有...
-
VBA 別ブックからコピペしたい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel VBA 定義されたプロージ...
-
Excel-VBAのmsgBox()の不思議
-
【VBA】マクロの入ったファイル...
-
VBA 複数条件の分岐処理の上手...
-
現在のブックを閉じないで、マ...
-
VBAで各列の"+"と"o"の合計数を...
-
VBAに詳しい方教えてください。
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
ユーザーフォームに別シートか...
-
エクセルのマクロについて教え...
-
ExcelVBA シート名を複数セルか...
-
エクセルのマクロについて教え...
-
VBA listBoxから
-
Excelのマクロについて教えてく...
-
エクセルのマクロについて教え...
おすすめ情報