why does criteria1:=argument work but not criteria1:="<>argument"

[anzer]

[FONT=&quot]Hi, So in my macro I have[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Dim TradeDate As Range[/FONT]
[FONT=&quot]Set TradeDate = Active sheet.Range("AA1")[/FONT]
[FONT=&quot]Sheets("Sheet1").Activate[/FONT]
[FONT=&quot]Range("A1").AutoFilter Field:=4, Criteria1:=TradeDate[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]The filtered results are good in my pivot table[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]But when I need this in another macro with the same date:[/FONT]
[FONT=&quot]Dim TradeDate As Range[/FONT]
[FONT=&quot]Set TradeDate = Active sheet.Range("AA1")[/FONT]
[FONT=&quot]Sheets("Sheet1").Activate[/FONT]
[FONT=&quot]Range("A1").AutoFilter Field:=4, Criteria1:="<>TradeDate"[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Basically, just the criteria1 that changes and it it not working, because it is showing the date I want to filter out. Why is it doing this???[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Thank you

Just in case, TradeDate is given like this:

[/FONT]
Range("AA1").Select
ActiveCell.FormulaR1C1 = "=IF(WEEKDAY(TODAY())=2,TODAY()-3,TODAY()-1)"
Selection.NumberFormat = "dd.mm.yyyy"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""dd.mm.yyyy"")"
Range("AB1").Select
Selection.Copy
Range("AC1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set myCell = Range("AC1")
With myCell
.Application.SendKeys "{F2}"
.Application.SendKeys "{ENTER}"
End With
Range("AA1:AB1").Select
Selection.Delete Shift:=xlToLeft

[FONT=&quot]
[/FONT]

 

[dmt32]

Try

Range("A1").AutoFilter Field:=4, Criteria1:="<>" & TradeDate

Dave

 

[anzer]

Try

Range("A1").AutoFilter Field:=4, Criteria1:="<>" & TradeDate

Dave

Hello Dave, I already have tried it and it is not working
The pivot table data is in Date format dd/mm/yyyy and the TradeDate cell is also in date format dd/mm/yyyy
I even copied both date in another sheet and did if both cells are equal then 1 if false 0 and the 2 are equals so I don,t know what I am doing wrong at this point

Thank you

 

[dmt32]

Hello Dave, I already have tried it and it is not working
The pivot table data is in Date format dd/mm/yyyy and the TradeDate cell is also in date format dd/mm/yyyy
I even copied both date in another sheet and did if both cells are equal then 1 if false 0 and the 2 are equals so I don,t know what I am doing wrong at this point

Thank you

Autofilter & dates can sometimes be a problem as need to think USA date format

try this

Range("A1").AutoFilter Field:=4, Criteria1:="<>" & CLng(TradeDate.Value)

Dave

 

[anzer]

Autofilter & dates can sometimes be a problem as need to think USA date format

try this

Range("A1").AutoFilter Field:=4, Criteria1:="<>" & CLng(TradeDate.Value)

Dave

Hello Dave, This line has a bug
When I click on TradeDate.value, it does show me 21/09/2018 but the
CLnh(TradeDate.Value) shows Type Mismatch<type mismatch=""><type mismatch="">

Thank you</type></type>

 

[dmt32]

Hello Dave, This line has a bug
When I click on TradeDate.value, it does show me 21/09/2018 but the
CLnh(TradeDate.Value) shows <type mismatch="">

Thank you

If the line that bugs you posted is the same in your code then you need to copy the post correctly

CLng(TradeDate.Value)

Using Type Conversion function assumes that value in your range is a real date.

Dave

</type>

 

[anzer]

If the line that bugs you posted is the same in your code then you need to copy the post correctly

CLng(TradeDate.Value)

Using Type Conversion function assumes that value in your range is a real date.

Dave

This is the line in my code
Range("A1").AutoFilter Field:=4, Criteria1:="<>" & CLng(TradeDate.Value)
And still bug

 

[dmt32]

This is the line in my code
Range("A1").AutoFilter Field:=4, Criteria1:="<>" & CLng(TradeDate.Value)
And still bug

whats the error you are getting?

Dave

 

[anzer]

If the line that bugs you posted is the same in your code then you need to copy the post correctly

CLng(TradeDate.Value)

Using Type Conversion function assumes that value in your range is a real date.

Dave

It just worked! I hade to double click on the AA1 set as TradeDate to make it officially a date in Excel,
sometime it is always stupid mistakes like this ahah

Thank you again!!

 

[dmt32]

It just worked! I hade to double click on the AA1 set as TradeDate to make it officially a date in Excel,
sometime it is always stupid mistakes like this ahah

Thank you again!!

no worries - good all resolved

Dave