VBA过滤日期变成美国格式 [英] VBA filtering on date turns into US format

问题描述

我正在尝试过滤日期。经过检查，控制面板知道我在英国。但是，当我过滤它时-它有问题，似乎正在使用美国日期格式，而不是英国。当您单击过滤器时，它会根据英国系统正确地提供日期/月份。

I am trying to filter on a date. Have checked and the control panel knows I am in the UK. But when I filter it - it has problems and seems to be using the US date format and not the UK. When you click on the filter it gives the dates / months correctly according to the UK system.

当我记录代码以查看发生了什么之后，出现了以下内容。日期将被定义-我只是想弄清楚当我的过滤器不起作用时VBA在做什么。

When I recorded the code to see what was happening the following came up. The date will be defined - I was just trying to figure out what VBA was doing when my filter wouldn't work.

Sub dating()


ActiveSheet.Range("$A$1:$AM$442").AutoFilter Field:=38, Operator:= _
    xlFilterValues, Criteria2:=Array(2, "4/25/2014")
End Sub

我已将代码更新为，即使该列中有该日期的数据，它也不会在该日期进行过滤。我可能在这里缺少明显的东西。

I have updated the code to and it still won't filter on that date even though there is data with that date in the column. I am probably missing something obvious here.

Dim dteInvoices As Date

dteInvoices = Worksheets("front sheet").Range("f14")

Worksheets("trade details").Range("A1").AutoFilter 39, strText
             .Range("A1").AutoFilter 38, CLng(dteInvoices)

感谢 http://www.ozgrid.com/VBA/autofilter-vba-dates.htm 我设法将它工作

http：/ /www.ozgrid.com/VBA/autofilter-vba-dates.htm

Range("a1").AutoFilter Field:=38, Criteria1:=">=" & lngDate, _
    Operator:=xlAnd, Criteria2:="<" & lngDate + 1

推荐答案

不是错误。在VBA中的任何地方，您都必须遵守美国惯例。这很好：否则，将不得不根据用户设置使用不同的字符串！

我通常使用 Format（MyDate， \＃mm\ / dd\ / yy \＃）确定。

Not a bug. Everywhere in VBA you MUST you US conventions. Which is good: otherwise one would have to use different strings depending on user settings !
I generally use Format(MyDate, "\#mm\/dd\/yy\#") to be sure.

这篇关于VBA过滤日期变成美国格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！