Consulting

Results 1 to 9 of 9

Thread: Calculate multiple sums depending on cells

  1. #1
    VBAX Regular
    Joined
    Aug 2013
    Posts
    26
    Location

    Calculate multiple sums depending on cells

    Hi,

    this thread is the successor of this one.

    I want to sum up (K-) cells depending on the first symbol of another (C-) cell in the same line(s) and post it into an Outlook body.

    The cells could be
    C16=1a..., K16=5
    C17=1b..., K17=5
    mysum1=10
    Dim mysum1 As Integer
    Dim mysum2 As Integer
    ...
    For Each c In ws.Range("C16", ws.Cells(ws.Rows.Count, 3).End(xlup))
            If Left(c.Value, 1) = "1" Then
                mysum1 = xl.WorksheetFunction.sum(ws.Range("K16:K18"))
            ElseIf Left(c.Value, 1) = "2" Then
                mysum2 = xl.WorksheetFunction.sum(ws.Range("K19:K24"))
            ...
            ElseIf Left(c.Value, 1) = "45" Then
                mysum45 = xl.WorksheetFunction.sum(ws.Range("K28:K34"))
            End If
    Next
    ...
    .Body = "Bandbeschichtung: " & mysum1 _
            & vbCrLf & "Walz+Fin " & mysum2 _
    Obviously the part of the Range within the loop doesn't depend on the first symbol of the c-row so far, but anyways:
    It only posts mysum1, no other sum works.

    Thanks in advance for any help.

    André

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    For Each c In ws.Range("C16", ws.Cells(ws.Rows.Count, 3).End(xlup)) 
        .body ="Bandbeschichtung: " & xl.WorksheetFunction.sum(ws.Range(choose(val(Left(c.Value, 2)) mod 45 +1,"K28:K34",  "K16:K18","K19:K24")))
    Next

  3. #3
    VBAX Regular
    Joined
    Aug 2013
    Posts
    26
    Location
    Thanks for your reply.

    Just recognized the line "45" is likely to be missunderstood.
    It should be like "4" and/or "5". So any K-cell related to C-cells with first character 4 AND 5 should be summed up to mysum45 instead of first two characters 45, so (c.Value, 1) was right in this regard.
    I guess the MOD-Operator doesn't help my issue. There are six groups from 1-9. (Only six, cause there are some like mysum45)
    After googling I suppose it should be the OR-operator, like ElseIf Left(c.Value, 1) = "4" Or "5" Then... <- just checked it. Works fine.

    Would be nice your way to be able to write each "sum" (without diming and calculating it beforehand) in the body.
    But it seems If is not allowed within the body.
    The following shows a failure message: .body = "Bandbeschichtung: " & If Left(c.Value, 1) = "2" Then xl.WorksheetFunction.sum(ws.Range("K16:K18"))

    Why doesn't the For Each loop through all C's instead of calculating only mysum1?
    Last edited by Aschrum; 09-23-2013 at 06:43 AM.

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    If you write after the loop only the last value will be written.

    No need for any ElseIf
    You can write directly in .Body (unless you make a syntax error as you did).

    For Each cl In ws.columns(3).specialcells(2).offset(15).specialcells(2) 
        .body ="Bandbeschichtung: " & xl.WorksheetFunction.sum(ws.Range(choose(val(Left(cl.Value, 2)),"K16:K18","K19:K24","K28:K34","K28:K34"))) 
    Next

  5. #5
    VBAX Regular
    Joined
    Aug 2013
    Posts
    26
    Location
    Somehow this kills the complete mailitem.
    .Body = "Bandbeschichtung: " & xl.WorksheetFunction.sum(ws.Range(Choose(Val(Left(c.Value, 1)), "K16:K18", "K19:K24", "K28:K34", "K28:K34")))

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    It doesn't; test the code using

    msgbox  "Bandbeschichtung: " & xl.WorksheetFunction.sum(ws.Range(Choose(Val(Left(c.Value, 1)), "K16:K18", "K19:K24", "K28:K34", "K28:K34")))

  7. #7
    VBAX Regular
    Joined
    Aug 2013
    Posts
    26
    Location
    Doesn't work here either. Yes, I've tried it within For each... Next
    Is some of the code not working in Office 2007?

    This works, but doesn't help the issue.
    MsgBox (xl.WorksheetFunction.sum(ws.Range("K16:K18")))

    By the way: I think the "choose"-way is a bad track.
    It has no indication regarding the first symbol in C to its K-cells. As a consequence it shouldn't be possible to do the final step as an automated "selection"/addition of what I've tried to explain here
    The cells could be
    C16=1a..., K16=5
    C17=1b..., K17=5
    mysum1=10
    Last edited by Aschrum; 09-25-2013 at 01:23 AM.

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Since you are providing too little information it's not possible to give any sound advice.
    Post a sample workbook please.

  9. #9
    VBAX Regular
    Joined
    Aug 2013
    Posts
    26
    Location
    Yeah, sure.

    example.xlsm
    I didn't finish it the list. It should end with a C-cell containing a 8 or 9 as first symbol.

    As already said there are six groups. 1, 2, 3, 4&5, 6&7, 8&9.
    It is possible that a group doesn't appear in some weeks.
    Moreover the cells aren't fixed. There could also be ten C-cells beginning with "1" or as said none.
    Therefor the sums shouldn't contain fixed cells like "K16:K18" but instead.

    In this example SUM1=(K16:18)=8+2+2 whereas "1" in "SUM" is for the first symbol in C.

    Did you get me now?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •