# Arranging summation of values in rows based on user input

• ### Question

• Hello,

Good Evening all.

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.

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.

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

Sunday, October 30, 2016 7:49 PM

### All replies

• Hi Syed,

For sum function in Excel, you could try to set the formula in Excel cells, something like below:

`Range("A1").Formula = "=SUM(" & Range(Cells(2, 1), Cells(3, 2)).Address(False, False) & ")"`

If you want to sum function in VBA, you could try below:

`Application.WorksheetFunction.Sum(1, 2)`

Best Regards,

Edward

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, October 31, 2016 9:15 AM
• Thanks Edward,

How can I make sure each category value is summed up for a chosen serial number? I know it has to be a nested for or loop but any idea about the code?

Thanks

Syed

Monday, October 31, 2016 12:13 PM
• Hi Syed,

What do you mean by a chosen serial number? Will SUMIF function work for you?

I think one excel file which contains the source data, and your expected result would be much helpful, and you could upload it through OneDrive, then share us link here.

Best Regards,

Edward

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Tuesday, November 1, 2016 3:27 AM
• Hello Edward,

I can't share the link it is saying user needs to be verified?

By chosen serial number I meant user will input a serial number in a text box.for that serial number , lets say 10, all values for CAT1 are summed up and displayed, then for same serial number all values for CAT2 are summed up and displayed, and so on upto cat10.

Regards

Syed

Wednesday, November 2, 2016 9:55 PM
• Hi Syed,

Without checking your worksheet, I am afraid I am not able to understanding your business logic. I think you could loop through the range to count the value.

#Looping Through a Range of Cells [Excel 2003 VBA Language Reference]

https://msdn.microsoft.com/en-us/library/office/aa221353%28v=office.11%29.aspx?f=255&MSPPError=-2147217396

For link, I think you could paste like "social.msdn.microsoft.com/Forums/office" by removing http://.

Best Regards,

Edward

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, November 3, 2016 7:54 AM