findで検索後、その下の行の値を別シートに参照させたい！

質問

いつも本当にお世話になっております。
タイトルのごとく、リストシートと表示シートがあり、表示シートに検索文字が表示してあります。
検索文字をリストシートから検索し、その下の行の値を表示シートに参照させたいのです。
わけがわからないながらも、作ってみました。

Dim i As Range
Dim C As String
Dim x As String
C = Worksheets("表示シート").Range("e6")
With Worksheets("リストシート")
Set i = Columns("B").Find(C)

End With
Sheets("表示シート").Select
Range("E6").Formula = "=リストシート!i.Offset(1, 1)"
　　 Range("E7").Formula = "=リストシート!i.Offset(1, 2)"
end sub

とすると、e6には、”リストシート!i.Offset(1, 1)”と表示されてしまいます。
行番号だけを認識し、Range("BX") や　Chells(x,1)みたいな形は無理でしょうか？
ご教示お願いいたします！

回答No.1

higekuman · ベストアンサー

> With Worksheets("リストシート")
> 　　Set i = Columns("B").Find(C)
> End With

これだと、With で括っている意味がありません。

With Worksheets("リストシート")
　　Set i = .Columns("B").Find(C)
End With

のようにしましょう。

そして、

With Sheets("表示シート")
　　.Range("E6").Value = i.Offset(1, 1).Value
　　.Range("E7").Value = i.Offset(1, 2).Value
End With

のようにすると、やりたいことが出来ると思います。

ところで、オフセットの値は(1, 1)、(1, 2)で大丈夫ですか？
(1, 0)、(1, 1)のような気もするんですけど。

お礼

higekumanさん！！いつもありがとうございます！！！
できましたぁ～！！
やった～！
ほんまにありがとうございました！

回答No.2

mt2008

非常に判りにくいのですが、「表示シート」のE6セルの値を、「リストシート」のB列から探して、その1つ下の行の値を、「表示シート」のE7セルに表示したい。

【リストシート】
A　B
　あ
　10
　い
　20
　う
　30

表示シート　のE6の値が「い」の時、E7に20を表示
と、いう事でしょうか？

この場合でしたら、無理にVBAでやることはありません。表示シートのE7に
　=INDEX(リストシート!B:B,MATCH(E6,リストシート!B:B,0)+1)

これでいけるはずです

お礼

mt2008様
お忙しいところ、ありがとうございました！！
なんて簡単なマクロ、関数なんですか！！
本当に助かりました！！