Filter doesn't recognize value

[reeser]

I am trying to filter data in one sheet depending on a variable in another. But even though I have the same formatting in both sheets it will not recognize the number. I have tried everything from pasting values, to pasting the same formats in both areas, using the F2 option, but nothing seems to work. My single value is in Sheet1 B2. The rest of the data I'm filtering is in Exceptions2 with the filter criteria residing in column A. I have headers in row 1.

Code that is running the process is below:

Sub Recall_BT()


Sheets("DataEntry").Unprotect
Sheets("Exceptions2").Visible = True

Dim rr As Range, r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim rCell As Variant

rCell = Sheets("Sheet1").Range("g3")


Dim sh As Worksheet
Dim wsCopyTo As Worksheet

Application.ScreenUpdating = False
Set sh = Worksheets("Exceptions2")
sh.Select
sh.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=rCell
Set rr = ActiveSheet.AutoFilter.Range
Set rr = rr.Offset(1, 0).Resize(rr.Rows.Count - 1, 1)
On Error Resume Next
 Set r1 = rr.SpecialCells(xlVisible)
On Error GoTo 0
If r1 Is Nothing Then
  MsgBox "No visible cells"
  Exit Sub
End If
Set r2 = Intersect(r1.EntireRow, sh.Range("F:L").EntireColumn)
Set r3 = Intersect(r1.EntireRow, sh.Range("N:W").EntireColumn)
Set wsCopyTo = Worksheets("BackTemp1")
r2.Copy
wsCopyTo.Range("I14").PasteSpecial _
       Paste:=xlPasteValues
r3.Copy
wsCopyTo.Range("Q14").PasteSpecial _
       Paste:=xlPasteValues
r2.EntireRow.Delete

Sheets("Exceptions2").Visible = False
Sheets("DataEntry").Protect

Application.ScreenUpdating = True
End Sub

 

[Ragnar1211]

I realize it is showing nothing, but if you manually click on the filter, what value does it say?

 

[reeser]

If I manually select the number I want it filters perfectly. I've even put in an IF formula to see if the cells were matching that way. The formula returns shows that they do. It is simply something with the code that does not match the value in Sheet1 G3 with the filter but I can't figure it out.

 

[Ragnar1211]

Ever feel like the person you are writing to doesnt understand what you are saying? Let me try again:

1) Press Alt+F11 to go to the VBA screen
2) Find the filter macro that you are trying to fix
3) Insert a break point at this line:

Set rr = ActiveSheet.AutoFilter.Range <<<----------- Break at this line

4) Run the macro, it will stop at the line we broke it at.
5) Goto excel, it should be on the filtered page, everthing will be hidden because of the filter.

6) Click on the filtered column and go to custom - it will now tell you what it did filter it by.

Hopefully this will give you the reason why your code is not working.

 

[reeser]

Ever feel like the person you are writing to doesnt understand what you are saying? Let me try again:

1) Press Alt+F11 to go to the VBA screen
2) Find the filter macro that you are trying to fix
3) Insert a break point at this line:

Set rr = ActiveSheet.AutoFilter.Range <<<----------- Break at this line

4) Run the macro, it will stop at the line we broke it at.
5) Goto excel, it should be on the filtered page, everthing will be hidden because of the filter.

6) Click on the filtered column and go to custom - it will now tell you what it did filter it by.

Hopefully this will give you the reason why your code is not working.

Yeah I feel like that all the time.

What I'm trying to say is, that is the exact process I've gone through. I run the macro with the break line. It brings up a blank page b/c it can't find the value from G3. When I check the filter (hence me checking it manually) it shows that it tried filtering for that value. That is why I think something is getting jacked in the formatting which causes the filter to not read it as it should. I do appreciate your help greatly. I'm just getting very frustrated by not finding a solution.

 

[Ragnar1211]

If you want another set of eyes you could upload the actual file to me and I could have a look at it. Send me a pm if you want.