Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Need to get results in message box

    Hi,

    This codes Displays Message Box from Row (B2) onwards in Sheet2 (named :Level2)
    Code:
    Sub SumTotal()
        Dim Rng As Range
        Dim c As Range
        Worksheets("Level2").Select
        Set Rng = Range("B2:B" & Range("B2").End(xlDown).Row)
        Set c = Range("B2").End(xlDown).Offset(1, 0)
        c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
        MsgBox "Total of " & c.Text & " in " & Rng.Rows.Count & " rows."
    End Sub
    Help needed to get this 2 Columns Rows result in the same MsgBox.

    Total Rows count of Column A starting from Row 3 (A3) in Sheet2 (named :Level2) - result of only rows effected

    Total Rows Count of Column A starting from Row 2 (A2) from Sheet1 (named :Level1) - result of only rows effected

    Thanks in advance.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Carmine,

    I'm not sure if I'm reading your question properly but you can try this:
    Code:
    Sub SumTotal()
        Dim Rng As Range
        Dim c As Range
        Worksheets("Level2").Select
        Set Rng = Range("B2:B" & Range("B2").End(xlDown).Row)
        Set c = Range("B2").End(xlDown).Offset(1, 0)
        c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
        MsgBox "Total of " & format(c.value,"#,###.00") & " in " & Rng.Rows.Count & " rows."
    End Sub
    If this is not what you want please post a sample workbook.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks RG, I have attached the sample file along with results, Please have a look.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi carmine

    ..see attached workbook.
    I used this:
    Code:
    Sub showMessage()
    
    Worksheets("Level2").Select
    zLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    temp = "b2:b" & zLastRow
    zTotalLevel2 = Application.Sum(Range(temp))
    saywhat = "Total of " & zTotalLevel2 & " in " & (zLastRow - 1) & " rows."
    saywhat = saywhat & vbCr
    saywhat = saywhat & "Total of Deal Number " & (zLastRow - 2)
    saywhat = saywhat & vbCr
    
    zLastRow = Worksheets("Level1").Cells(Rows.Count, "A").End(xlUp).Row
    zCount = zLastRow - 1
    saywhat = saywhat & "Total Deals (Level1) : " & zCount
    
    boxtitle = "Level1 and Level2 Summary"
    btns = vbOKOnly + vbinform
    answer = MsgBox(saywhat, btns, boxtitle)
    
    End Sub
    zeddy
    Attached Files Attached Files

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Carmine,

    Here is yet another way to accomplish your task.

    HTH,
    Maud

    Carmine.png

    Code:
    Sub SumTotal()
    '---------------------------------------
    'DECLARE AND SET VARIABLES
        Dim ws1 As Worksheet, ws2 As Worksheet
        Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
        Dim c As Range, message As String
        Set ws1 = Worksheets("Level1")
        Set ws2 = Worksheets("Level2")
    '---------------------------------------
    'SET RANGES
        Set Rng1 = ws2.Range("B2:B" & ws2.Range("B2").End(xlDown).Row)
        Set Rng2 = ws2.Range("A3:A" & ws2.Range("A2").End(xlDown).Row)
        Set Rng3 = ws1.Range("A2:A" & ws1.Range("A2").End(xlDown).Row)
        Set c = Rng1(Rng1.Count + 1)
    '---------------------------------------
    'BUILD SUM FORMULA AND MESSAGE
        ws2.Select
        c.Formula = "=SUM(" & Rng1.Address(False, False) & ")"
        message = "Total of " & c.Text & " in " & Rng1.Rows.Count & " rows." & Chr(13) & _
                  "Total Deal Number " & Rng2.Count & Chr(13) & _
                  "Total Deals " & Rng3.Count
        MsgBox message
    End Sub
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks Zeddy.

    Thanks Maud.

    You both ROCK the forum.

    Thanks.

Posting Permissions

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