Arranging summation of values in rows based on user input

[sahaider]

Hello,

I am trying to write a macro that will perform some summation of values based on user input.
I have some values arranged in columns for different categories like CAT1, CAT2, upto CAT10 for each different serial numbers arranged in rows, 10,20,30...etc
What I am trying to achieve is based on user input of serial number like 10,20 or 30 the macro will sum up all the category values for each category and placed them in separate cell. So if user enters serial 10 then for serial 10 all the values under CAT 1 are summed up displayed in a separate cell. Then for the same serial 10 all values of CAT2 are summed up displayed in a separate cell and this is repeated upto CAT10. If user inputs serial 20 similar summation is carried out and result displayed.

I am able to write some basic code which transposes the column labels into row labels but unable to display the sums for each serial number.
Any help appreciated please.

/CODE:
Sub Button1_Click()
Dim TA, cell, oldTA, newTA, p As Integer
Dim KPI As Characters

For i = 2 To 12

Worksheets("Sheet1").Cells(i + 4, 1) = Worksheets("Untitled_1").Cells(1, i)

Next

oldTA = 0

p = 2

cell = InputBox("Enter SERIAL NUM")

If cell = "" Then
Exit Sub

End If


Do

If Worksheets("Untitled_1").Cells(p, 1) = cell Then

TA = Worksheets("Untitled_1").Cells(p, 2)

End If


p = p + 1

newTA = TA + oldTA
oldTA = newTA

Loop Until Worksheets("Untitled_1").Cells(p, 6) = ""


End Sub
/CODE

SERIAL	CAT1	CAT2	CAT3	CAT4	CAT5	CAT6	CAT7	CAT8	CAT9	CAT10
10	23	18618	13544	2067	39885	89991	29621	6786	342	29
20	420	36320	126353	17226	10054	8228	1999	4481	1465	166
30	84	24752	141772	98476	6691	12842	6567	6238	1981	18128
40	68	7472	9756	1033	22995	44055	15950	2667	152	0
50	329	26072	57062	6604	5420	8087	1694	1213	1372	21
60	28	8637	62010	48530	3944	6947	9522	4044	638	7612
10	345	37391	80721	9293	6837	10730	1378	2252	514	18
20	79	8569	9134	1492	24237	32987	10025	3616	134	36
30	29	12484	72306	58798	4088	5472	3319	7185	1160	10220

<colgroup><col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3876;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;" span="2"> <col width="117" style="width: 88pt; mso-width-source: userset; mso-width-alt: 4278;" span="8"> <tbody> </tbody>

 

[JLGWhiz]

Not sure I fully understand what you are after, but see if this helps.

Sub t()
Dim srNbr As Long, lr As Long
lr = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
srNbr = Application.InputBox("Enter the serial number to summarize", "ENTER SERIAL NUMBER", Type:=1)
    If srNbr = False Then Exit Sub
    For i = 2 To 11
        With ActiveSheet
            .Cells(lr + 2, i) = Application.SumIf(.Range("$A$2:$A$10"), "=" & srNbr, .Range(.Cells(2, i), .Cells(lr, i)))
        End With
    Next
End Sub

 

[sahaider]

Hello JLGWhiz,
Thanks. sorry it didn't work. I am trying to get the total sum displayed in a different cell. so if user enters 10 as the chosen serial number I expect to see 4529 displayed for CAT1,then 566136 for CAT2 and so on. Similarly if user inputs 20 I expect 8680 for CAT1 and then 571310 for CAT2 values and so on.
Regards
Sahaider

 

[JLGWhiz]

Hello JLGWhiz,
Thanks. sorry it didn't work. I am trying to get the total sum displayed in a different cell. so if user enters 10 as the chosen serial number I expect to see 4529 displayed for CAT1,then 566136 for CAT2 and so on. Similarly if user inputs 20 I expect 8680 for CAT1 and then 571310 for CAT2 values and so on.
Regards
Sahaider

Where are you getting those totals from? the chart in the OP does not produce them. The whole column under CAT 1 in that chart would not add upt to 4529.
The code I used probably needs to be tweaked to cover your full data base if you have more rows. Again, you still have not specified where you want the results displayed.

 

[JLGWhiz]

