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]

Struggling to fully comprehend without sample data/formulas but see if this is it.

Sub Test3()
  Dim d As Object
  Dim T14_10 As Variant, T14_60 As Variant, T1_1 As Variant, T1_6 As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  T14_10 = Range("Table14[col 10]").Value
  T14_60 = Range("Table14[col 60]").Value
  T1_1 = Range("Table1[col 1]").Value
  T1_6 = Range("Table1[col 6]").Value
  For i = 1 To UBound(T1_1)
    If Len(T1_6(i, 1)) > 0 Then d(T1_1(i, 1)) = T1_6(i, 1)
  Next i
  For i = 1 To UBound(T14_10)
    If d.exists(T14_10(i, 1)) Then T14_60(i, 1) = d(T14_10(i, 1))
  Next i
  Range("Table14[col 60]").Value = T14_60
  With Range("Table1[col 6]")
    .ClearContents
    .Cells(1).Formula2 = "=XLOOKUP([@[col 1]],Table14[col 10],Table14[col 60])&"""""
  End With
End Sub

what's the '&""""" ' portion for?

So that if thee looked up cell is empty the formula returns "" rather than 0

 

[frostworks]

Struggling to fully comprehend without sample data/formulas but see if this is it.

Sub Test3()
  Dim d As Object
  Dim T14_10 As Variant, T14_60 As Variant, T1_1 As Variant, T1_6 As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  T14_10 = Range("Table14[col 10]").Value
  T14_60 = Range("Table14[col 60]").Value
  T1_1 = Range("Table1[col 1]").Value
  T1_6 = Range("Table1[col 6]").Value
  For i = 1 To UBound(T1_1)
    If Len(T1_6(i, 1)) > 0 Then d(T1_1(i, 1)) = T1_6(i, 1)
  Next i
  For i = 1 To UBound(T14_10)
    If d.exists(T14_10(i, 1)) Then T14_60(i, 1) = d(T14_10(i, 1))
  Next i
  Range("Table14[col 60]").Value = T14_60
  With Range("Table1[col 6]")
    .ClearContents
    .Cells(1).Formula2 = "=XLOOKUP([@[col 1]],Table14[col 10],Table14[col 60])&"""""
  End With
End Sub

So that if thee looked up cell is empty the formula returns "" rather than 0

This works, excellent thanks!

 

[Peter_SSs]

This works, excellent thanks!

You're welcome. Sorry it took me so long to catch on but we got there in the end.