VBA search column heading in a sheet and return SUM in another sheet
<p>I would like to get datas from sheet 1 to sheet 2 with reference to the column headings With VBA.<br>
For example:(EXCEL file)<br>
<a href="https://i.stack.imgur.com/Pd6vs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Pd6vs.png" alt="enter image description here"></a></p>

<p>So if I want to find the sum of fun1 person A with criteria 1 the command have to go and find the heading “sum of fun 1” in sheet 1 and choose the datas that are only under criteria 1 and sum it up in sheet 2 cell D5. (By using column heading reference instead of cell reference. The table range is A2 : U80. thanks. </p>

<pre><code>Public Sub Match()




ThisWorkbook.Sheets("Sheet1").Activate


Range("Sheet2!B3") = Application.Sum(Application.Index(Range("A:G"), 0, Application.Match("Crit1" &amp; "Fun1personA", Range("A2:G2"), 0)))
End Sub
</code></pre>

<p>I have tried it codes but it failed. i know that i havnt include the Row reference for crit1 , but iam not sure how to apply that to the formula.</p>

<p>Can anyone help me with this ? Thanks in advance</p>