VBA to apply filter and fill a column using vlookup

[ajeya]

Hello !

I was trying to apply Vlookup on a new column created with the reference table in another workbook.

What I'm trying to achieve:

> Column S in "Working IC" workbook, with sheet "Subs Console" has the Party Name column from where I want to compare the Party names from another workbook "MacroRUN.xlsm" havin sheet "Party Name".

> Sheet "Party Name" has column A similar to column S in "Subs Console" sheet of another workbook.

> Sheet "Party Name" has column B which we want to lookup and apply on sheet "Subs Console" of another workbook in column AZ with reference to Party name.

ActiveSheet.UsedRange.AutoFilter Field:=48, Criteria1:="AP"
    
    Dim ws As Worksheet
    Set ws = Sheets("Subs Console")
    
    Windows("Working IC.xlsx").Activate
    Sheets("Subs Console").Select
    
    Dim c As Range
    
        For Each c In ws.UsedRange.Columns("AZ").Cells
        
            On Error Resume Next
            c.Value = Application.WorksheetFunction.VLookup(c, Sheets("Party Name").Range("A:B"), 2, False)
            
        Next c

 

[JackDanIce]

Without the filter, what would the formula be if you typed it in and what cell does it go in?

 

[ajeya]

Without the filter, what would the formula be if you typed it in and what cell does it go in?

Thanks for your reply JackDanIce.

If I understand correctly, you are probably asking me this:

Without the filter, All these columns start from row 5 and all the corresponding data is in row 6 onwards.

Didn't understnad your part about the formula. I would still be applying the Vlookup.


Forgot to mention this:

After I run it, nothing actually happens and the entire column remains blank. Pressing F8 shows that it gets stuck on the "For Each" loop.

 

[JackDanIce]

The formula is "=VLOOKUP(A2,Table,Column,0<false or="" true="">)" i.e. if you were not using VBA, what would the VLOOKUP-formula in the Excel sheet be and what cell would it go in?</false>

 

[ajeya]

The formula is "=VLOOKUP(A2,Table,Column,0<false or="" true="">)" i.e. if you were not using VBA, what would the VLOOKUP-formula in the Excel sheet be and what cell would it go in?</false>

I'm sorry.

On Excel , I would be applying this formula on sheet "Subs Console" of Workbook "Working IC.xlsx" on column AZ and row 6 :

=VLOOKUP(S6,'[MacroRUN.xlsm]Party Name'!$A:$B,2,FALSE)

But I also need to apply the filter as these Vlookups need to be only applied on 2 criteria.

 

[JackDanIce]

Try running this which doesn't need the filter but still tests for value of "AP":

Sub Macro1()

    Dim LR      As Long
    Dim x       As Long
    Dim arr()   As Variant
    Dim arrAZ() As Variant
    Dim dic     As Object
    
    With Workbooks("MacroRUN.xlsm").Sheets("Party Name")
        arr = .Cells(1, 1).Resize(.Cells(.Rows.count, 1).End(xlUp).row, 2).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 1)) = arr(x, 2)
    Next x
    Erase arr
    
    Application.ScreenUpdating = True
    
    With Workbooks("Working IC.xlsx")
        .Activate
        With .Sheets("Subs Console")
            If .AutoFilterMode Then .AutoFilterMode = False
            LR = .Cells(.Rows.count, 48).End(xlUp).row - 4
            arr = .Cells(6, 19).Resize(LR, 30).Value
            arrAZ = .Cells(6, 52).Resize(LR).Value
            For x = LBound(arr, 1) To UBound(arr, 1)
                If CStr(arr(x, 30)) = "AP" Then arrAZ(x, 1) = dic(arr(x, 1))
            Next x
            .Cells(6, 52).Resize(UBound(arr, 1)).Value = arrAZ
            If ActiveSheet.Name <> .Name Then .Select
        End With
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr
    Erase arrAZ
    Set dic = Nothing
                    
End Sub

 

[ajeya]

Try running this which doesn't need the filter but still tests for value of "AP":

