Autofilter and then apply Vlookup

[ajeya]

Hi,

I tried searching for this but unable to find any solution.

I have a sheet "Working IC" which has some 50+ columns (starting from row 5th) and I created a remarks column in "AZ" column first.
Then I applied autofilter with a criteria on column no 48 ( I will apply another filter and will do the same vlookup operation again on that)
I have another workbook open (MacroRUN.xlsm) which has two sheets "Party Name" and "Line Description". I will be using the data from these worksheets in "Working IC" sheet.

So from column 48, I will pick up all the filtered values one by one and apply vlookup to compare them from the "Party Name" and "Line Description" worksheets open in another workbook.
And on column "AZ" I want them to come one by one as per the vlookup values. But something is going on wrong and need your help on this.

Here is the code:

Sub RUN()
'
' RUN Macro
'


'
    Windows("Working IC.xlsx").Activate
    
    ' Open the sheet on this workbook where I need to work
    Sheets("Subs Console AP DATA").Select
    
    Range("AZ5").Select
    ActiveCell.FormulaR1C1 = "Tax Remarks"
    Range("AY5").Select
    Selection.Copy
    Range("AZ5").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("AV5").Select
    Selection.AutoFilter
    
    ' For First criteria "AP"
    ActiveSheet.UsedRange.AutoFilter Field:=48, Criteria1:="AP"
    
    Dim ws As Worksheet
    Set ws = Sheets("Subs Console AP DATA")
    
    Windows("MacroRUN.xlsm").Activate
    Sheets("Party Name").Select
    
    Dim rg As Range

    ' Will set a range to cover entire columns A and B to support future additions there. Column A has the same data as column S of "Working IC" workbook's open sheet. Column B has the value we are looking for and to put back in column AZ of "Working IC" workbook's open sheet after applying vlookup.
   
     Set rg = ActiveSheet.Range("A:B")
    
    
    Windows("Working IC.xlsx").Activate
    Sheets("Subs Console AP DATA").Select
    
    
    Dim c As Range
    On Error Resume Next
        For Each c In ws.UsedRange.Columns("AZ").Cells
            c.Value = Application.WorksheetFunction.VLookup(c, rg, 2, False)
        Next c
        
           
    
    
    
    ' For Second criteria EAR ( Second filter applied)
    
    ActiveSheet.UsedRange.AutoFilter Field:=48, Criteria1:="EAR"
    
    Windows("MacroRUN.xlsm").Activate
    Sheets("Line Description").Select
    
    Dim rg2 As Range
    Set rg2 = ActiveSheet.Range("A:B")
    
    
    Windows("Working IC.xlsx").Activate
    Sheets("Subs Console AP DATA").Select
    
    
    Dim c2 As Range
    On Error Resume Next
        For Each c2 In ws.UsedRange.Columns("AZ").Cells
            c2.Value = Application.WorksheetFunction.VLookup(c2, rg2, 2, False)
        Next c2
           
            
End Sub

 

[ajeya]

Bump

 

[ajeya]

Anyone ? Please ?

 

[ajeya]

Bump

 

[ajeya]

Need assistance, please.

 

[ajeya]

Bump

 

[ajeya]

Bump

 

[ajeya]

I tried to change some coding but still no luck !
Anyone?

Here it is:

Sub RUN()
'
' RUN Macro
'


'
    Windows("Working IC AP-EAR cost trf Jul 2017.xlsx").Activate
    Sheets("Subs Console AP DATA").Select
    
    Range("AZ5").Select
    ActiveCell.FormulaR1C1 = "Tax Remarks"
    Range("AY5").Select
    Selection.Copy
    Range("AZ5").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("AV5").Select
    Selection.AutoFilter
    
    ' For First criteria AP
    ActiveSheet.UsedRange.AutoFilter Field:=48, Criteria1:="AP"
    
    Dim ws As Worksheet
    Set ws = Sheets("Subs Console AP DATA")
         
    
    Windows("Working IC AP-EAR cost trf Jul 2017.xlsx").Activate
    Sheets("Subs Console AP DATA").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
           
    
    
    
    ' For Second criteria EAR
    
    ActiveSheet.UsedRange.AutoFilter Field:=48, Criteria1:="EAR"
    
      
    
    Windows("Working IC AP-EAR cost trf Jul 2017.xlsx").Activate
    Sheets("Subs Console AP DATA").Select
    
    
    Dim c2 As Range
    
        For Each c2 In ws.UsedRange.Columns("AZ").Cells
           On Error Resume Next
           c2.Value = Application.WorksheetFunction.VLookup(c2, Sheets("Line Description").Range("A:B"), 2, False)
        Next c2
           
  ActiveSheet.ShowAllData
              
End Sub

 

[ajeya]

Bump

 

[ajeya]

Sorry for not adding the exact error.

After I run it, nothing actually happens, there is no error and the code brings the screen back. The entire column remains blank. Pressing F8 shows that it gets stuck on the "For Each" loop.