Lookup to add Products from one table to another VBA

[Sohan2012]

Greetings, I would be very grateful if you could give me some idea, you will see I am trying to develop a code that allows me, through a macro, to apply the XLookUp function to compare two tables (TABLE1 AND TABLE1) and enter, according to their ID, the weight and the cost of the products in the second table and after leaving the "TABLE1" clean to be filled again,((𝐓𝐀𝐁𝐋𝐄𝐒 𝐂𝐀𝐍 𝐆𝐑𝐎𝐖 𝐎𝐍 𝐈𝐃 𝐂𝐎𝐃𝐄 𝐅𝐎𝐑 𝐏𝐑𝐎𝐃𝐔𝐂𝐓𝐒)) I'm starting with Vba and I can't get much further than the Dim and the Set, I'm trying to declare the function with Listobjects.ListColumns. DataBody.Range and the function does not accept the range thanks for what you can contribute...

30306

30305

[o0omax]

can you share the excel file you are working on?

[Sohan2012]

Hello, I'm an accountant and I would like to automate an Excel book, I have just graduated from the university, basically in order to make the code work, I have the two declared tables and I want to use xlookup using the reference ID from "table 2" and that me bring the weight and the value of the "table 1" there are 2 images jpg any help would be very grateful

[o0omax]

Solved it with two for loops. There might be an easier solution, but i got this working.

I asked you for the Excel file so that I know where the tables are. Now they are in two different sheets. But you can change it (also in the code).

[Sohan2012]

30313

Could you guide me when you make the loop you mention the following fragment "If Str(a) = Str(b) Then" however when I look for "Str" in the variables I do not get its declaration could you tell me what "Str" means

[Aussiebear]

Str is the variable name for String

[o0omax]

sorry for not giving the variables proper names.

Try to understand what I am looping:


For Each a In GoalTable.ListColumns("ID").DataBodyRange

"GoalTable.ListColumns("ID").DataBodyRang" is a range of all the IDs in the GoalTable. So just read it like this: For each ID in the GOal list (which is a) do sth. The example here is a= 1001 (you can step through your code pressing F8 and hover over the variable to see which value they take on - that helps you to see whats going on).

Next question do what?
Next we want to loop through all the IDs, which
"For Each b In SrcTable.ListColumns("ID").DataBodyRange" does. If a = b, so if the IDs are the same then do the copying.

PS: I just used str() to make sure a and b are of the same datatype (here string). If a would be 1001 as a string and b would be 1001 as an integer, that would not be equal!

[o0omax]

I just checked it again and saw that it soemhow only works for the first row. Does somebody know how to fix it?

[Sohan2012]

Hello, thank you very much, the code you indicated to me works but only with the first row of the "Goal" table, you can try it for yourself, I was reviewing the loop and apparently it is so good, it is not xlookup but in theory it works well, you could complement it for I worked with more rows thanks

[o0omax]

I also dont know why it does not work. If nobody replies in the next 2 days, just open a new Thread stating the exact Problem with the excel file included. Thats how others can also find the solution better.