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.

Apr 7 in Others 66 views

## 1 answer to this question.

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):

• 38,010 points

## How to get rid of a #value error in Excel?

Changing the format to "Number" doesn't actually ...READ MORE

## How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

## Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE