Macro to filter and put vlookup formula in following rows

Help Activities
, ,
1

I want to apply Filter in L column from L3 to until data is their, and in that filtered rows of L column I want to apply the Vlookup formula. Suggest a macro code to implement it.

2

Hi @Gopi_Krishna1

Try this code:

Sub ApplyFilterAndVlookup()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name
    
    ' Turn off screen updating to speed up the process
    Application.ScreenUpdating = False
    
    ' Find the last row with data in column L
    lastRow = ws.Cells(ws.Rows.Count, "L").End(xlUp).Row
    
    ' Apply filter to column L from row 3 to the last row with data
    ws.Range("L3:L" & lastRow).AutoFilter Field:=1, Criteria1:="<>"

    ' Apply VLOOKUP formula to the filtered range
    ws.Range("M3:M" & lastRow).Formula = "=VLOOKUP(L3, 'OtherSheet'!A:B, 2, FALSE)" ' Adjust 'OtherSheet' and the column index as needed

    ' Turn on screen updating
    Application.ScreenUpdating = True
End Sub

Hope it helps!!

3

Sorry i didn’t mention the filter criteria we have to put filter for #N/A and empty cells

4

Hi @Gopi_Krishna1

Try this:

Sub FilterColumnAndApplyVlookup()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
    
    ' Find the last row with data in column L
    lastRow = ws.Cells(ws.Rows.Count, "L").End(xlUp).Row
    
    ' Define the range to apply the filter (assuming data starts from L3)
    Set rng = ws.Range("L3:L" & lastRow)
    
    ' Apply the filter
    rng.AutoFilter Field:=1, Criteria1:="<>#N/A", Operator:=xlAnd, Criteria2:="<> "
    
    ' Apply VLOOKUP formula to the filtered range in column M
    ws.Range("M3:M" & lastRow).Formula = "=VLOOKUP(L3, 'OtherSheet'!A:B, 2, FALSE)" ' Adjust 'OtherSheet' and the column index as needed

    ' Remove the filter
    rng.AutoFilter
End Sub

Regards

5

Hi @Gopi_Krishna1,

Try this one.

Sub ApplyFilterAndVlookup()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim visibleRange As Range
    
    'Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name
    
    ' Turn off screen updating to speed up the process
    Application.ScreenUpdating = False
    
    ' Find the last row with data in column L
    lastRow = ws.Cells(ws.Rows.Count, "L").End(xlUp).Row
    
    ' Apply filter to column L from row 3 to the last row with data
    ws.Range("$A$1:$L$20").AutoFilter Field:=12, Criteria1:="=#N/A", _
        Operator:=xlOr, Criteria2:="="

' Get the visible range after applying the filter
    On Error Resume Next
    Set visibleRange = ws.Range("$A$1:$L$" & lastRow).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0    

' Check if there are visible cells
    If Not visibleRange Is Nothing Then
        ' Apply VLOOKUP formula in column M (assuming M is the adjacent column to L)
        visibleRange.Offset(0, 1).Formula = "=VLOOKUP(L3, A:B, 2, FALSE)" ' Change "A:B" to your lookup range

        ' Select only the visible cells
        visibleRange.Select
    End If
    ' Turn on screen updating
    Application.ScreenUpdating = True
End Sub

Thanks,
Ashok :slight_smile:

7

Is this filter only #N/A and empty cells?

8

filter only #N/A OR empty cells

9

Filtering is not applying properly, I need only #N/A and empty row cells

10

Check my updated code.

This is updated.
ws.Range(“$A$1:$L$20”).AutoFilter Field:=12, Criteria1:=“=#N/A”, _
Operator:=xlOr, Criteria2:=“=”

11

I’m am going to call this code from text file. Im using UiPath invoke code activity for this.

12

Hi @Gopi_Krishna1

Can you share the exact requirement of yours please.

Regards

13

Macro is working fine. But I want to keep the macro outside of my execution file. I their any way keep the macro outside?

14

Hi @Gopi_Krishna1

Are you asking about the keeping the Macros text file outside the project folder. Specify.

Regards

15

Will i have to
create new query for that?

16

Hi @Gopi_Krishna1

If you want to place the Macros text file in any other folder you can do that. and pass the text file path in Invoke VBA.

If your query is about something else specify.

Regards

17

Also I need to put some other Vlookup value in P,Q,R,U,…some more columns

18

You can repeat the code for vlookup just change the offset as you shift next columns.

visibleRange.Offset(0, 4) for P column
visibleRange.Offset(0, 5) for Q column

Likewise.

Thanks,
Ashok :slightly_smiling_face:

1 Like
19

If I want to put Vlookup formula in L column itself means? What modification we have to do?

20

In that case you don’t need to use offset. It would be visibleRange.Formula =

For columns before L use this

visibleRange.Offset(0, -1) for K
visibleRange.Offset(0, -2) for J

Thanks,
Ashok :slightly_smiling_face:

1 Like
21

can we use this code only in macro inside the execution excel or we can use it in txt file using invoke VB activity? Meanwhile having it inside the execution excel as macro it’s working fine. But in invoke VB activity it’s not filtering properly. Putting formula in random rows in L column