Sub Macro1()

    Dim LR      As Long
    Dim x       As Long
    Dim arr()   As Variant
    Dim arrAZ() As Variant
    Dim dic     As Object
    
    With Workbooks("MacroRUN.xlsm").Sheets("Party Name")
        arr = .Cells(1, 1).Resize(.Cells(.Rows.count, 1).End(xlUp).row, 2).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 1)) = arr(x, 2)
    Next x
    Erase arr
    
    Application.ScreenUpdating = True
    
    With Workbooks("Working IC.xlsx")
        .Activate
        With .Sheets("Subs Console")
            If .AutoFilterMode Then .AutoFilterMode = False
            LR = .Cells(.Rows.count, 48).End(xlUp).row - 4
            arr = .Cells(6, 19).Resize(LR, 30).Value
            arrAZ = .Cells(6, 52).Resize(LR).Value
            For x = LBound(arr, 1) To UBound(arr, 1)
                If CStr(arr(x, 30)) = "AP" Then arrAZ(x, 1) = dic(arr(x, 1))
            Next x
            .Cells(6, 52).Resize(UBound(arr, 1)).Value = arrAZ
            If ActiveSheet.Name <> .Name Then .Select
        End With
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr
    Erase arrAZ
    Set dic = Nothing
                    
End Sub

Thanks for the code. Looks a little bit complex to understand for my understanding level

Okay, I ran this for "AP" module and got an error: "object variable or with block variable not set" . Tried to solve it myself but unable to fix it.

Error on this line: dic(arr(x, 1)) = arr(x, 2)

Thanks a lot for your quick assistance.

 

[JackDanIce]

My bad, try:

Sub Macro1()

    Dim LR      As Long
    Dim x       As Long
    Dim arr()   As Variant
    Dim arrAZ() As Variant
    Dim dic     As Object
        
    Set dic = CreateObject("Scripting.Dictionary")
        
    With Workbooks("MacroRUN.xlsm").Sheets("Party Name")
        arr = .Cells(1, 1).Resize(.Cells(.Rows.count, 1).End(xlUp).row, 2).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 1)) = arr(x, 2)
    Next x
    Erase arr
    
    Application.ScreenUpdating = True
    
    With Workbooks("Working IC.xlsx")
        .Activate
        With .Sheets("Subs Console")
            If .AutoFilterMode Then .AutoFilterMode = False
            LR = .Cells(.Rows.count, 48).End(xlUp).row - 4
            arr = .Cells(6, 19).Resize(LR, 30).Value
            arrAZ = .Cells(6, 52).Resize(LR).Value
            For x = LBound(arr, 1) To UBound(arr, 1)
                If CStr(arr(x, 30)) = "AP" Then arrAZ(x, 1) = dic(arr(x, 1))
            Next x
            .Cells(6, 52).Resize(UBound(arr, 1)).Value = arrAZ
            If ActiveSheet.Name <> .Name Then .Select
        End With
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr
    Erase arrAZ
    Set dic = Nothing
                    
End Sub

 

[ajeya]

My bad, try:

Sub Macro1()

    Dim LR      As Long
    Dim x       As Long
    Dim arr()   As Variant
    Dim arrAZ() As Variant
    Dim dic     As Object
        
    Set dic = CreateObject("Scripting.Dictionary")
        
    With Workbooks("MacroRUN.xlsm").Sheets("Party Name")
        arr = .Cells(1, 1).Resize(.Cells(.Rows.count, 1).End(xlUp).row, 2).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 1)) = arr(x, 2)
    Next x
    Erase arr
    
    Application.ScreenUpdating = True
    
    With Workbooks("Working IC.xlsx")
        .Activate
        With .Sheets("Subs Console")
            If .AutoFilterMode Then .AutoFilterMode = False
            LR = .Cells(.Rows.count, 48).End(xlUp).row - 4
            arr = .Cells(6, 19).Resize(LR, 30).Value
            arrAZ = .Cells(6, 52).Resize(LR).Value
            For x = LBound(arr, 1) To UBound(arr, 1)
                If CStr(arr(x, 30)) = "AP" Then arrAZ(x, 1) = dic(arr(x, 1))
            Next x
            .Cells(6, 52).Resize(UBound(arr, 1)).Value = arrAZ
            If ActiveSheet.Name <> .Name Then .Select
        End With
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr
    Erase arrAZ
    Set dic = Nothing
                    
End Sub

This is amazing. It works for all "AP" values on column "AV" and the rest are blank as expected. Thanks a lot for your help !

I will try to analyse this code further as I have another "EAR" condition on same column "AV" where I need to apply the similar code (will post it here If that goes wrong).

Just a small query, If you can spare a few minutes,
What exactly went wrong in my code?

Thanks again

 

[JackDanIce]

Glad it works. I think you weren't referencing the correct sheets when looking up the values but you didn't post the entire code and without your work book, difficult to replicate the error to explain.