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

Fusky

New Member
Joined
Nov 17, 2016
Messages
15
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 example:(EXCEL file)
Fun1PersonAFun2PersonAFun1PersonBFun2PersonBFun1PersonCFun2PersonC
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,345
Office Version
  1. 365
Platform
  1. Windows
try this instead
Code:
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

New Member
Joined
Nov 17, 2016
Messages
15
Thanks for ur codes @alansidman i have tired it but unforntunaltely not working :(
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,345
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 17, 2016
Messages
15

ADVERTISEMENT

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

Well-known Member
Joined
Feb 26, 2007
Messages
6,345
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Fusky

New Member
Joined
Nov 17, 2016
Messages
15
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,345
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,435
Messages
5,675,848
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top