1

I am trying to develop a search template using xlookup via VBA. The input cell will be a string of numbers and the output will be displayed via a recorded macro from xlookup formula and copy pasting the output as values. Once the search button is clicked, the macro will run and display the xlookup results (14 cells/column). However I am having trouble to prevent lagging from the file as the data source range is from a large (20+MB) file which is saved in a shared drive and will be updated from time to time as well. Is there a way the path can be written as string in the macro or other ways to help with the lag?

This is the recorded Macro

Sub Search()
'
' Search Macro
' AU Stripe Macro Search
'

'
    Range("B6").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2,'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C1,""Merchant Details Not Available"")"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = _  
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2,""Merchant Details Not Available"")"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C3,""Merchant Details Not Available"")"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C4,""Merchant Details Not Available"")"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C5,""Merchant Details Not Available"")"
    Range("B11").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C6,""Merchant Details Not Available"")"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C7,""Merchant Details Not Available"")"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C8,""Merchant Details Not Available"")"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C9,""Merchant Details Not Available"")"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C10,""Merchant Details Not Available"")"
    Range("B16").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C11,""Merchant Details Not Available"")"
    Range("B17").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C12,""Merchant Details Not Available"")"
    Range("B18").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C13,""Merchant Details Not Available"")"
    Range("B19").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(R4C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C2, 'Z:\Folder\[Master List.xlsx]Sheet1'!C14,""Merchant Details Not Available"")"
    Range("B6:B19").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
Warcupine
  • 4,460
  • 3
  • 15
  • 24
pyl
  • 19
  • 2
  • Can you post the code? We can't really help improve it if we don't know what is in it. – Warcupine Sep 20 '21 at 15:24
  • @Warcupine Sure thing. I've included that in my post now. Appreciate if you are able to help advise if there is way to also make the path of the file appear as string. – pyl Sep 22 '21 at 15:31

0 Answers0