Results 1 to 4 of 4

Sum column that contains both numeric and text values

Threaded View

  1. #1
    Registered User
    Join Date
    01-12-2010
    Location
    Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    47

    Sum column that contains both numeric and text values

    I am going back over code that I wrote while learning VBA. There is a lot of what I call "brute force" code. The code works. Just extremely verbose. I know there are neater and more efficient ways to accomplish the task.

    Here is an example:

    I want to sum a column of values. The column contains both text and numeric values. I used the following code to accomplish the task:

    If ActiveSheet.Name = "THM" Then
    Range("B28") = "ND"
      If Trim(Range("B19")) <> "ND" Then
      Range("B28") = Range("B19")
      End If
    
      If Trim(Range("B21")) <> "ND" And Trim(Range("B28")) <> "ND" Then
        Range("B28") = Range("B21") + Range("B28")
      Else
        If Trim(Range("B21")) <> "ND" And Trim(Range("B28")) = "ND" Then
        Range("B28") = Range("B21")
        End If
      End If
    
      If Trim(Range("B23")) <> "ND" And Trim(Range("B28")) <> "ND" Then
        Range("B28") = Range("B23") + Range("B28")
      Else
        If Trim(Range("B23")) <> "ND" And Trim(Range("B28")) = "ND" Then
        Range("B28") = Range("B23")
        End If
      End If
    
       If Trim(Range("B25")) <> "ND" And Trim(Range("B28")) <> "ND" Then
        Range("B28") = Range("B25") + Range("B28")
       Else
        If Trim(Range("B25")) <> "ND" And Trim(Range("B28")) = "ND" Then
          Range("B28") = Range("B25")
        End If
      End If
    The result is that Range("B28") holds the sum of the numeric values B19:B25. Cells B20,22,24 are blank.
    If every cell in the named ranges contains ND - B28 contains ND.


    I have a strong feeling there is a much simpler way to accomplish the task.

    As always - thanks to all for looking and helping.
    Last edited by chromachem; 04-27-2010 at 09:08 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1