[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『該当しない所は空欄にしたい』（VBA初心者）
A ｜ B ｜ C ｜ D ｜ E ｜ F ｜ G ｜ H ｜
1 ｜個数 │商品 ｜担当｜価格｜ ｜ ｜ ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
2 ｜ 100 │菓子A｜ ｜ ｜ ｜担当｜価格 ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
3 ｜ 200 ｜菓子B｜ ｜ ｜菓子A｜山田｜1200 ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
4 ｜ 300 │菓子C｜ ｜ ｜菓子B｜高橋｜245 ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
5 ｜ 400 ｜菓子D｜ ｜ ｜菓子D｜田中｜5420 ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
6 ｜ 500 │菓子E｜ ｜ ｜菓子F｜上田｜10 ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
7 ｜ 600 ｜菓子F｜ ｜ ｜菓子H｜吉村｜4907 ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
8 ｜ 700 │菓子F｜ ｜ ｜ ｜ ｜ ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
9 ｜ 800 ｜菓子H｜ ｜ ｜ ｜ ｜ ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
10 ｜ 110 │菓子A｜ ｜ ｜ ｜ ｜ ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
11 ｜ 120 ｜菓子D｜ ｜ ｜ ｜ ｜ ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
12 ｜ 130 │菓子E｜ ｜ ｜ ｜ ｜ ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
13 ｜ 140 ｜菓子A｜ ｜ ｜ ｜ ｜ ｜
-----｜-----｜-----｜----｜----｜-----｜----｜-----｜
F列でC列が一致箇所にG、H列の情報をD、E列に反映させたいのですが
下記式だと該当しないセルに#N/Aと記載されてしまいます。
該当しないところは空欄にしたいのですが
どうすればよいでしょうか。
Sub test()
Range("C1:C13").SpecialCells(xlCellTypeConstants, 1).Offset(, 1).Formula = "=INDEX($G$3:$G$7,MATCH(B2,$F$3:$F$7,0))"
Range("C1:C13").SpecialCells(xlCellTypeConstants, 1).Offset(, 1).Formula = "=INDEX($H$3:$H$7,MATCH(B2,$F$3:$F$7,0))"
End Sub
＜ 使用 Excel：unknown、使用 OS：unknown ＞
（マナ） 2019/09/30(月) 21:21
セルに直接=IFERROR(INDEX($G$3:$G$7,MATCH(B2,$F$3:$F$7,0)),"")と入れた時は問題ないのですが
Sub test()
Range("C1:C13").SpecialCells(xlCellTypeConstants, 1).Offset(, 1).Formula = "==IFERROR(INDEX($G$3:$G$7,MATCH(B2,$F$3:$F$7,0)),"")"
End Sub
でマクロ実行すると
実行時エラー1004
アプリケーション定義またはオブジェクト定義のエラーです。
エラーメッセージがでます。
どうしてでしょうか。
（VBA初心者） 2019/10/01(火) 19:17
match、indexはセル範囲を自動で拡張してくれると思っているのだろうか。
（toyota） 2019/10/01(火) 20:34
（マナ） 2019/10/01(火) 21:17
横からですが、手作業でC1:C13を選択し、ctrl + G → セルの選択 → 定数、数値のみにチェック →OK という操作をしたときに、"該当するセルが見つかりません"なんて出たりしませんか？
（もこな２） 2019/10/01(火) 22:00
（マナ） 2019/10/01(火) 22:07
マナさん
回答ありがとうございます。
回答自粛されなくて全然OKです＾＾
（VBA初心者） 2019/10/03(木) 08:59
＞セルに直接=IFERROR(INDEX($G$3:$G$7,MATCH(B2,$F$3:$F$7,0)),"")と入れた時は問題ないのですが
それが、そもそも本当？ って感じなんですけども。
Matchの第一引数がB2になっていますが、B2って個数ですよ？
それとも提示されたレイアウトがズレていて、個数はA列なんですか？
（半平太） 2019/10/03(木) 09:35
■２
＞色々試してみたのですがどうすれば対策できるかわかりませんでした。
どのようなことを色々試してみたのか不明ですが、よくあるのはエラーを無視する（エラーが発生する場合はその処理を行わない）方法でしょうか
Sub test2()
On Error Resume Next
Range("C1:C13").SpecialCells(xlCellTypeConstants, 1).Offset(, 1).Formula = "〜"
On Error GoTo 0
End Sub
■３
数式の中身まで検証してませんけど
誤 〜.Formula = "==IFERROR(INDEX($G$3:$G$7,MATCH(B2,$F$3:$F$7,0)),"")" 正 〜.Formula = "=IFERROR(INDEX($G$3:$G$7,MATCH(B2,$F$3:$F$7,0)),"")"
ではありませんか?投稿時にミスっただけでしょうか？
（もこな２ ） 2019/10/03(木) 10:04
With Range(Range("C2"), Cells(Rows.Count, "C").End(xlUp))
With .Resize(, 2).Offset(, 1)
.Formula = "=INDEX(F$6:G$11,MATCH(C2,F$6:F$11,0),2)"
.Font.ColorIndex = xlColorIndexAutomatic
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Font.Color = vbWhite
On Error GoTo 0
End With
End With
End Sub
列の表記が違うようですね。
エラー値が表示されたセルを、
空欄にするなら、ジャンプ機能（SpecialCells）で見つかったセルに対して、
なにかすればいいのでは？
サンプルではフォントの色を白にして、「空白」のように見せてみました。
フォントを白くするなら、マクロで設定せず、
手動で条件付き書式設定で白くするよう設定してもいいかも知れません。
もちろんIferror関数などを使って、""（長さ0の文字列）を返して、
「空白」のようにみせるのも常套手段です。
ほんとに「空白」にするなら、「数式と値のクリア」をしないと、
「空白」とは呼べないと思います。
いずれにしても、C列でSpecialCellsでセルを検索して
数式を入れるセルを決めるというのは、
手順が違うように思います。
計算してみるまでエラー値になるかわからないし、
エラー値が表示されるのが不細工なので、
それを見せないようにしたいのですから。
（まっつわん） 2019/10/03(木) 16:14
____A_______B_______C_______D_______E_______F_______G_______H______I_____
1| 個数 商品 担当 価格
2| 100 菓子A 商品 ダミー 担当 価格
3| 200 菓子B 菓子A 山田 1200
4| 300 菓子C 菓子B ??橋 245
5| 400 菓子D 菓子D 田中 5420
6| 500 菓子E 菓子F 上田 10
7| 600 菓子F 菓子H 吉村 4907
8| 700 菓子F
9| 800 菓子H
10| 110 菓子A
11| 120 菓子D
12| 130 菓子E
13| 140 菓子A
↓になれば正解という理解でよいですか？
____A_______B_______C_______D_______E_______F_______G_______H______I_____
1| 個数 商品 担当 価格
2| 100 菓子A 山田 1200 商品 ダミー 担当 価格
3| 200 菓子B ??橋 245 菓子A 山田 1200
4| 300 菓子C 菓子B ??橋 245
5| 400 菓子D 田中 5420 菓子D 田中 5420
6| 500 菓子E 菓子F 上田 10
7| 600 菓子F 上田 10 菓子H 吉村 4907
8| 700 菓子F 上田 10
9| 800 菓子H 吉村 4907
10| 110 菓子A 山田 1200
11| 120 菓子D 田中 5420
12| 130 菓子E
13| 140 菓子A 山田 1200
その場合、手作業で考えてみると、
(1) D2セルに =IFERROR(VLOOKUP($C2,$F$2:$I$7,MATCH(D$1,$F$2:$I$2,0),FALSE),"") (2) D2セルの数式をD2〜E13に貼付
で出来ちゃう気がしますので、これってわざわざマクロでやることなのかな?なんて思います。
■５
順番が前後しちゃいますが、
＞VLOOKUPを使わない理由ですが、F列とG列の間に情報が追加される可能性がある為、matchとindexをつかってます。
上記の数式で示しているように列の特定にMATCH関数を使えば、VLOOKUP関数でも対応できますよ。
（INDEX関数の範囲を複数列にしても、同様の方法が使えます）
■６
「■1と■2」の補足
↓が見つからなかったのは、定数のうち【数値】を探しているからだと思われます。
Range("C1:C13").SpecialCells(xlCellTypeConstants, 1)
なので、↓のようにすれば良いと思います。
Range("C1:C13").SpecialCells(xlCellTypeConstants, 1 + 2)
Range("C1:C13").SpecialCells(xlCellTypeConstants, xlNumbers + xlTextValues )
■７
ただ、直し方を言っておいて何ですが、そもそもSpecialCells使う必要あるのかなぁとおもったり。
もし、単純に参照先に商品、担当、価格がある場合以外は空白になってほしいだけであれば、C列がブランクでもIFERROR関数でカバーできちゃので、単純に「D2〜cells(C列最終行,"E")」まで、上記の数式をいれちゃうのも手だと思います。
（もこな２ ） 2019/10/03(木) 17:21
＞アプリケーション定義またはオブジェクト定義のエラーです。
このエラーだけなら
> "==IFERROR(INDEX($G$3:$G$7,MATCH(B2,$F$3:$F$7,0)),"")"
^^↓^^ ^^↓^^
"=IFERROR(INDEX($G$3:$G$7,MATCH(B2,$F$3:$F$7,0)),"""")"
で、皆さんのご指摘もあわせると、こんな感じではだめですか
Range("C2:C13").SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=IFERROR(INDEX($G$3:$G$7,MATCH(C2,$F$3:$F$7,0)),"""")"
^^^^ ^^ ^^^
あるいは
Columns("B").SpecialCells(xlCellTypeConstants, 1).Offset(, 2).Formula = "=IFERROR(INDEX($G$3:$G$7,MATCH(C2,$F$3:$F$7,0)),"""")" ^^^^ ^^ ^^^
^^^^^^^^^^^ ^^^
わたしなら、A1形式でなく、R1C1形式を使いますが、
前回質問から想像すると、たぶん問題ないのかもしれません。
（マナ） 2019/10/03(木) 18:46
皆さんのご指摘通り、MATCHはC列なのに間違ってB列を質問の方で入れてしまいました。
しっかり確認すればよかったです。申し訳ありません。
半平太さん、もこな２さん、まっつわん、マナさん
丁寧に回答ありがとうございます。
エラーなく動きました。
助かりました。
=IFERROR(INDEX($G$3:$G$7,MATCH(C2,$F$3:$F$7,0)),"""")"
↑について修行し直します。
（VBA初心者） 2019/10/05(土) 12:08
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.