Convert formula into vba macro

[eddorena]

Hi All,

Please help me to convert this formula by using a VBA macro, this formula helps me to auto Xlookup for entire line (without putting same formula in another line)

=FILTER(XLOOKUP(ISGPN,'[FILEBAPREF.xlsx]Sheet1'!$B:$B,'[FILEBAPREF.xlsx]Sheet1'!$G:$G,"",0),XLOOKUP(A:A,'[FILEBAPREF.xlsx]Sheet1'!$B:$B,'[FILEBAPREF.xlsx]Sheet1'!$G:$G,"",0)<>"")

I try this one and not working well.

result.Value = Application.WorksheetFunction.Filter(formula, Evaluate(formula.Address & "<>"""""))

The formula name above is code as below

formula.Value = Application.WorksheetFunction.XLookup(Lookuplist, wb.Worksheets("PNR").Range("B:B"), wb.Worksheets("PNR").Range("G:G"), """", 0)

Thank You

 

[excel_learnerz]

Here is a VBA macro that performs the same Xlookup function as your formula and populates the results in the same row as the input values:

Sub XlookupMacro()
    Dim lastRow As Long
    lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row 'get the last row with data in column A
    Dim lookupRange As Range
    Set lookupRange = Range("B1:B100") 'set the lookup range as required
    Dim lookupResult As Variant
    Dim i As Long
    For i = 1 To lastRow
        If Not IsEmpty(Cells(i, "A")) Then 'check if cell in column A is not empty
            lookupResult = Application.XLookup(Cells(i, "A"), lookupRange, Range("G1:G100"), "", 0, 1) 'perform Xlookup
            lookupResult = WorksheetFunction.Filter(lookupResult, WorksheetFunction.XLookup(Range("A" & i), lookupRange, Range("G1:G100"), "", 0, 1) <> "") 'filter lookup result
            Range("H" & i).Resize(1, UBound(lookupResult)) = lookupResult 'populate result in row i, starting from column H
        End If
    Next i
End Sub

You can modify the range lookupRange to match the range of your lookup values in column B, and adjust the range G1:G100 to match the range of your return values. The macro loops through each row in column A and performs the Xlookup and Filter functions on the corresponding row in column B. The results are then populated in the same row as the input value, starting from column H.

 

[eddorena]

Here is a VBA macro that performs the same Xlookup function as your formula and populates the results in the same row as the input values:

Sub XlookupMacro()
    Dim lastRow As Long
    lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row 'get the last row with data in column A
    Dim lookupRange As Range
    Set lookupRange = Range("B1:B100") 'set the lookup range as required
    Dim lookupResult As Variant
    Dim i As Long
    For i = 1 To lastRow
        If Not IsEmpty(Cells(i, "A")) Then 'check if cell in column A is not empty
            lookupResult = Application.XLookup(Cells(i, "A"), lookupRange, Range("G1:G100"), "", 0, 1) 'perform Xlookup
            lookupResult = WorksheetFunction.Filter(lookupResult, WorksheetFunction.XLookup(Range("A" & i), lookupRange, Range("G1:G100"), "", 0, 1) <> "") 'filter lookup result
            Range("H" & i).Resize(1, UBound(lookupResult)) = lookupResult 'populate result in row i, starting from column H
        End If
    Next i
End Sub

You can modify the range lookupRange to match the range of your lookup values in column B, and adjust the range G1:G100 to match the range of your return values. The macro loops through each row in column A and performs the Xlookup and Filter functions on the corresponding row in column B. The results are then populated in the same row as the input value, starting from column H.

Hi Learnerz, I'll testing right now.