vba vlookup and writing formula into cell

[frostworks]

Hello! I have two tables here and am attempting to use vba to do two things:

1. lookup value in sheet 1 table 14 col 10, match against sheet 2 table 1 col 1, return sheet 2 table 1 col 6 value in sheet 1 table 14 col 60
2. write vlookup formula into sheet 2 table 1 col 6 to look up value in sheet 2 table 1 col 1 and return sheet 1 table 14 col 60

Can't seem to find a standardise/proper example online or maybe i'm too dense for it.. Can someone help me please?

Sheet 1 Table 14

Sheet 2 Table 1

Thanks in advance!

 

[Peter_SSs]

Hmm, that sounds pretty much just swapping the values into the other table then bringing them back by formula? If so, try this with a copy of your workbook.

Sub Test()
  With Range("Table14[col 60]")
    .Cells(1).Formula2 = "=XLOOKUP([@[col 10]],Table1[col 1],Table1[col 6])&"""""
    .Value = .Value
  End With
  With Range("Table1[col 6]")
    .ClearContents
    .Cells(1).Formula2 = "=XLOOKUP([@[col 1]],Table14[col 10],Table14[col 60])&"""""
  End With
End Sub

 

[frostworks]

hello! it seems to work great but when i try to apply it to my real tables, it only picks up the very first row of each table. is there any possible reason why?

For some context on this request, Table 1 is a Power Query output of Table 14 merged with another table.
Table 1 Col 6 is where comments are entered by others, and in order for it to reflect properly the next time the query is run, i copy the comments from Table 1 Col 6 into Table 14 Col 60 and change Table 1 Col 6 to a lookup of Table 14 Col 60.

 

[Peter_SSs]

What happens if you add this line?

Sub Test2()
  With Range("Table14[col 60]")
    .ClearContents
    .Cells(1).Formula2 = "=XLOOKUP([@[col 10]],Table1[col 1],Table1[col 6])&"""""
    .Value = .Value
  End With
  With Range("Table1[col 6]")
    .ClearContents
    .Cells(1).Formula2 = "=XLOOKUP([@[col 1]],Table14[col 10],Table14[col 60])&"""""
  End With
End Sub

 

[frostworks]

i don't think i would want to clear the contents of table14 col 60 from the start, as table1 col 6 still looks up that.
i believe i will run into an issue whereby line 3 of table14 col60 is populated, clearing contents will result in table1 col6 returning a "" instead of the entry as table1 col6 starts with a lookup.

Am i making sense here?

 

[Peter_SSs]

i don't think i would want to clear the contents of table14 col 60 from the start, as table1 col 6 still looks up that.

In your image table14 col 60 is blank and table1 col 6 has values so how could table1 col 6 be looking up values from table14 col 60?

 

[frostworks]

therefore the vba is to
1. look up and copy from table 1 col 6 to table 14 col 60, change to values only in table 14 col 60
2. clear table 1 col 6 and replace with a vlookup to table 14 col 60

 

[frostworks]

i'm reading your code as

Sub Test2()

 'looking up and copying from table1 col6 to table14 col60
  With Range("Table14[col 60]")
    .Cells(1).Formula2 = "=XLOOKUP([@[col 10]],Table1[col 1],Table1[col 6])&"""""
 'keep as value
    .Value = .Value
  End With
  With Range("Table1[col 6]")
    .ClearContents
 'looking up values in table140 col60 and showing it in table1 col6
    .Cells(1).Formula2 = "=XLOOKUP([@[col 1]],Table14[col 10],Table14[col 60])&"""""
  End With
End Sub

 

[Peter_SSs]

1. That doesn't explain how your Table1 has values when Table14 is blank.
   
   
2. Perhaps you could give us the two sample tables with XL2BB as they are "before" the requested change and also the two tables as they are "after" the requested change with any values and/or formulas manually entered as you want them to finish? That way we can be more sure of our testing.
   
   
3. When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.
   
   
4. I think that your reading of my code is correct. I must be missing something because as far as I can tell your code comments match your request.

1. lookup value in sheet 1 table 14 col 10, match against sheet 2 table 1 col 1, return sheet 2 table 1 col 6 value in sheet 1 table 14 col 60
2. write vlookup formula into sheet 2 table 1 col 6 to look up value in sheet 2 table 1 col 1 and return sheet 1 table 14 col 60

 

[frostworks]

Appreciate the help Peter!

1. Table 1 has values because there will be someone entering the comments. i need them to be transferred to Table 14 because i want it output again in Table 1 via Table 14, updated with whatever new comments/input in Table 1.

2. Unfortunately can't do that as my work laptop has high restrictions...

3. Noted on that, thanks for this time.

4. i did a workaround in the end using excel formula to create a dummy column reading results from col 6 and col 60, and having VBA to copy values from the dummy column into col 60. However i'm keen to know more about your code, what's the '&""""" ' portion for?
i think your code isn't working because some cells in the column has values and some doesn't. i think the formula cannot be replicated in the entire table if there are filled cells, so only row 1 works.