VBA Codes for looking for a column heading and find sum in another sheet

[Fusky]

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)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Fun1PersonA[/TD]
[TD]Fun2PersonA[/TD]
[TD]Fun1PersonB[/TD]
[TD]Fun2PersonB[/TD]
[TD]Fun1PersonC[/TD]
[TD]Fun2PersonC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Crit1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Crit1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Crit1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Crit1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Crit1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Crit2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Crit2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

 

[alansidman]

try this instead

Option Explicit


Sub Crit()
    Dim lc As Long, lr As Long, i As Long, j As Long
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim Pers As String, Crit As String
    Pers = InputBox("Which person to search?")
    Crit = InputBox("What is your criteria?")
    Dim res As Long    'assumes data are integers.  Change if not
    res = 0
    For i = 2 To lc
        If Cells(1, i) = Pers Then
            For j = 2 To lr
                If Range("A" & j) = Crit Then
                    res = res + Cells(j, i).Value
                End If
            Next j
        End If
    Next i
    MsgBox (res)
End Sub

 

[Fusky]

Thanks for ur codes @alansidman i have tired it but unforntunaltely not working

 

[alansidman]

What does this mean

but unfortunately not working

Cannot diagnose issue with a response like that. I created a spreadsheet that replicated yours and it worked for me. What is happening and what is not happening. Error messages?

 

[Fusky]

Yes, its return zeros only. what i would like to do is i have made a table in sheet 2 as a summary of sheet1, so example when i choose the column named Fun1personA and crit1 the program have to go and search the datas which are Fun1PersonA as well as Crit1. the datas may change the abit in future thats why i would like to use VBA and the columns may change also thast why i would like to use the column names as the refernce, Thanks

 

[alansidman]

Are the values in the cells actually numbers? Not just text that look like numbers? Are they integers or decimals. In order for this to work, they need to be formatted as numbers. Without seeing the actual file, I cannot diagnose the issue. The code was tested and works when the cells contain integers.

Additionally, the input is case sensitive.

 

[Fusky]

Are the values in the cells actually numbers? Not just text that look like numbers? Are they integers or decimals. In order for this to work, they need to be formatted as numbers. Without seeing the actual file, I cannot diagnose the issue. The code was tested and works when the cells contain integers.

Additionally, the input is case sensitive.

Yes all the datas in the file is numbers

 

[alansidman]

Suggest you upload a sample file to Box.net or similar location so that it can be analyzed to determine where you and I differ in the construction of the worksheet and either the sheet or the code be amended to work.