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.
Bookmarks