Change Value in Listobject

    Dear experts


    I'm a bit confused with listobjects.

    While I know how to add news records to the listobject (just add the data underneath it), I do not know how to change existing data (changing value, deleting records) inside a listobject.


    The listobject is called tblProducts and it contains columns ProductID,Name,Price etc. and the data is entered with userforms.


    How do I change a value if I know the ProductID?


    I need to find out in which row the ProductID is with Application.Worksheetfunction.Match, right? For example, ProductID 88 is in row 8
    From row 8, I need to know the columns ... and that where I don't know how to go ahead, as I have to use the column names?


    I see that listobjects contain "DataBodyRange". I assume that this is for the value inside the listobject?
    Do I need to use DataBodyRange?


    I've created a workbook with some sample data and a userform, hope this helps.


    Thank you so much for your help.


    Maria

    Re: Change Value in Listobject


    Maria - not entirely sure what you're trying to do, but as an example this will change the value of Product 88 in the Price with VAT column

    Code
    Sub x()


With ActiveSheet.ListObjects("tblProducts")
    .DataBodyRange(Application.Match(88, .ListColumns(1).DataBodyRange, 0), Application.Match("Price with VAT", .HeaderRowRange, 0)).Value = 50
End With


End Sub


    There is an overview here http://www.thespreadsheetguru.…o-listobject-excel-tables

    Re: Change Value in Listobject


    Hello Stephen


    I should have explained my problem a bit in more detail, sorry for that.
    But the code you wrote is what I need.
    Brilliant!


    Thanks a lot


    Maria

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login