別シートの単語と一致する2カ所のセルを見つけ出し、その場所から右に1つだけセルを動かす。 動かした先にあるセルの数値を返し、それぞれの間に挟まれた数値の合計を得るマクロを行いたいです。

Question

前提・実現したいこと

【vba】
別シートの単語と一致する2カ所のセルを見つけ出し、その場所から右に1つだけセルを動かす。
動かした先にあるセルの数値を返し、それぞれの間に挟まれた数値の合計を得るマクロを行いたいです。

[ 画像1 ]

[ 画像2 ]

実現したい事の詳細ですが、
①まず資料2の(E1)黄色背景のセルと(G1)オレンジ背景のセルに書かれた単語を、資料のＡ列から探し出す。
(今回の例ではE1がAさん、G1はHさん)
②見つけたセルから1つ右のセルをそれぞれ選択させる。
(今回の例では1.5と2.8)
③選択した2つのセルの間の数値を合計する。
④資料のE1に値を入力する。

AさんとHさんの数値の足し算は出来たのですが、その間の合計を導き出すコードが
どうしてもわかりません。
82.7という結果が出てほしいのですが・・・

発生している問題

それぞれ対応するセルを導き出すことまでは出来ましたが、その間の数値の合計が上手くいきません。

該当のソースコード

vba
ソースコード

Sub foundcell()

Application.ScreenUpdating = False

Dim Tango1 As Range
Dim Tango2 As Range
Dim One As Range
Dim Two As Range
Dim Kekka As Single

Set Tango1 = Worksheets("資料").Range("A:A").Find(Worksheets("資料2").Range("E1"))
Set One = Tango1.Offset(0, 1)
   '"資料"ワークシートのA列から"資料2"ワークシートのセル(E1)に書いてある単語と同じものを見つけ、その場所を"Tango1"とする
   '"Tango1"から右に１つセルを移動させた場所を"One"とする
   
Set Tango2 = Worksheets("資料").Range("A:A").Find(Worksheets("資料2").Range("G1"))
Set Two = Tango2.Offset(0, 1)
   '"資料"ワークシートのA列から"資料2"ワークシートのセル(G1)に書いてある単語と同じものを見つけ、その場所を"Tango2"とする
   '"Tango2"から右に１つセルを移動させた場所を"Two"とする
   
Kekka = WorksheetFunction.Sum(One + Two)
Worksheets("資料").Range("E1") = Kekka
   'セルOneからセルTwoまでの値を合計したものを"Kekka"とする
   '"資料"ワークシートのセル(E1)に"Kekka"の値を反映させる
   
   

End Sub

試したこと

Kekka = WorksheetFunction.Sum(One + Two)
Worksheets("資料").Range("E1") = Kekka

sum(One + Two)の部分を直せばうまくいくのかなと
思っておりますが、VBAの勉強は最近始めたばかりで、
頓珍漢なコードになっているかもしれません。
もっと実用的なコードに書き直せるのであれば、
ぜひとも勉強したいです。

ご教授頂けましたら幸いです。

補足情報（FW/ツールのバージョンなど）

初めて質問させてもらいます。
読みづらくて申し訳ありません。

どうかよろしくお願い致します。

Answer 1

VBAはhatena19さんの回答で良いと思います。
数式でも対応可能なのでそちらを回答します。

=SUM(INDIRECT(ADDRESS(MATCH(資料2!E1,A:A,0),2,1) & ":" & ADDRESS(MATCH(資料2!G1,A:A,0),2,1)))

Comments

[vpbyau]

迅速な対応ありがとうございます。
数式でも可能なんですね！今回教えて貰ったことを違う形で試してみます。
ありがとうございました！

Answer 2

    Kekka = WorksheetFunction.Sum(One + Two)

上記の部分を下記に修正すればいいでしょう。

    Kekka = WorksheetFunction.Sum(Range(One, Two))

Range(開始セル, 終了セル) で開始セルから終了セルまでの矩形範囲を返します。

Comments

[vpbyau]

迅速な対応ありがとうございます。
エラーも起きずに処理することができました！
別のデータでも応用することが出来、一歩前に進めました。
ありがとうございました。