This should cover the full range of your data. See if it gives better results.

Sub t2()
Dim srNbr As Long, lr As Long
lr = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
srNbr = Application.InputBox("Enter the serial number to summarize", "ENTER SERIAL NUMBER", Type:=1)
    If srNbr = False Then Exit Sub
    For i = 2 To 11
        With ActiveSheet
            .Cells(lr + 2, i) = Application.SumIf(.Range("$A$2:$A" & lr), "=" & srNbr, .Range(.Cells(2, i), .Cells(lr, i)))
        End With
    Next
End Sub

Also, if you can give a better explanation than "it's not working" and tell me what it does do or does not do, if there are error messages of whatever, it will be more helpful to develop code you can use.

 

[sahaider]

Hello,

To answer you question on 4529. for serial number 10 sum of all values in CAT1 column is 4529.The sum is displayed based on user input so if user enters serial 30, sum should be 1806 for CAT1.
At the moment the code you provided is producing a row of 0's like the below:

0

0

0

0

0

0

0

0

0

0

<colgroup><col width="64" style="width: 48pt;" span="10"> <tbody> </tbody>

 

[JLGWhiz]

Here is the results that I got by entering 10 in the input box.

SERIAL	CAT1	CAT2	CAT3	CAT4	CAT5	CAT6	CAT7	CAT8	CAT9	CAT10
10	23	18618	13544	2067	39885	89991	29621	6786	342	29
20	420	36320	126353	17226	10054	8228	1999	4481	1465	166
30	84	24752	141772	98476	6691	12842	6567	6238	1981	18128
40	68	7472	9756	1033	22995	44055	15950	2667	152	0
50	329	26072	57062	6604	5420	8087	1694	1213	1372	21
60	28	8637	62010	48530	3944	6947	9522	4044	638	7612
10	345	37391	80721	9293	6837	10730	1378	2252	514	18
20	79	8569	9134	1492	24237	32987	10025	3616	134	36
30	29	12484	72306	58798	4088	5472	3319	7185	1160	10220
	368	56009	94265	11360	46722	100721	30999	9038	856	47

<tbody>
</tbody>

I can't begin to guess what the difference is in your worksheet amd mine that would produce zeroes in yours, unless it is that the data with headers does not begin in cell A1.

 

[JLGWhiz]

I might add that the data is based on the chart in the OP and not on what your actual worksheet might contain. The results are accurate based on the posted chart. The code should have been installed in your standard code module 1, not a sheet or workbook code module.

 

[sahaider]

hello,
I think we are getting there.
SO to clarify when user inputs 10 in input box I do not want to display /calculate all CAT1 values for any other serial number , i.e 20, 30
all I want to see is for serial 10 sum of all CAT1 values ,then sum of all CAT2 values and so on upto CAT10 values.
So in the result displayed in Column Serial you will have 10 and sum of CAT1,sum of CAT2,sum of CAT3....upto sum of CAT10.
Thanks

 

[JLGWhiz]

I am not sure we are communicating very well. After reading post #9 several times, I think you might be asking for something like the code below. If not, then I don't know what you really want.

Sub t3()
Dim srNbr As Range, lr As Long, sh As Worksheet
lr = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set sh = ActiveSheet
sh.Range("A1", sh.Cells(Rows.Count, 1).End(xlUp)).AdvancedFilter xlFilterCopy, , sh.Range("N" & lr + 2), True
    For Each srNbr In sh.Range("N" & lr + 3, sh.Cells(Rows.Count, 14).End(xlUp))
        If sh.Cells(lr + 2, 1) = "" Then
            sh.Cells(lr + 2, 1) = srNbr.Value & ": Sum"
        Else
            sh.Cells(Rows.Count, 1).End(xlUp)(2) = srNbr.Value & ": Sum"
        End If
        For i = 1 To 10
            sh.Cells(Rows.Count, 1).End(xlUp).Offset(, i) = Application.SumIf(sh.Range("$A$2:$A" & lr), "=" & srNbr.Value, sh.Range(sh.Cells(2, i + 1), sh.Cells(lr, i + 1)))
        Next
    Next
    sh.Range("N" & lr + 2).CurrentRegion.ClearContents
End Sub