Results 1 to 2 of 2

Thread: VBA Beginner

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Beginner

    I copied this code from TFLYNN's EXCEL site. Can anybody tell me why it will not work.

    Sub CountNonBlankCells() 'Returns a count of non-blank cells in a selection
    Dim myCount As Integer 'using the CountA ws function (all non-blanks)
    myCount = Application.CountA(Selection)
    MsgBox "The number of non-blank cell(s) in this selection is : "_
    & myCount, vbInformation, "Count Cells"
    End Sub

    Thanks,
    Stephen

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA Beginner

    The procedure is fine, It is just the way it was copied and pasted.

    If you remove the _ and the end of line :
    MsgBox "The number of non-blank cell(s) in this selection is : "_ and also remove any carriage return there and it hould work.

    VBA places an underscore at the end of a line to indicate the the following text forms part of the same line of code or instruction. A carriage return must have found it's way into the code when you copied and pasted.

    The following should work :

    Sub CountNonBlankCells() 'Returns a count of non-blank cells in a selection
    Dim myCount As Integer 'using the CountA ws function (all non-blanks)
    myCount = Application.CountA(Selection)
    MsgBox "The number of non-blank cell(s) in this selection is : " & myCount, vbInformation, "Count Cells"
    End Sub


    Andrew C

Posting Permissions

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