VBA Edit Table

Question

Hi 

I'm trying to code some basic VBA in excel(of which I have no knowledge). I watched some youtube videos and all went smoothly, but I ran in to the following problem:

I have a partially filled table (that was filled with other userforms). But now I don't want to add rows to a table, but edit existing (empty) fields in the table.

The user would select an input from a combobox named CBRegulatoryRM. Now excel should match that input with an entry in the first column of the table and in that row fill out the other user inputs CBRegulatoryRMFDA, CBRegulatoryTSCA, CBRegulatoryBfR36, CBRegulatoryGB, CBRegulatoryFoodContact. I have the following code:

Private Sub CommandButton4_Click()

Dim the_sheet As Worksheet

Dim table_list_object As ListObject

Dim table_object_row As ListRow

Set the_sheet = Sheets("WSRM")

Set table_list_object = the_sheet.ListObjects(1)

Set table_object_row = table_list_object.ListRows.Add

table_object_row.Range(Value = CBRegulatoryRM, 14).Value = CBRegulatoryRMFDA

table_object_row.Range(Value = CBRegulatoryRM, 15).Value = CBRegulatoryTSCA

table_object_row.Range(Value = CBRegulatoryRM, 16).Value = CBRegulatoryBfR36

table_object_row.Range(Value = CBRegulatoryRM, 17).Value = CBRegulatoryGB

table_object_row.Range(Value = CBRegulatoryRM, 18).Value = CBRegulatoryFoodContact

Unload UFRegulatory

End Sub

I guess the ListRows.Add line is causing the issue. But I don't know how to set the table_object_row variable to the user input CBRegulatoryRM (or if this is the correct way to go about it).

Anyone know how to do this?

Thanks.

Answer 1

Yes , it worked thanks!

Answer 2

The following assumes that the table has a header row,  and the you want the additional data to be inserted in the columns B, C, D, E and F of the row that contains an entry in Column A that matches the .Value selected in the ComboBox.

It also assumes that CBRegulatoryRMFDA, etc are the names of controls on your form.

If the data is to go in to columns other than B, C, D, E and F adjust the second index in the Offset  accordingly.

Private Sub CommandButton4_Click()

Dim i As Long, j As Long

With ActiveWOrksheet.Range("A1")

    For i = 1 To .currentregiona.Rows.Count - 1

        If .Offset(i, 0) = CBRegulatoryRM.Value Then

            .Offset(i, 1) = CBRegulatoryRMFDA

            .Offset(i, 2) = CBREgulatoryTSCA

            .Offset(i, 3) = CBRegulatoryBfR36

            .Offset(i, 4) = CBRegulatoryGB

            .Offset(i, 5) = CBRegulatoryFoodContact

            Exit For

        End If

    Next i

End With

End Sub