Trouble getting results from Listobject Table Column when searching

[malcolmlyle]

I have a listobject, which is this data:

I'm trying to match a calculated wiresize and then select the next "usable" wiresize (not all wiresizes are available, so I have a column that the user can define what sizes they want to block). Also I have option to oversize the selection intentionally to compensate for voltage drop, for example.

Public Function NextWireSize(wSize As String, iOversize As Integer) As String

    Dim oSrchTbl As ListObject
    Dim i As Integer
    Dim iNrows As Integer
    Dim iUseRow As Integer
    Dim iMatchRow As Integer
    Dim rScolumn As Range
    Dim rUcolumn As Range
    Dim sSize As String
    
    ' define constants - set column numbers for table
    Const tSizeCol As Integer = 1       ' text size column
    Const tUseCol As Integer = 3        ' Boolean Column (hidden)
               
    ' initialize variables
    Set oSrchTbl = ThisWorkbook.Sheets("Options").ListObjects("WireSizesUse")
    
    With oSrchTbl
        Set rScolumn = .ListColumns(tSizeCol).DataBodyRange
        Set rUcolumn = .ListColumns(tUseCol).DataBodyRange
    
    NextWireSize = "Error"              ' default value
    iMatchRow = 0
    
    iNrows = oSrchTbl.DataBodyRange.Rows.Count
    ' find the current size
    For i = 1 To iNrows
        If sSize = rScolumn(i).Value Then
            iMatchRow = i               ' save row # of the match
            Exit For
        End If
    Next i

    If iMatchRow = 0 Then GoTo ErrorExit                     ' no match, return "Error"
    If (iMatchRow + iOversize) > iNrows Then GoTo ErrorExit  ' no possible match, return "Error"
    
    iUseRow = 0                                              ' default value for no match
    For i = (iMatchRow + iOversize) To iNrows   ' start at found row, and look for use match
        If rUcolumn(i).Value = True Then        ' this is the first match that's also TRUE
            iUseRow = i                         ' use this row, it's the next larger usable row
            Exit For
        End If
    Next i

    If iUseRow <> 0 Then NextWireSize = rScolumn(iUseRow)

    End With
    
ErrorExit:
End Function

The problem is, i'm not referencing this correctly as it's never finding a match and i'm not able to address the value of the data from tSizeCol (col 1).

I've tried using worksheet function match and find, but I can't get those to behave either. I'm lost on why this is not returning the value based on an index.

 

[NoSparks]

In this line shouldn't it be wSize

If sSize = rScolumn(i).Value Then

or should sSize be something derived from wSize

 

[NoSparks]

maybe like this

    ' find the current size
    sSize = "#" & wSize
    For i = 1 To iNrows
        If sSize = rScolumn(i).Value Then
            iMatchRow = i               ' save row # of the match
            Exit For
        End If
    Next i

 

[malcolmlyle]

maybe like this

    ' find the current size
    sSize = "#" & wSize
    For i = 1 To iNrows
        If sSize = rScolumn(i).Value Then
            iMatchRow = i               ' save row # of the match
            Exit For
        End If
    Next i

The wire sizes are stored as text strings with the "#" in front of them. The initial selection (based on ampacity requried) seeds the correctly formatted string from standard ampacity tables in the workbook, so I don't think this is a "#" issue.

 

[NoSparks]

glad you got it sorted out

 

[malcolmlyle]

In this line shouldn't it be wSize

If sSize = rScolumn(i).Value Then

or should sSize be something derived from wSize

I guess I'd been looking at it so long that I coudln't see the forest for the trees. Thanks for the QA/QC!