Search code examples
excelvbalistobject

Finding value in table column and returning table row number - VBA

Background information:

I am trying to find a value in a Table column and for it to return the row number of that table.

The table name is "Type_K" and is on the "DATA" sheet, it looks like this:

enter image description here

From a user input I want to find the same value in the second column and then return the table row. This will be used on the "Pipe Costing" sheet. Here is the table the user is filling out: enter image description here The Material column has a dropdown list with 4 options, depending on that input the column for Type changes its dropdown list, same goes for the columns Wall and Size.

For this example, the User has chosen:

Material = Copper

Type = Type K

Wall (N/A in this case)

Size = 1/4"

The value from the Size column is the one I want to find in the DATA Table (the second column of the first image)

Currently the code checks for what the type is and return the correct table name

If Worksheets("Pipe Costing").Range("D" & ThisRow).Value = "Type K" Then
    Copper_Type_ref = "Type_K"
ElseIf Worksheets("Pipe Costing").Range("D" & ThisRow).Value = "Type L" Then
        Copper_Type_ref = "Type_L"
ElseIf Worksheets("Pipe Costing").Range("D" & ThisRow).Value = "Type M" Then
        Copper_Type_ref = "Type_M"
ElseIf Worksheets("Pipe Costing").Range("D" & ThisRow).Value = "Type DWV" Then
        Copper_Type_ref = "Type_DWV"
End If

The "ThisRow" is just the row number of the user input (i.e. they are changing something in row 4, so ThisRow=4).

The complete code is:

Private Sub Copper_Data_Fill(ThisRow)
Dim Copper_Type_ref As String
Dim RowNum As Long

If Worksheets("Pipe Costing").Range("D" & ThisRow).Value = "Type K" Then
        Copper_Type_ref = "Type_K"
ElseIf Worksheets("Pipe Costing").Range("D" & ThisRow).Value = "Type L" Then
        Copper_Type_ref = "Type_L"
ElseIf Worksheets("Pipe Costing").Range("D" & ThisRow).Value = "Type M" Then
        Copper_Type_ref = "Type_M"
ElseIf Worksheets("Pipe Costing").Range("D" & ThisRow).Value = "Type DWV" Then
        Copper_Type_ref = "Type_DWV"
End If

    'RowNum = Application.Match("F" & ThisRow, Worksheets("DATA").ListObjects(Copper_Type_ref).ListColumns(2).DataBodyRange, False).Row
    'RowNum = Worksheets("DATA").ListObjects(Copper_Type_ref).ListColumns(2).DataBodyRange.Find("F" & ThisRow, xlValues).Row
    RowNum = Worksheets("DATA").ListObjects(Copper_Type_ref).ListColumn(2).DataBodyRange.Find("F" & ThisRow, xlValues).Index
    Worksheets("Pipe Costing").Range("H" & ThisRow).Value = Worksheets("DATA").ListObjects(Copper_Type_ref).DataBodyRange(RowNum, 4).Value
End Sub

I am hoping to have RowNum be the table row number and then use that to fill the last line of

Worksheets("Pipe Costing").Range("H" & ThisRow).Value = Worksheets("DATA").ListObjects(Copper_Type_ref).DataBodyRange(RowNum, 4).Value

Any help is appreciated!

Solution

  • It would be easier to pass the current row from the input sheet as a Range parameter to your sub:

    Private Sub Copper_Data_Fill(ThisRow As Range)
    Dim dVal, f As Range, tbl as range
    
    
    dVal = ThisRow.Columns("D").Value
    Select Case dVal
        Case "Type K", "Type L", "Type M", "Type DWV"
            'get the corresponding listobject data range
            Set tbl = Worksheets("DATA").ListObjects(Replace(dVal, " ", "_")).DataBodyRange
        Case Else
            Exit Sub 'nothing to do (clear H?)
    End Select
    
    Set f = tbl.Columns(2).Find(ThisRow.Columns("F").Value, _
                                lookat:=xlWhole, LookIn:=xlValues)
    
    If Not f Is Nothing Then
        ThisRow.Columns("H").Value = f.Offset(0, 2).Value 'col4
    Else
        ThisRow.Columns("H").Value = "not found"
    End If
    
End Sub