For loop to add up areas of openings excluding certain rows keeps returning VALUE error

Question

My data, which can have any number of rows, looks like this and I need to add up the sizes of all the openings on a building that aren't on walls:

The code looks like this and is a user defined function because I need to be able to re-arrange the Sheet without it breaking:

Public Function PermiabilityPercentage(CW, OW, Width, Height)
'This Function runs down a list of data that contains a notation for which wall an opening is on and the width and height of the door and using _
A cell with the comparative notation sums all of the area for openings where the notation matches and makes a percantage of total area with all of the openings.

'CW=Checking wall cell _
OW= Top cell of opening wall getting checked _
Width= top cell of the width of openings _
Height= top cell of the height of the openings

Dim DataRows As Integer
Dim TotalRows As Integer: TotalRows = OW.End(xlDown).Row
Dim Rowcnt As Integer

For Rowcnt = 0 To TotalRows Step 1
    If OW.Offset(Rowcnt) <> "" _
        Then
            DataRows = DataRows + 1
    ElseIf OW.Offset(Rowcnt) = "" _
        Then
            Exit For
    End If
Next Rowcnt

Dim Row As Integer
Dim TotalArea As Integer

Dim TotalAreaOther As Integer

For Row = 0 To DataRows Step 1
    If OW.Offset(Row) <> CW _
        Then
            TotalAreaOther = TotalAreaOther + (Width.Offset(Row) * Height.Offset(Row))
    End If
Next Row

PermiabilityPercentage = TotalAreaOther
Exit Function

'For Row = 0 To DataRows Step 1
'    If OW.Offset(Row) = CW _
'        Then
'            TotalArea = TotalArea + (Width.Offset(Row) * Height.Offset(Row))
'    End If
'Next Row

End Function

The section that is commented out operates flawlessly and is quite similar to the section that produces TotalAreaOther, which is what I was investigating. I consistently received the error. Although this isn't the code's intended result, I do need these amounts to be completed.

currently

CW= A2
OW= A6
Width= D6
Height= E6

why does it keep giving me this error? all of the math should only be calculating numbers so I'm unsure why it is giving me a #VALUE! error.

Answer 1

I think this might be more of a data issue than a code issue: In one of the rows with Wall position "A," there is a non-numeric entry in either column D or column E. Its proper operation in rows other than "A" indicates that those rows all contain numerical data, thus a "A" row must be the issue. Be aware that even though it MAY APPEAR like a number to you, Excel may not classify it as such. Check the information in those columns.

I set up a condensed version of your data to show the problem. If every entry is a number, the formula is valid:

But when I put an "x" in D6 it generates the #VALUE! error. Excel expects 2 numeric values for the statement (Width.Offset(Row) * Height.Offset(Row)). The #VALUE! error indicates that one of those entries is non-numeric (the wrong value type):