update a table based on another table

[modiria50989]

[FONT=&quot]Hello,

[/FONT][FONT=&quot]<o></o>[/FONT]
[FONT=&quot]I need a VBA for the following process please. Thanks.[/FONT]
[FONT=&quot]If we have 2 tables in excel. each table has [/FONT][FONT=&quot]diffrent number ofrows, they have 2 columns including car nembers and car names. the macrofirst look at the car number at table 1 then goes through table 2, if finds asame car number, then replace the car name at table 1 with the car name attable 2 corresponding to that car number.[/FONT][FONT=&quot]<o></o>[/FONT]
[FONT=&quot] [/FONT]

 

[igold]

Hi modiria50989,

Welcome to the Forum.

Assuming that these are really Tables that you are referring to and not ranges, then see if this does what you want.

This code assumes that Table1 is on Sheet1 and Table2 is on Sheet2.

Sub CarsNames()


    Dim tbl1 As ListObject: Set tbl1 = Worksheets("Sheet1").ListObjects("Table1")
    Dim tbl2 As ListObject: Set tbl2 = Worksheets("Sheet2").ListObjects("Table2")
    Dim i As Long, ii As Long
    For i = 1 To tbl1.ListRows.Count
        For ii = 1 To tbl2.ListRows.Count
            If tbl2.DataBodyRange(ii, 1) = tbl1.DataBodyRange(i, 1) Then
                tbl1.DataBodyRange(i, 2) = tbl2.DataBodyRange(ii, 2)
            End If
        Next
    Next
      
End Sub

 

[modiria50989]

Hi, thanks for the code, but seems something's wrong here since I'm getting an error that says: "Subscript out of range". it highlights the bellow line. just to know that length of the table (Table2) that I want to modify that is much more than the reference table(Table1).

 

[modiria50989]

sorry I forgot, here is the line highlighted for error:

Dim tbl1 As ListObject: Set tbl1 = Worksheets("Sheet1").ListObjects("Table1")

 

[igold]

You have to make sure that you substitute your table names for the the table names used in the code. The same is true for the worksheet names where the table resides...

The number of rows in each table is of no consequence.

Also according to your OP you wanted to modify Table1 not Table2...

if finds asame car number, then replace the car name at table 1 with the car name attable 2 corresponding to that car number.

 

[modiria50989]

I want to modify Table 2 which has more row numbers, sorry if I said wrong. However, I'm sure that I named my tables correctly based on the names (Table1, Table2) you gave me. In the Sheet1 I named a 2 columns table as Table1, why the bellow is highlighted?

Dim tbl1 As ListObject: Set tbl1 = Worksheets("Sheet1").ListObjects("Table1")

 

[igold]

What version of Excel are you using?

Did you make the two columns a "Named Range" or did you make the two columns a "Table".

 

[igold]

While we are trying to work through the first part, here is updated code that will change Table2 rather than Table1.

Sub CarNames()


    Dim tbl1 As ListObject: Set tbl1 = Worksheets("Sheet1").ListObjects("Table1")
    Dim tbl2 As ListObject: Set tbl2 = Worksheets("Sheet2").ListObjects("Table2")
    Dim i As Long, ii As Long
    For i = 1 To tbl1.ListRows.Count
        For ii = 1 To tbl2.ListRows.Count
            If tbl2.DataBodyRange(ii, 1) = tbl1.DataBodyRange(i, 1) Then
                tbl2.DataBodyRange(ii, 2) = tbl1.DataBodyRange(i, 2)
            End If
        Next
    Next
      
End Sub

 

[modiria50989]

Excel 2010 on windows 10. For example for table 1, in sheet1 i select my range by mouse then i right click on the selected area( A1:B16) then i hit the Define Name and name it as Table1

 

[igold]

OK, you are making it a Named Range. Try this instead...

Select the range (including the header), on the Home tab of the ribbon, click on "Format as Table". Many different styles will come up, you can pick any style now (it can be changed later). A box will open asking you to confirm your selection and if you have Headers. Click on OK. Then look at the ribbon and Table Tools should be highlighted. All the way on the left hand side of the ribbon you will see the "Table Name", change that to either Table1 or Table2 depending on which range you are working with.

After that just make sure that the sheet names are correct.