VBAについての質問です

質問

VBAの数式の設定で質問がございます。

各社員の勤務表にある毎月の各案件に対する作業時間を
１つの表にまとめたいと思います。

エクセルの場合は下記の数式となりますけど、人数と案件は多いですので、VBAを使って作業量を減らしたいと思います。

=HLOOKUP(A3,'[2014勤務表.xlsx]1月'!A1:D6,6,FALSE)

VBAについての書き方を教えてください！

よろしくお願いします！

回答No.2

eden3616

No1の回答において、VBAコードの追加方法ですが以下のように訂正します。

■VBAコードの追加
(1)Alt+F11でVBEを開く
(2)挿入→標準モジュールから新規標準モジュールを作成
(3)作成した標準モジュールに最下のVBAコードを貼付
(4)Alt+F11でVBEを閉じる

失礼しました。

回答No.1

eden3616

■はじめに

取得元のデータ『2014勤務表.xlsx』において、
『1月』シートのセル『A1：D6』のフォーマットが同じであるとして
ユーザー定義関数を作成しました。

■処理について

『花子1』以外の名前について取得データがどこに
どの様な位置関係で存在しているか提示されていませんので
以下のような処理を行っています。

「対象ブック」、「対象シート」の中の「名前」を検索し、そのセルを基準セルとします。
基準セルから開始及び終了セルまでのオフセット距離を指定
オフセット範囲とキーワードを用いてVlookupまたはHLookup関数により
取得した値を返します。

■動作要件・条件

動作条件として取得元の様式は以下の要件を満たす必要があります。
　(1)6行4列の範囲である
　(2)左上のセルに名前が入っている
　(3)名前の次の行が案件名である
また、内部でVlookupまたはHLookup関数を使用しています。
適応条件を満たさない様式の場合正しく動作致しません。

■VBAコードの追加

最下のVBAコードを以下の手順で集約先ブックへ追加してください。
　(1)シートを右クリック
　(2)コードの表示を選択
　(3)VBAコードを貼付け

■使用方法

対象のブックを開いている必要があります。
『花子1』の『案件1』『1～12月』を対象として説明します。

C3セルに以下の数式を入力して、C14セルまでオートフィルしてください。
=集計("2014勤務表.xlsx",C$1,$B3,$A3,1,0,5,3,5,"h",TRUE)

数式は引数が多いですが以下の様式になります。

=集計(ブック名,名前,月,案件名,開始行,開始列,終了行,終了列,シフト量,タイプ,検索の型)

ブック名　：　対象のブック名を指定
名前　：　基準セルを検索する名前のセルを指定
月　：　集計対象のシート名である月のセルを指定
案件名　：　Lookup関数で使用するキーを指定
開始行　：　基準セルからの開始セルへのオフセット行値
開始列　：　基準セルからの開始セルへのオフセット列値
終了行　：　基準セルからの終了セルへのオフセット行値
終了列　：　基準セルからの終了セルへのオフセット列値
シフト量　：　V or Hlookup関数で使用する行・列番号のオフセット量
タイプ　：　Lookup関数のタイプを「h」または「v」で指定
検索の型　：　Lookup関数で使用する検索の型をTrueかFalseで指定

■VBAコード

Function 集計(book As String, _
　　key1 As Range, key2 As Range, key3 As Range, _
　　ost_r1 As Integer, ost_c1 As Integer, _
　　ost_r2 As Integer, ost_c2 As Integer, _
　　sft As Integer, sw As String, opt As Boolean) As Variant
Dim myTar As Object
Dim myRng As Range
Set myTar = Workbooks(book).Sheets(key2.Value)
Set myTar = myTar.Cells.Find(key1)
If Not myTar Is Nothing Then
　　Set myRng = Workbooks(book).Sheets(key2.Value).Range( _
　　　　myTar.Offset(ost_r1, ost_c1), myTar.Offset(ost_r2, ost_c2))
　　If sw = "h" Then
　　　　集計 = WorksheetFunction.HLookup(key3.Value, myRng, sft, opt)
　　ElseIf sw = "v" Then
　　　　集計 = WorksheetFunction.VLookup(key3.Value, myRng, sft, opt)
　　End If
End If
End Function

お礼

詳しく教えて頂いて有難うございました。
助かりました。
これから試してみます。