none
Arranging summation of values in rows based on user input RRS feed

  • 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.

    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
      
       

    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