ここから本文です

以下の条件にてVBAでVLOOKUPを使いたいのですが ・検索値の列及び行は可変です ...

アバター

ID非公開さん

2018/3/2617:02:42

以下の条件にてVBAでVLOOKUPを使いたいのですが
・検索値の列及び行は可変です
・参照元の列はE列までですが行は可変です
・#N/Aはブランクに置き換えます

っとすべて可変の場合の書きかたが分かりません。
以下は範囲指定で自動マクロを行った結果なのですが
これを可変対応とした場合はどうすれば宜しいでしょうか?

Sub test001()

Range("Q7").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(CONCATENATE(Q$4,$F7),syukei!$A$1:$H$1509,8,FALSE)"
Range("Q7").Select
Selection.AutoFill Destination:=Range("Q7:CB7"), Type:=xlFillDefault
Range("Q7:CB7").Select
Selection.AutoFill Destination:=Range("Q7:CB140"), Type:=xlFillDefault
Range("Q7:CB140").Select
Selection.Copy
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

閲覧数:
51
回答数:
3

違反報告

ベストアンサーに選ばれた回答

hel********さん

2018/3/2709:50:56

イメージ的な推測で記述しています。参考になれば・・。

Sub test001()
Dim Gyou As Long, Retu As Long, EndRow As Long
Gyou = Cells(Rows.Count, "F").End(xlUp).Row
Retu = Cells(4, Columns.Count).End(xlToLeft).Column
EndRow = Worksheets("syukei").Range("E" & Rows.Count).End(xlUp).Row
Range("Q7").Formula = "=VLOOKUP(CONCATENATE(Q$4,$F7),syukei!$A$1:$E$" & EndRow & ",8,FALSE)"
Range("Q7").AutoFill Destination:=Range(Cells(7, 17), Cells(7, Retu)), Type:=xlFillDefault
Range(Cells(7, 17), Cells(7, Retu)).AutoFill Destination:=Range(Cells(7, 17), Cells(Gyou, Retu)), Type:=xlFillDefault
Range(Cells(7, "Q"), Cells(Gyou, Retu)).Value = Range(Cells(7, "Q"), Cells(Gyou, Retu)).Value
Range(Cells(7, "Q"), Cells(Gyou, Retu)).Replace What:="#N/A", Replacement:=""
End Sub

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

アバター

質問した人からのコメント

2018/3/27 11:50:38

先ずはnotebooks20さんに意味不明な情報を出したにも
関わらず、不明な点を指摘頂きながらもお付き合い頂けたことを
感謝申し上げます。
またhelpnanodaさんのソースで課題解決し大変助かりました。
しっかりとソースの意味を理解するように致します。

ベストアンサー以外の回答

1〜2件/2件中

並び替え:回答日時の
新しい順
|古い順

msk********さん

2018/3/2708:59:13

>検索値の列及び行は可変です
⇒意味不明

>参照元の列はE列までですが行は可変です
⇒意味不明

>#N/Aはブランクに置き換えます
⇒IFERRO関数を組み込めば不要


例えば、ご例示のコードは以下の様な感じになります。

Sub sample()
With Range("Q7:CB140")
.Formula = _
"=IFERROR(VLOOKUP(Q$4&$F7,syukei!$A:$H,8,FALSE),"""")"
.Value = .Value
End With
End Sub

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

not********さん

2018/3/2708:18:56

申し訳ありませんが、想像力不足のため、不明な点が多く、答えられない内容が多いです。

以下、逆質問の羅列になり、すみません。


・検索値の列及び行は可変です・・・について

オートフィルを使っているので、(Q$4,$F7)の「Q」と「7行目」は、このままでも可変だと思いますが、違う変更なのですか?
例えば、選択したセルに対して相対的に4行目とF列という固定した番地も変更する必要があるとか?



・参照元の列はE列までですが行は可変です・・・について

参照元はA1~H1509の8列目=H列となっていますが、何がE列までなのですか?

そして、「行は可変」とは、1も1509も「$」が付いていて絶対番地(固定になっている)なのですが、1や1509が変わるという意味ですか?

その場合、その条件は?



・#N/Aはブランクに置き換えます ・・・について

これは、関数に組み込んだら解決します。

2行目の値を
「ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(CONCATENATE(Q$4,$F7),syukei!$A$1:$H$1509,8,FALSE),"""")"」と変更しましょう。

IFERRORを追加しました。
これは「エラーなら何も表示せず、エラーでないなら関数の答えを表示する」という関数です。

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

この質問につけられたタグ

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる