VBA 水平方向に HLOOKUP関数

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

Sheet1の黄色に、枠を囲っているところにsheet２から、関数HLOOKUPで数値を引っ張って来たいと思っております。水平方向での処理なので、iを立てて処理することができないのでformulaのR1C1形式を使うのかと思いいろいろトライしましたがうまくいきません。どなたか具体的な処理を教えていただけないでしょうか。

添付しているJPEGの簡略図のように
sheet1の黄色部分（B6:E6)に,sheet1,2のa～d（B2:E2)を起点にして、sheet2の緑部分（B3:E3)をHlookup関数で
引っ張ってきたいのです。実際の場合はもう少しデータ量がcolumn数は変動があるので、column.countなどでiなどを立てて処理を行えたらと思っております。

No.3 回答者： tom04 回答日時： 2014/06/25 21:43

こんばんは！

横からお邪魔します。

＞sheet1の黄色部分（B6:E6)に,sheet1,2のa～d（B2:E2)を起点にして、
＞sheet2の緑部分（B3:E3)をHlookup関数で引っ張ってきたいのです

結局、Sheet2の最終行データをSheet1の最終行の下に表示させたい！というコトですかね？
もしそうであればHLOOKUP関数でなくても対応できると思います。
一例です。
尚、Sheet1・Sheet2の列数はいくつあっても良いのですが、同じ列数だとします。

Sub Sample2()
Dim lastCol As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
With Worksheets("Sheet1")
lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column '★Sheet1の1行目を最終列番号取得
.Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(, lastCol - 1).Value = _
wS.Cells(Rows.Count, "B").End(xlUp).Resize(, lastCol - 1).Value
End With
End Sub

こんな感じではどうでしょうか？m(_ _)m

No.2 回答者： satoron666 回答日時： 2014/04/30 09:53

質問いただいたので、再回答いたします。

んー、英語ばっかりのプログラムって苦手なんですよね（苦笑
分かりやすい？プログラムを書いてみました。

---------------------------------------
Sub データ取得()
Dim 範囲 As Range
Dim 検索値 As Variant
Dim シート2最終行 As Long
Dim シート1最終行 As Long
Dim 行番号 As Long
Dim 値 As String

シート1最終行 = ThisWorkbook.Worksheets("Sheet1").Range("A1").End(xlDown).Row
シート2最終行 = ThisWorkbook.Worksheets("Sheet2").Range("A1").End(xlDown).Row

Set 範囲 = ThisWorkbook.Worksheets("Sheet2").Range("A1:E" & 最終行)
行番号 = 3 '結果を出したい部分？の行番号　'必ず最終行をつかうなら、シート2最終行を使うのもあり。
検索値 = Worksheets("Sheet1").Range("B1") '検索値
値 = Application.WorksheetFunction.HLookup(検索値, 範囲, 行番号, False)
Worksheets("Sheet1").Range("B6").Value = 値

End Sub

----------------------------------------------
必要に応じて、行番号、検索値…そして、値を記入する部分を変更頂ければと思います。
上記例では、aを検索しています。
bを検索する場合は、
検索値 = Worksheets("Sheet1").Range("C1") '検索値
　 Worksheets("Sheet1").Range("C6").Value = 値
に変えていただけると良いかと思います。

あとはループ掛けたり…色々と編集してください＾＾
分かりにくいところがあったらきいてください。そして、もしかしたらバグもあるかもしれません（苦笑

No.1 回答者： satoron666 回答日時： 2014/04/25 09:26

=HLOOKUP(B1,Sheet2!$A$1:$E$3,2,0)

こういう感じですか？

また、行番号はROWですよ。

=HLOOKUP(B1,Sheet2!$A$1:$E$3,ROW(C6)-3,0)
などとしても良いかもしれません。

HLOOKUPは、
HLOOKUP(検索値、範囲、行数、検索の型）です。
R1C1では良くわからないですが…

この回答への補足

satoronさま

ありがとうございます。
ちなみに他の方に質問をしたら、

Sub sample()
Dim xAdr As String, xFormula As String
Const cFormula As String = "=HLOOKUP(B1,Sheet2!#Adr,#2,FALSE)"
With Worksheets("Sheet2")
With .Cells(1).CurrentRegion
xAdr = .Offset(, 1).Resize(, .Columns.Count - 1).Address
xFormula = Replace(Replace(cFormula, "#Adr", xAdr), "#2", .Rows.Count)
End With
End With

With Worksheets("Sheet1").Cells(1).CurrentRegion
.Resize(1, .Columns.Count - 1).Offset(.Rows.Count, 1).Formula = xFormula
End With
End Sub

と教えて頂いてなんとか動くのですが、もう少し簡潔に行かないものかと悩んでます。。

補足日時：2014/04/29 08:09