Update Table (ListObject) Row Values, based on first cell value-CodePudding

I want to update data in any table using this format:

Dim Update As String
Dim FindUpdate As Range
         
Update = Range("USER_NAME").value
        
With Sheets("Settings")
    Set FindUpdate = .Range("USERS_TABLE").Find(What:=Update, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    .Cells(FindUpdate.row, 6).value = Computer
End With

This code works but it requires me to put this table at the beginning of the Sheet.

I want to change values in a table (ListObjects("USERS_TABLE")) regardless of where it's positioned in the worksheet. Changing the range to a table's name USERS_TABLE did not do the trick.

Ideally I want to change the above code to:

Dim Update As String
Update = Range("USER_NAME").value ' This is a Namedrange

' declare a variables that will point to a table I want to access
dim ws as Worksheet
Set ws = ActiveSheet

Dim table_row as ListRow
Set table_row = ws.ListObject("USERS_TABLE").ListRows.**SOMETHING_TO_HELP_ACCESS_THE_TABLE_ROW_HERE**

Dim FindUpdate As Range
    
With table_row ' REPLACE SHEETS WITH TABLE_ROW

    Set FindUpdate = .**Row?**.Find(What:=Update, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        
    ' AND CAUSE IT'S A TABLE, MAYBE A COLUMN HERE?
    .**Cells(or Range)?**(FindUpdate.**row**, 6).value = Computer
            
End With

CodePudding user response：

TO @VBasic2008, sorry for the confusion, but... This is what I have... It works I can add rows using this

Public Sub Add_Table_Row()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim add_row As ListRow
    
    Set add_row = ws.ListObjects("Names_Table").ListRows.Add(1)
    
    With add_row
    
        .Range(3) = "Sample Text"
    
    End With

End Sub

I want to use the above method to update Table values, like this...

Public Sub Update_Table_Row()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim update_row As ListRow
    
    Set update_row = ws.ListObjects("Names_Table").ListRows ' STUCK HERE
    
    With update_row 
    
        .Range(3) = "Change This Text"
    
    End With

End Sub

CodePudding user response：

is it work?

Public Sub Update_Table_Row()

Dim ws As Worksheet
Set ws = ActiveSheet

Dim update_row As ListRow

Set update_row = ws.ListObjects("Names_Table").ListRows ' STUCK HERE

With update_row 

    .Range(3) = "Change This Text"

End With

End Sub