Getting in filter up the particular Criteria

A

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
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
 
Upvote 0
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 ?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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: 3
  • 2020-08-16_130328.png
    2020-08-16_130328.png
    15.2 KB · Views: 3
  • 2020-08-16_130446.png
    2020-08-16_130446.png
    58 KB · Views: 2
Upvote 0
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.
 
Upvote 0
@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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA to correct the sequential number for generated multiple workbooks
Replies
1
Views
388
Joe4
Joe4
C
  • Question
Random selection of filtered rows.
Replies
2
Views
883
cspengel
C
Y
  • Question
Extract X rows after using Auto filter
Replies
8
Views
460
BSALV
BSALV
A
  • Solved
Help tweeking a Vstack and Filter formula
Replies
1
Views
258
StephenCrump
S
T
  • Question
VBA to filter and extract data
Replies
0
Views
972
tarunrag98
T

Forum statistics

Threads
1,214,821
Messages
6,121,755
Members
449,049
Latest member
excelknuckles

Share this page

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
Back
Top