こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

vlookupで参照するシート名と範囲を変えたい

お世話になります。
vlookup関数について質問です。
新規作成する表の列B2からF2(1行目は項目行)に別のブック
「商品マスタ」からvlookupで必要なデータを検索して入力
B2からFのデータのある最終行までコピーするというマクロを作りました。

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,[商品マスタ.xlsx]商品マスタ2017.10.5!R2C1:R430C6,COLUMN(RC),0)"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:F2"), Type:=xlFillDefault
Range("B2:F2").Select

Range("B2").Select
Selection.AutoFill Destination:=Range("B2:F2"), Type:=xlFillDefault
Range("B2:F2").Select
Selection.AutoFill Destination:=Range("B2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
On Error Resume Next
With Range("B:F").SpecialCells(xlCellTypeFormulas)
.Value = .Value
End With

★商品マスタの内容を更新時に新規シートを追加しているので
《商品マスタ2017.10.5!R2C1:R430C6》この部分のシート名と参照範囲を
毎回手動で書き直していますが、シート名と参照範囲を自動で切り替える
方法がありましたら教えてください。
シートは最新版のデータが入ったものを常に使用します。
よろしくお願いします。

投稿日時 - 2018-02-25 16:28:50

QNo.9432604

困ってます

質問者が選んだベストアンサー

2種類作りました。
Macro1が新しい商品マスターが右端にある場合
Macro2がそうとは限らない場合です。
'
Option Explicit
'
Sub Macro1()
'
  Dim Row As Long
  Dim MSheet As Worksheet
'
  Row = Workbooks("商品マスタ.xlsx").Sheets.Count
'
  Set MSheet = Workbooks("商品マスタ.xlsx").Sheets(Row)
  Row = MSheet.Cells(Rows.Count, "A").End(xlUp).Row
  [B2] = "=VLOOKUP($A2,[商品マスタ.xlsx]" & MSheet.Name & "!$A$2:$F$" & Row & ",COLUMN(B2),0)"
  Row = Cells(Rows.Count, "A").End(xlUp).Row
  [B2].Copy Range("B2:F" & Row)
  With Range("B:F")
    .Value = .Value
  End With
End Sub
'
Sub Macro2()
'
  Dim Count As Long
  Dim Row As Long
  Dim SDate As String
  Dim Ndate As Date
  Dim Odate As Date
  Dim MSheet As Worksheet
'
  For Each MSheet In Workbooks("商品マスタ.xlsx").Sheets
    Count = Count + 1
    SDate = Mid(MSheet.Name, 6)
    SDate = Replace(SDate, ".", "/")
    Ndate = SDate
    Stop
'
    If Odate < Ndate Then
      Row = Count
      Odate = Ndate
    End If
  Next MSheet
'
  Set MSheet = Workbooks("商品マスタ.xlsx").Sheets(Row)
  Row = MSheet.Cells(Rows.Count, "A").End(xlUp).Row
  [B2] = "=VLOOKUP($A2,[商品マスタ.xlsx]" & MSheet.Name & "!$A$2:$F$" & Row & ",COLUMN(B2),0)"
  Row = Cells(Rows.Count, "A").End(xlUp).Row
  [B2].Copy Range("B2:F" & Row)
  With Range("B:F")
    .Value = .Value
  End With
End Sub

投稿日時 - 2018-02-25 23:48:05

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

既に回答が付いていまたね。確認せずに回答してしまいました。
一応間違いがあったので、書いておきます。
Stopは消して下さい。
何でここは回答を取り消せないんだ(ぼやき)

投稿日時 - 2018-02-25 23:55:38

ANo.4

「商品マスタ」ブックの新しいシートが常に一番目とかのルールだったら楽だったんですけどね。
シート名を全部なめて、シート名から「商品マスタ」を除いた残りの文字列が日付になるシート調べ、その日付が一番大きいシートのデータ範囲を調べる。
と、いう内容のFunctionを作ってみました。あなたのコードと同じ標準モジュールに置いてください。

Function Sample() As String
  Sample = "NODATA"
  dDate = DateValue("1900-1-1")
  nSel = 0
  With Workbooks("商品マスタ.xlsx")
    For i = 1 To .Sheets.Count
      sSName = .Worksheets(i).Name
      sSName = Replace(Replace(sSName, "商品マスタ", ""), ".", "-")
      If CDate(sSName) Then
        If dDate < DateValue(sSName) Then
          dDate = DateValue(sSName)
          nSel = i
        End If
      End If
    Next i
    If nSel > 0 Then
      Sample = .Worksheets(nSel).Name
      nRow = .Worksheets(nSel).Range("A2").End(xlDown).Row
      Sample = "=VLOOKUP(RC1,[商品マスタ.xlsx]" & Sample & "!R2C1:R" & nRow & "C6,COLUMN(RC),0)"
    End If
  End With
End Function

で、あなたのコードの一部を以下のように変更します。
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC1,[商品マスタ.xlsx]商品マスタ2017.10.5!R2C1:R430C6,COLUMN(RC),0)"

