マクロをすっきりさせたい・・・

質問

いつもお世話になっております。
下記、マクロを組んだのですが、
簡潔にまとめるには、どうしたら良いでしょうか・・・
宜しくお願い致します。

Set 範囲 = Workbooks("A.xls").Worksheets("マスター").Range("A2:G4000")
ThisWorkbook.Activate
列番号 = 7
検索値 = (Worksheets("B").Range("B24"))
Range("D14").Value = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)

Set 範囲 = Workbooks("A.xls").Worksheets("マスター").Range("A2:G4000")
ThisWorkbook.Activate
列番号 = 7
検索値 = (Worksheets("B").Range("B25"))
Range("D15").Value = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)

Set 範囲 = Workbooks("A.xls").Worksheets("マスター").Range("A2:G4000")
ThisWorkbook.Activate
列番号 = 7
検索値 = (Worksheets("B").Range("B26"))
Range("D16").Value = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)

回答No.1

mc-aumsc · ベストアンサー

行番号を変数にしてFor～Nextでループさせてみるのはいかがでしょうか？
以下のような感じで…。

Dim I As Integer
For I=14 To 16
Set 範囲=Workbooks("A.xls").Worksheets("マスター").Range("A2:G4000")
This Workbook.Activate
列番号=7
検索値=(Worksheets("B").Cells(I+10,2))
Cells(I,4).Value=WorksheetFunction.VLookup(検索値,範囲,列番号,False)
Next I

たぶん、これで動くと思います。
ミスってたらごめんなさい。

お礼

ご回答有難うございます！
じつは、１４行ほどありまして。
あほほど、長かったのが、ものすごくすっきりです！

回答No.2

end-u

同じ変数を何回もSetし直さなくてもよいです。

Set 範囲 = Workbooks("A.xls").Worksheets("マスター").Range("A2:G4000")
ThisWorkbook.Activate
列番号 = 7

検索値 = Worksheets("B").Range("B24")
Range("D14").Value = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
検索値 = (Worksheets("B").Range("B25"))
Range("D15").Value = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
検索値 = (Worksheets("B").Range("B26"))
Range("D16").Value = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)

また、１回しか使わない変数なら直値で書いても良いかもしれません。
Range("D14").Value = WorksheetFunction.VLookup(Worksheets("B").Range("B24"), 範囲, 列番号, False)

しかしExcelの関数を利用するのであれば、まとめて数式をセットして値に直したほうが効率は良いです。
With Range("D14:D16")
　　.Formula = "=VLOOKUP(B!B24,'[A.xls]マスター'!$A$2:$G$4000,7, False)"
　　.Value = .Value
End With

変数を使用して文字列として繋ぐ事もできます。
'：
　　.Formula = "=VLOOKUP(B!B24," & 範囲.Address(external:=True) & ",7, False)"
'：

以下余談。
ワークシート関数は引数と使い方によっては配列の答えを返します。
（複数の答えをまとめて返せるという意味）
また、エラー結果を考慮するとApplication.VLookupを使ったほうが良い場合もあります。

Range("D14:D16").Value = Application.VLookup(Worksheets("B").Range("B24:B26"), _
　　　　　　　　　　　　　　　　　　　　　　 Workbooks("A.xls").Worksheets("マスター").Range("A2:G4000"), _
　　　　　　　　　　　　　　　　　　　　　　 7, False)

お礼

ご回答有難うございます。
検証にじかんがかかり、お礼が遅くなり申し訳ありません。

色々な方法を伝授して頂きありがとうございました！！