Hi im new to VBA.I would like to get datas from sheet 1 to sheet 2 with reference to the column headings With VBA.
For exampleEXCEL file)
<tbody>
</tbody>
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.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Public Sub Match()
ThisWorkbook.Sheets("Sheet1").Activate
Range("Sheet2!B3") = Application.Sum(Application.Index(Range("A:G"), 0, Application.Match("Crit1" & "Fun1personA", Range("A2:G2"), 0)))
End Sub</code>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.trying to do somehthing like a summary table.
Can anyone help me with this ? Thanks in advance
For exampleEXCEL file)
Fun1PersonA | Fun2PersonA | Fun1PersonB | Fun2PersonB | Fun1PersonC | Fun2PersonC | ||
Crit1 | |||||||
Crit1 | |||||||
Crit1 | |||||||
Crit1 | |||||||
Crit1 | |||||||
Crit2 | |||||||
Crit2 | |||||||
<tbody>
</tbody>
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.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Public Sub Match()
ThisWorkbook.Sheets("Sheet1").Activate
Range("Sheet2!B3") = Application.Sum(Application.Index(Range("A:G"), 0, Application.Match("Crit1" & "Fun1personA", Range("A2:G2"), 0)))
End Sub</code>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.trying to do somehthing like a summary table.
Can anyone help me with this ? Thanks in advance