Arranging summation of values in rows based on user input

S

sahaider

New Member
Joined
May 30, 2014
Messages
35
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

[TABLE="width: 914"]
<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>[TR]
[TD="class: xl63, width: 106, bgcolor: aqua"]SERIAL[/TD]
[TD="class: xl63, width: 86, bgcolor: aqua"]CAT1[/TD]
[TD="class: xl63, width: 86, bgcolor: aqua"]CAT2[/TD]
[TD="class: xl63, width: 117, bgcolor: aqua"]CAT3[/TD]
[TD="class: xl63, width: 117, bgcolor: aqua"]CAT4[/TD]
[TD="class: xl63, width: 117, bgcolor: aqua"]CAT5[/TD]
[TD="class: xl63, width: 117, bgcolor: aqua"]CAT6[/TD]
[TD="class: xl63, width: 117, bgcolor: aqua"]CAT7[/TD]
[TD="class: xl63, width: 117, bgcolor: aqua"]CAT8[/TD]
[TD="class: xl63, width: 117, bgcolor: aqua"]CAT9[/TD]
[TD="class: xl63, width: 117, bgcolor: aqua"]CAT10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]10[/TD]
[TD="class: xl64, bgcolor: transparent"]23[/TD]
[TD="class: xl64, bgcolor: transparent"]18618[/TD]
[TD="class: xl64, bgcolor: transparent"]13544[/TD]
[TD="class: xl64, bgcolor: transparent"]2067[/TD]
[TD="class: xl64, bgcolor: transparent"]39885[/TD]
[TD="class: xl64, bgcolor: transparent"]89991[/TD]
[TD="class: xl64, bgcolor: transparent"]29621[/TD]
[TD="class: xl64, bgcolor: transparent"]6786[/TD]
[TD="class: xl64, bgcolor: transparent"]342[/TD]
[TD="class: xl64, bgcolor: transparent"]29[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]20[/TD]
[TD="class: xl64, bgcolor: transparent"]420[/TD]
[TD="class: xl64, bgcolor: transparent"]36320[/TD]
[TD="class: xl64, bgcolor: transparent"]126353[/TD]
[TD="class: xl64, bgcolor: transparent"]17226[/TD]
[TD="class: xl64, bgcolor: transparent"]10054[/TD]
[TD="class: xl64, bgcolor: transparent"]8228[/TD]
[TD="class: xl64, bgcolor: transparent"]1999[/TD]
[TD="class: xl64, bgcolor: transparent"]4481[/TD]
[TD="class: xl64, bgcolor: transparent"]1465[/TD]
[TD="class: xl64, bgcolor: transparent"]166[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]30[/TD]
[TD="class: xl64, bgcolor: transparent"]84[/TD]
[TD="class: xl64, bgcolor: transparent"]24752[/TD]
[TD="class: xl64, bgcolor: transparent"]141772[/TD]
[TD="class: xl64, bgcolor: transparent"]98476[/TD]
[TD="class: xl64, bgcolor: transparent"]6691[/TD]
[TD="class: xl64, bgcolor: transparent"]12842[/TD]
[TD="class: xl64, bgcolor: transparent"]6567[/TD]
[TD="class: xl64, bgcolor: transparent"]6238[/TD]
[TD="class: xl64, bgcolor: transparent"]1981[/TD]
[TD="class: xl64, bgcolor: transparent"]18128[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]40[/TD]
[TD="class: xl64, bgcolor: transparent"]68[/TD]
[TD="class: xl64, bgcolor: transparent"]7472[/TD]
[TD="class: xl64, bgcolor: transparent"]9756[/TD]
[TD="class: xl64, bgcolor: transparent"]1033[/TD]
[TD="class: xl64, bgcolor: transparent"]22995[/TD]
[TD="class: xl64, bgcolor: transparent"]44055[/TD]
[TD="class: xl64, bgcolor: transparent"]15950[/TD]
[TD="class: xl64, bgcolor: transparent"]2667[/TD]
[TD="class: xl64, bgcolor: transparent"]152[/TD]
[TD="class: xl64, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]50[/TD]
[TD="class: xl64, bgcolor: transparent"]329[/TD]
[TD="class: xl64, bgcolor: transparent"]26072[/TD]
[TD="class: xl64, bgcolor: transparent"]57062[/TD]
[TD="class: xl64, bgcolor: transparent"]6604[/TD]
[TD="class: xl64, bgcolor: transparent"]5420[/TD]
[TD="class: xl64, bgcolor: transparent"]8087[/TD]
[TD="class: xl64, bgcolor: transparent"]1694[/TD]
[TD="class: xl64, bgcolor: transparent"]1213[/TD]
[TD="class: xl64, bgcolor: transparent"]1372[/TD]
[TD="class: xl64, bgcolor: transparent"]21[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]60[/TD]
[TD="class: xl64, bgcolor: transparent"]28[/TD]
[TD="class: xl64, bgcolor: transparent"]8637[/TD]
[TD="class: xl64, bgcolor: transparent"]62010[/TD]
[TD="class: xl64, bgcolor: transparent"]48530[/TD]
[TD="class: xl64, bgcolor: transparent"]3944[/TD]
[TD="class: xl64, bgcolor: transparent"]6947[/TD]
[TD="class: xl64, bgcolor: transparent"]9522[/TD]
[TD="class: xl64, bgcolor: transparent"]4044[/TD]
[TD="class: xl64, bgcolor: transparent"]638[/TD]
[TD="class: xl64, bgcolor: transparent"]7612[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]10[/TD]
[TD="class: xl64, bgcolor: transparent"]345[/TD]
[TD="class: xl64, bgcolor: transparent"]37391[/TD]
[TD="class: xl64, bgcolor: transparent"]80721[/TD]
[TD="class: xl64, bgcolor: transparent"]9293[/TD]
[TD="class: xl64, bgcolor: transparent"]6837[/TD]
[TD="class: xl64, bgcolor: transparent"]10730[/TD]
[TD="class: xl64, bgcolor: transparent"]1378[/TD]
[TD="class: xl64, bgcolor: transparent"]2252[/TD]
[TD="class: xl64, bgcolor: transparent"]514[/TD]
[TD="class: xl64, bgcolor: transparent"]18[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]20[/TD]
[TD="class: xl64, bgcolor: transparent"]79[/TD]
[TD="class: xl64, bgcolor: transparent"]8569[/TD]
[TD="class: xl64, bgcolor: transparent"]9134[/TD]
[TD="class: xl64, bgcolor: transparent"]1492[/TD]
[TD="class: xl64, bgcolor: transparent"]24237[/TD]
[TD="class: xl64, bgcolor: transparent"]32987[/TD]
[TD="class: xl64, bgcolor: transparent"]10025[/TD]
[TD="class: xl64, bgcolor: transparent"]3616[/TD]
[TD="class: xl64, bgcolor: transparent"]134[/TD]
[TD="class: xl64, bgcolor: transparent"]36[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]30[/TD]
[TD="class: xl64, bgcolor: transparent"]29[/TD]
[TD="class: xl64, bgcolor: transparent"]12484[/TD]
[TD="class: xl64, bgcolor: transparent"]72306[/TD]
[TD="class: xl64, bgcolor: transparent"]58798[/TD]
[TD="class: xl64, bgcolor: transparent"]4088[/TD]
[TD="class: xl64, bgcolor: transparent"]5472[/TD]
[TD="class: xl64, bgcolor: transparent"]3319[/TD]
[TD="class: xl64, bgcolor: transparent"]7185[/TD]
[TD="class: xl64, bgcolor: transparent"]1160[/TD]
[TD="class: xl64, bgcolor: transparent"]10220[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Not sure I fully understand what you are after, but see if this helps.
Code: 
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
 
Upvote 0
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
 
Upvote 0
sahaider said:
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
Click to expand...

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.
 
Last edited:
Upvote 0
This should cover the full range of your data. See if it gives better results.
Code: 
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.
 
Upvote 0
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:
[TABLE="width: 480"]
<colgroup><col width="64" style="width: 48pt;" span="10"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Here is the results that I got by entering 10 in the input box.

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

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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
Code: 
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
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

I
  • Question Question
Is it possible to insert/delete rows in a file and make other files automatically update their rows accordingly?
Replies
3
Views
453
jkpieterse
jkpieterse
R
  • Solved
Using VBA to achieve looping a user input form and consolidate the output into another worksheet
Replies
1
Views
872
Artik
Artik
T
  • Solved
VBA - Problem with Coding controls for values based on Dates
Replies
3
Views
847
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
Breaking long sequence of UPCs into separate rows based on user input
Replies
1
Views
505
swartzfeger
S
countryfan_nt
  • Question Question
Sending outlook msgs. with VB - image in body (changes bases on cell value)
Replies
5
Views
993
Domenic
D

Share this page

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
Back
Top