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

0 votes

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:

enter image description here

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) <> "" _
            DataRows = DataRows + 1
    ElseIf OW.Offset(Rowcnt) = "" _
            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 _
            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.


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, 2023 in Others by narikkadan
• 63,720 points

1 answer to this question.

0 votes

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:

Working version

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

Non-numeric entry

answered Apr 7, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

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

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

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

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

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

answered Oct 24, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

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

answered Jan 21, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

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

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

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

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP