formula to VBA at value

[rjmdc]

hi
how would i translate this simple formula into a lookup sub then saved as value not a formula
=XLOOKUP([@Participant],'Clients Info'!A:A,'Clients Info'!B:B)

this is very volotile
my workbook with many thousands of rows partcipant name change slightly in the database like added an initial or a lower case or space and most of my data already used and saved became #NA
i need the lookup as a formula but then saved as a value so it never changes no matter what happens - via VBA
please help me

 

[DanteAmor]

Try the following macro.
Before running the macro, set on this line the name of your table and the name of the header where you want to put the formula.
With Range("Table1[HeadFormula]")

Sub macro3()
  With Range("Table1[HeadFormula]")   '<-- fit your data
    .Formula = "=VLOOKUP([@Participant],'Clients Info'!A:B,2,0)"
    .Value = .Value
  End With
End Sub

Regards
Dante Amor

 

[rjmdc]

hi
please help me fit it to this style so that it gets called among all the other codes called

Sub RefreshTabsFormula(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Target.Column = 1

        Cells(Target.Row, "B").Formula3 = help me fill in the formula
           
        Cells(Target.Row, "B").Formula3 = Cells(Target.Row, "B").Value
        End If
    End If
End Sub

 

[DanteAmor]

please help me fit it to this style

Try:

Sub RefreshTabsFormula(ByVal Target As Range)
  If Target.Cells.Count = 1 Then
    If Target.Column = 1 Then
      Cells(Target.Row, "B").Formula = "=VLOOKUP(A" & Target.Row & ",'Clients Info'!A:B,2,0)"
      Cells(Target.Row, "B").Value = Cells(Target.Row, "B").Value
    End If
  End If
End Sub

 

[rjmdc]

we are still in testing i will keep you posted i think it looks like its working

 

[rjmdc]

hi
this seems to work
thank you i hope continued usage continues to export expected results
i appreciate the assistance