AdvanceFilter won't work in VBA on dates (works manually)

[Lukasier]

Hi,

I am using advance filtering in my code to filter based on LT or WT, both are stored as text like "03/21" or "12/21" where first 2 digits represent weeks and last 2 digits represent year.

It works just fine with weeks > 10 like "12/21" or "14/21", but it does not work for weeks < 10 like "04/21" or "08/21".

It works when I do it manually, I believe it's caused by "cells containing year represent as 2 digits" error.

Someone have any ideas how to fix it?

Sub GetDatas()

Call SystemsOFF

Dim MomentaryRange As Range
Dim FilteringRange As Range
Dim FilteredRange As Range
Dim RangeToPaste As Range
Dim ClearRange As Range
Dim UnhideRange As Range
Dim LastRow As Integer


Set UnhideRange = Offline_zbiorczy.Range("A4:A300").EntireRow
Set ClearRange = Offline_zbiorczy.Range("B4:CD300")
Set FilteredRange = Offline.Range("A2").CurrentRegion
Set FilteringRange = Dodatkowe_Tabele.Range("C1:D2")
Set MomentaryRange = Temp.Range("A1:CC1")


    UnhideRange.Hidden = False
    
    ClearRange.ClearContents
    
    FilteredRange.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=FilteringRange, CopyToRange:= _
        MomentaryRange, Unique:=False
    
    LastRow = MomentaryRange.CurrentRegion.Rows.Count + 3
    
    Offline_zbiorczy.Range("B4:CD" & LastRow).Value2 = MomentaryRange.CurrentRegion.Offset(1, 0).Value2
    
    Offline_zbiorczy.Range(Cells(LastRow, 1), Cells(300, 1)).EntireRow.Hidden = True

    MsgBox "Dane zostały wczytane", vbInformation, "Operacja ukończona"
    
Call SystemsON

End Sub

 

[Marc L]

Hi,​

​

I never had such issue with an advanced filter with dates but here this is very not date but text ​

​

So you must obviously try with the double quotes like <"10" like manually …​

 

[Lukasier]

Well, because that is not really a date, but a string representation of a week in a year. I need it in that format for many more macros

 

[Lukasier]

Using double quotes didnt work :/

 

[Marc L]

​

In fact the text criteria must be between quotes rather than double quotes like for example <'10' …​

 

[Lukasier]

When I get this little error, macro won't filter any rows

When weeks are over 10, then error isn't there anymore and macro filters just fine.

These 4 cells I am using for advancefiltering

 

[Lukasier]

"*" & criteria & "*" worked..... VBA is so funny sometimes :D

 

[Marc L]

​

Those post #6 criterias seem to not match your initial explanation ! What do you think those criterias can filter ?​

​

Yes according to post #5 with quotes well working on my side this is obviously not funny​

but just logic as "*" means search for text rather than for any number …​

​