formula to VBA at value

rjmdc

Well-known Member
Joined
Apr 29, 2020
Messages
693
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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]")

VBA Code:
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
 
Upvote 0
hi
please help me fit it to this style so that it gets called among all the other codes called
Rich (BB code):
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
 
Upvote 0
please help me fit it to this style
Try:

VBA Code:
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

:giggle:
 
Upvote 0
Solution
we are still in testing i will keep you posted i think it looks like its working
 
Upvote 0
hi
this seems to work
thank you i hope continued usage continues to export expected results
i appreciate the assistance
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top