Getting in filter up the particular Criteria

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Hello,
In my VBA code there is a loop in column Y and there is formula in Column Z
Then i want to filter data "Success" in column Z
But it select only "Success" but not whatever the loop data in column Y
For example if i filtered manually "Success" there total number of rows filtered is 675
If i use macro then only 478 rows are visible

Could anyone please help me on this ? Below code is for your reference.
VBA Code:
For Each cell In Range("I2:I" & LR)
    If IsError(Range("Z" & cell.Row).Value) Then
      If cell.Value = "W100" Then
         Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W004"")"
      ElseIf cell.Value = "W102" Then
         Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W005"")"
      Else
         Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],RC[-16])"
      End If
    End If
    Next cell
    
    
    If WorksheetFunction.CountIf(Range("Z:Z"), "Error") > 0 Then
        With Sheets("PIR Template")
                With .Range("A1:AH1")
                     .AutoFilter
                     .AutoFilter Field:=26, Criteria1:="Error"
                End With
        End With
        Range("AA2:AA" & LR).Formula = "=VLOOKUP(RC[-2],ZLOE019!C1:C7,7,0)"
        Application.CutCopyMode = False
        Sheets("PIR Template").AutoFilterMode = False
    End If
    
    If WorksheetFunction.CountIf(Range("Z:Z"), "Success") > 0 Then
        With Sheets("PIR Template")
                With .Range("A1:AH1")
                     .AutoFilter
                     .AutoFilter Field:=26, Criteria1:="Success"
                End With
        End With
End if
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What happens with the code below?

VBA Code:
        With Sheets("PIR Template")
                With .Range("A1:AH" & .Range("A" & Rows.Count).End(xlUp).Row)
                     .AutoFilter
                     .AutoFilter Field:=26, Criteria1:="Success"
                End With
        End With
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Yes it filters only "Success" in column Z
But there is a Loop in column Y
If i filtered manually i mean without vba then visible rows are 675
but if i use VBA then visible rows are 457
Could you please tell me what happened ?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What is the significance of the loop in column Y? that is happening before the filter and so doesn't affect the filter.
Did you test what I posted to see if you get the same result?
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes i tested you code
I'm getting same error it filtered only 457 rows
Below is the y column loop for your reference.

VBA Code:
Sheets("PIR Template").Select
    Range("Z2:Z" & LR).Formula = "=VLOOKUP(RC[-1],ZLOE019!C1:C7,6,0)"
    
    For Each cell In Range("I2:I" & LR)
    If IsError(Range("Z" & cell.Row).Value) Then
      If cell.Value = "W100" Then
         Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W004"")"
      ElseIf cell.Value = "W102" Then
         Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W005"")"
      Else
         Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],RC[-16])"
      End If
    End If
    Next cell
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I can see the code in your previous post but that happens before the filter so shouldn't affect what it filters.

Can you upload a copy of your workbook to a free file-hosting site like www.box.com or www.dropbox.com, mark the file for sharing and post the link it provides in the thread.

Make sure that you alter any sensitive information before uploading the file.
 

Arunachaljois

Board Regular
Joined
Aug 1, 2020
Messages
57
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm unable to upload files in those websites

Here is my whole code and also i will provide the screenshot here
VBA Code:
For Each cell In Range("I2:I" & LR)
    If IsError(Range("Z" & cell.Row).Value) Then
      If cell.Value = "W100" Then
         Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W004"")"
      ElseIf cell.Value = "W102" Then
         Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],""W005"")"
      Else
         Range("Y" & cell.Row).Formula = "=CONCATENATE(RC[-23],""000"",RC[-20],RC[-16])"
      End If
    End If
    Next cell
    
    Sheets("PIR Template").AutoFilterMode = False
    If WorksheetFunction.CountIf(Range("Z:Z"), "Error") > 0 Then
        With Sheets("PIR Template")
                With .Range("A1:AH1")
                     .AutoFilter
                     .AutoFilter Field:=26, Criteria1:="Error"
                End With
        End With
        Range("AA2:AA" & LR).Formula = "=VLOOKUP(RC[-2],ZLOE019!C1:C7,7,0)"
        Application.CutCopyMode = False
        Sheets("PIR Template").AutoFilterMode = False
    End If
    
    Sheets("PIR Template").AutoFilterMode = False
    If WorksheetFunction.CountIf(Range("Z:Z"), "Success") > 0 Then
        With Sheets("PIR Template")
                With .Range("A1:AH1" & .Range("A" & Rows.Count).End(xlUp).Row)
                     .AutoFilter
                     .AutoFilter Field:=26, Criteria1:="Success"
                End With
        End With
        Range("AB2:AB" & LR).Formula = "=VLOOKUP(RC[-3],ZLOR014!C1:C62,11,0)"
        Range("AC2:AC" & LR).Formula = "=RC[-1]=RC[-18]"
        Range("AD2:AD" & LR).Formula = "=VLOOKUP(RC[-5],ZLOR014!C1:C62,16,0)"
        Range("AE2:AE" & LR).Formula = "=RC[-1]=ROUND(RC[-19],2)"
        Range("AF2:AF" & LR).Formula = "=VLOOKUP(RC[-7],ZLOR014!C1:C62,13,0)"
        Range("AG2:AG" & LR).Formula = "=RC[-1]=RC[-15]"
        Range("AH2:AH" & LR).Formula = "=VLOOKUP(RC[-9],ZLOR014!C1:C62,41,0)"
    Application.CutCopyMode = False
    Sheets("PIR Template").AutoFilterMode = False
    End If
    
    Range("A1").CurrentRegion.EntireColumn.AutoFit
    
    MsgBox "Validation Completed Sucessfully"
    Sheets("PIR Template").Range("Y1").Select
 

Attachments

  • 2020-08-16_130128.png
    2020-08-16_130128.png
    31.4 KB · Views: 2
  • 2020-08-16_130328.png
    2020-08-16_130328.png
    15.2 KB · Views: 2
  • 2020-08-16_130446.png
    2020-08-16_130446.png
    58 KB · Views: 1

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Screenshots aren't helping me I'm afraid, I need to see the file so I will have to leave the thread.
Hopefully someone else can see something.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,335
Office Version
  1. 365
Platform
  1. Windows
@Arunachaljois
For future reference, while we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Cross posted https://www.myonlinetraininghub.com/excel-forum/vba-macros/vlookup-in-happening-of-loop-data-in-vba
 

Watch MrExcel Video

Forum statistics

Threads
1,123,436
Messages
5,601,664
Members
414,464
Latest member
JamesonCreevey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top