sFormula = Sample()
If sFormula = "NODATA" Then Exit Sub
ActiveCell.FormulaR1C1 = sFormula

投稿日時 - 2018-02-25 22:34:37

お礼

新しいシートは一番右に来るようにしています。
まずは書き込んでくださったコードで試してみますね。
分からない部分がありましたらまた質問します。
ありがとうございました。

投稿日時 - 2018-02-26 19:25:24

ANo.3

>3月1日更新したとすると
という日付は、どこかのシートのどこかに書き込まれているんですか?

>>シート名に書き込まれています
という返事はしないでくださいね。
シート名をVBAで求めるんですから。

それとも一番後方のシートとか、、
2枚目のシートだ!といた決まりがありますか?

投稿日時 - 2018-02-25 20:51:39

お礼

日付はシートの中には書き込まれていません。
更新したシートは一番後方になるようにしています。
説明が足らず分かりにくい質問になってしまいましたので
処理内容について整理し直してみます。
ありがとうございました。

投稿日時 - 2018-02-26 19:14:10

ANo.2

>これで分かるでしょうか?
いいえ、わかりません。

>《商品マスタ2017.10.5!R2C1:R430C6》この部分のシート名と参照範囲を
>毎回手動で書き直していますが
ここを自動化したいんですよね?

ならば、変化する部分は
《商品マスタYYYY.MM.DD!R2C1:RnnnC6》
と仮定すると

YYYY.MM.DD と nnn この2か所と思います。
この部分を何の情報をもとにVBAで求めればいいのかが
私にはわからないのです。

言い換えれば、質問者さんは、
YYYY.MM.DD と nnn この2か所を
何をもとに(あるいはどこを見て)書き換えているのかということです。

投稿日時 - 2018-02-25 19:48:46

お礼

補足の説明も情報が足らずにすみません。
どういう処理をしたいのか整理してまとめ直してみます。
ありがとうございました。

投稿日時 - 2018-02-26 19:06:07

ANo.1

>《商品マスタ2017.10.5!R2C1:R430C6》この部分のシート名と参照範囲を
>毎回手動で書き直していますが、シート名と参照範囲を自動で切り替える
>方法がありましたら教えてください

自動で切り替えるとの行、
どのようなルールで切り替えればいいのかがわかりません。

"=VLOOKUP(RC1,[商品マスタ.xlsx]商品マスタ2017.10.5!R2C1:R430C6,COLUMN(RC),0)"

例えば、"2017.10.5"を実行日に置き換える
 とか、
どこかのシートのどこかのセルに日付があり、
その日付に置き換える
 とか
が考えられますがいかがでしょうか。

また、
参照範囲を自動的に切り替えるという要望ですが
これも、どのように切り替えればいいですか?

例えば、
有効なデータが埋まっている範囲を指定することが考えられますが
だとすると、
有効範囲の1列目には、空白セルは無いという理解でいいですか?

言い換えれば、
有効範囲の最終行をどのような条件で判断すればいいのか?
ということです。

投稿日時 - 2018-02-25 17:52:39

補足

補足します。

《商品マスタ》シート名商品マスタxxx(日付)
A     B     C      D      E     F
1 商品番号  商品コード  品名1  品名2   棚No.  単価
2  abcd    12345  りんご Lサイズ    1    200

《作成する表》
A     B     C   D     E   F
1 商品番号 商品コード 品名1 品名2 棚No. 単価
2 abcd
 
このような表の構成になっていてA列にはデータがあります。
商品マスタは更新日付をシート名としていますので、3月1日更新
したとすると商品マスタ2018.3.1という名前のシートを追加します。

シートを追加する毎に新しいシート名のAからFのデータのある
範囲を参照させるようにしたいと思っています。
これで分かるでしょうか?上手く説明出来ずすみません。

投稿日時 - 2018-02-25 19:32:35

あなたにオススメの質問