Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    How to display No. of Rows and Sum value in MsgBox

    Hello.

    Macro Sum column 'D' and displays the total to MsgBox i also want to get the total no. of rows in MsgBox. Howto do that.


    Code:
    Sub Test()
        Dim Rng As Range
        Dim c As Range
        Set Rng = Range("D1:D" & Range("D1").End(xlDown).Row)
        Set c = Range("D1").End(xlDown).Offset(1, 0)
        c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
        MsgBox c
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Hoe about something like:
    MsgBox "Total of " & c & " in " & Rng.Rows.Count & " rows."

    Steve

  3. #3
    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
    Foncesa,

    OR

    Code:
    Option Explicit
    
    Sub TotD()
      
       Dim lDSum As Long
       
       lDSum = Application.WorksheetFunction.Sum(Range("D1:D10"))
       MsgBox "Sum of Col D: " & Format(lDSum, "####"),  _
                      vbOKOnly + vbInformation, "Total of Column D"
     
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Another option would be:
    MsgBox "Total of " & c.Text & " in " & Rng.Rows.Count & " rows."

    So that the number displayed in the msgbox is the same format as the cell the number is entered into

    Steve

Posting Permissions

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