VBA Array Question - It is filtering correctly for all metrics except for one and I am baffled.

[ironny90]

Basically, it is trying to filter a bunch of industrial/warehouse assets based on metrics such as NRA (net rentable area), Vintage (year built) and Clear Height. The weird thing is it is working for all metric except for Clear Height and I don't know why. For example, if I set Apply Max to Yes, it gives zero result. Array (1,1) = 0 and it gives a message saying no comp is found as built in the macro. If I filter manually, there are clearly some assets fit the metrics range, but they are not picked up by the macro for some reason. Filtering for other metrics such as Vintage and % Office work properly.

Can someone help? I can give more information if needed. Thanks!!

 

[NoSparks]

If I filter manually, there are clearly some assets fit the metrics range, but they are not picked up by the macro for some reason.

what macro?

 

[ironny90]

what macro?

    'Checking Characteristic metrics
    For x = LocLen To MetricLen + LocLen - 2
        If PropMetrics(x, 2) = True Then ' Min
            If PropMetrics(x, 0) > AllDataArr(y, x + 5) Then
                Found = False
                Exit For
            End If
        End If
        If PropMetrics(x, 3) = True Then 'Max
            If PropMetrics(x, 1) < AllDataArr(y, x + 5) Then
                Found = False
                Exit For
            End If
        End If
    Next

 

[ironny90]

This is the section that checks min and max. It works for all other metrics except for Clear Height. AllDataArr() has all the data stored. It is on another tab.

 

[ironny90]

Sub GetComps()

Dim answer, BU, x, y, z, count, FstRw, LstRw, PropRw, CurEnd As Long
Dim Found As Boolean
Dim FstCell, DataRng As Range
Dim PropMetrics()

answer = MsgBox("Have you set up your desired parameters?", vbQuestion + vbYesNo + vbDefaultButton2, "Please make sure to fill out the parameters")
If answer = vbNo Then
    Exit Sub
End If
' Speeds Up Code
Entry_Point
On Error Resume Next

BU = Range("D2").Value

Const LocLen As Long = 7
Const MetricLen As Long = 5

ReDim PropMetrics(LocLen + MetricLen - 1, 5)

'Location
For y = 0 To LocLen - 1
    For x = 0 To 5
        PropMetrics(y, x) = Cells(5 + y, 4 + x).Value
     Next
     If Cells(5 + y, 9).Value = "Yes" Then
        PropMetrics(y, 5) = True
    Else
        PropMetrics(y, 5) = False
    End If
Next

'Metrics
For y = 0 To MetricLen - 1
    For x = 0 To 1
        PropMetrics(y + LocLen, x) = Cells(14 + y, 5 + x).Value
     Next
    For x = 2 To 3
        If Cells(14 + y, 6 + x).Value = "Yes" Then
            PropMetrics(y + LocLen, x) = True
        Else
            PropMetrics(y + LocLen, x) = False
        End If
     Next
Next

Dim ValRng As Range
Dim AllDataArr()
Dim CompArr()
Dim TempArr()


With Sheets("Data Summary")
    Const DataMetrics As Long = 25 ' Number of metrics added to the Lease Comps from the Data Summary tab
    .ShowAllData
    
    Set FstCell = .Range("A1:C7").Find("#", , xlValues).Offset(1, 1)
    LstRw = .Range(FstCell, .Cells(4000, FstCell.Column)).Find(0, , xlValues, xlWhole).Row ' Finding last lease
    
    Set DataRng = .Range(FstCell, .Cells(LstRw, DataMetrics + FstCell.Column - 1))
End With

AllDataArr = DataRng

ReDim CompArr(1 To 2, 1 To DataMetrics - 2)

CurEnd = 1

For y = 1 To UBound(AllDataArr)
    Found = True
    
    'Determining if location parameters need to be checked, skip check if all are not included
    For x = 1 To LocLen
        If PropMetrics(x - 1, 5) = True Then
           Found = False
           Exit For
        End If
    Next
    
    If Found = False Then
        'Checking for Location
        For x = 1 To 2
            If PropMetrics(x - 1, 5) = True Then
                Found = False
                z = 0
                Do While z < 5
                    If PropMetrics(x - 1, z) = AllDataArr(y, x + 1) And Not PropMetrics(x - 1, z) = "" Then
                        Found = True
                        Exit Do
                    End If
                    z = z + 1
                Loop
                
                If Found = False Then GoTo NextLease
            End If
        Next
        
        For x = 3 To LocLen
            If PropMetrics(x - 1, 5) = True Then
                Found = False
                z = 0
                Do While z < 5
                    If PropMetrics(x - 1, z) = AllDataArr(y, x + 3) And Not PropMetrics(x - 1, z) = "" Then
                        Found = True
                        Exit Do
                    End If
                    z = z + 1
                Loop
                
                If Found = False Then GoTo NextLease
            End If
        Next
        
    End If
    
    
    'Checking Characteristic metrics
    For x = LocLen To MetricLen + LocLen - 2
        If PropMetrics(x, 2) = True Then ' Min
            If PropMetrics(x, 0) > AllDataArr(y, x + 5) Then
                Found = False
                Exit For
            End If
        End If
        If PropMetrics(x, 3) = True Then 'Max
            If PropMetrics(x, 1) < AllDataArr(y, x + 5) Then
                Found = False
                Exit For
            End If
        End If
    Next
    
    'Checking Proximity
    If PropMetrics(x, 2) = True Then ' Min
            If PropMetrics(x, 0) > AllDataArr(y, 11) Then
                Found = False
                Exit For
            End If
        End If
        If PropMetrics(x, 3) = True Then 'Max
            If PropMetrics(x, 1) < AllDataArr(y, 11) Then
                Found = False
                Exit For
            End If
        End If
    
    If Found = False Then GoTo NextLease

    
    For z = 1 To DataMetrics - 1
        CompArr(CurEnd, z) = AllDataArr(y, z) 'All Info
    Next
    
    CurEnd = CurEnd + 1
    CompArr = Application.Transpose(CompArr)
    ReDim Preserve CompArr(1 To DataMetrics - 2, 1 To CurEnd)
    CompArr = Application.Transpose(CompArr)
    
NextLease:
Next

If CompArr(1, 1) = 0 Then
    MsgBox ("No comps were found for this property with your desired parameters")
    Exit Sub
End If

 

[ironny90]

This is the full code before the numbers are brought to a dashboard sheet. I wonder if there is something wrong with transpose.

I inherited the macro from another guy but it seems like no one found out about this issue until recently. That guy has left so I have to figure out what is going on.

 

[ironny90]

After further testing, I think the issue is with the columns on the data tab. A couple of metrics are not aligned to the right columns.