• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autofilter code not returning results

ThrottleWorks

Excel Ninja
Hi,

I am using below mentioned code to filter values from a sheet.
I have used this code multiple times in other modules of the macro and it's working fine. (for a different file)

I am getting error while running this code in a particular module only.

Please see below the details what this code (is supposed to) do.

I have 2 files.
File 1 has around 10 line items, I need to search these line items in File 2 according to the criteria mentioned in the code.

In the below mentioned code "301" in the worksheet name in File 2.
I have defined MFi for sheet "301".

Details in sheet MFi starts from Column B, Column A is blank.
Gs3 is the range of non blank cells in Sheet MFi.

Prod3 is defined as 3 (dim Prod3 as long, Prod3 = 3).

"Rng.Offset(0, 7).Value" is the key word that I am trying to find in Column C of the MFi sheet.

When I run the below code I did not get any results however if I do it manually I am getting valid results.

Can anyone please help me understandign why it is not working.

PS - Sorry for not uploading file, I am aware it is difficult to solve without sample file.

Can anyone please tell me if there is any fundamental mistake in this code.
I am confused because same code is working fine with other files. :(

Code:
If Rng.Offset(0, 5).Value = "301" Then
  MFi.Select
  Range("b3").Select
  Selection.AutoFilter
 
 
  'Find Product (Contains)
  Gs3.AutoFilter Field:=Prod3, Criteria1:="*" & Rng.Offset(0, 7).Value & " * """
 
 
  'Find Currency
  Gs3.AutoFilter Field:=Curr4, Criteria1:="*" & Rng.Offset(0, -4).Value & "*"
 
 
  'Getting LR to check if there are any records
  Lr3 = MFi.Cells(Rows.Count, 17).End(xlUp).Row
 
 
  If Lr3 > 2 Then
  Lr19 = Sht.Cells(Rows.Count, 74).End(xlUp).Row + 3
  Gs3.SpecialCells(xlCellTypeVisible).Copy Destination:=Sht.Cells(Lr19, 74)
  'Coppying Desc
  Sht.Cells(Lr19 - 1, 77) = Rng.Value
  Sht.Cells(Lr19 - 1, 79) = Rng.Offset(0, 9).Value
  End If
  End If
 
Back
Top