Excel VBA Autofilter не работает с столбцом Date

У меня возникла проблема с использованием AutoFilter с VBA в Excel.

Он хорошо работает для регулярных фильтров, но фильтрация столбца даты не работает должным образом. Столбец отформатирован как дата, я могу отфильтровать его вручную и абсурдно, если я запустил свой код, он ничего не фильтрует, но когда я проверяю фильтр, а затем только ОК (без изменений к критериям фильтра), он начинает правильно фильтровать.

Вот мой код:

ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
        :=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
        "<=" & CDate(WorksheetFunction.EoMonth([datecell], 3))

У кого-то есть идея? Это, кажется, общая проблема, но я не нашел решения.

Спасибо заранее.

Изменить: просто добавьте, когда я сделаю макрос и запустил записанный макрос, он тоже не работает.

Ответ 1

Даты могут быть сложными с помощью Excel VBA AutoFilter. Некоторым легче просто прокрутить массив, который нужно отфильтровать.

Иногда я обнаружил, что можно использовать числовое значение даты, особенно когда речь идет о "датах между"

Criteria1:= ">" & CDbl([datecell])
Criteria2:= "<=" & CDbl(WorksheetFunction.EoMonth([datecell], 3))

Обратите внимание, что указанные выше должны быть "настоящими датами", а не строками, которые выглядят как даты. Даже одна "строковая дата" будет испорчена.

Ответ 2

Autofilter() работает с "универсальным" форматом yyyy-mm-dd, то есть:

Criteria1:= ">" & Format([MY_DATE],"yyyy-mm-dd")
Criteria2:= "<=" & Format([MY_DATE],"yyyy-mm-dd")

Это лучше, потому что Excel не может "понять" это неправильно. Если вы используете mm/dd/yyyy или dd/mm/yyyy, Excel может соответствовать 02/jan как 01/feb.

см: http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

Правила работы с Excel (Международный выпуск)

Когда вам нужно преобразовать числа и даты в строки для передачи в Excel (например, в критериях для строк AutoFilter или .Formula), всегда явно конвертировать данные в строку в формате US, используя Trim (Str (MyNumber)), или функции sNumToUS(), показанной ранее, для всех типов номеров и дат. Затем Excel будет использовать его правильно и преобразовать в локальные форматы число/дата.

Edit:

Мы можем создать универсальную функцию, используя Application.International like:

Sub MySub()
    Select Case application.International(xlDateOrder)
        Case Is = 0
            dtFormat = "mm/dd/yyyy"
        Case Is = 1
            dtFormat = "dd/mm/yyyy"
        Case Is = 2
            dtFormat = "yyyy/mm/dd"
        Case Else
            dtFormat = "Error"
    End Select

    Debug.Print (dtFormat)

    ...
    Criteria1:= ">" & Format([MY_DATE],dtFormat)
    Criteria2:= "<=" & Format([MY_DATE],dtFormat)
    ...

End Sub

Ответ 3

вам нужно преобразовать формат в американский формат, например: " > " и "Формат" ([datecell] "," mm/dd/yyyy ") VBA не понимает другого формата.

Ответ 4

Этот синтаксис работает для меня:

.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))

Подсказка, полученная при регистрации макроса

Ответ 5

Карлос Энрике, Спасибо за предложение формата ([datecell], "mm/dd/yyyy" ). Он работает в моем файле. Мой предыдущий код:

Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
  Criteria1:=">=" & StrtDt, _
  Operator:=xlAnd, _
  Criteria2:="<=" & EndDt

Моим измененным кодом является:

Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
  Criteria1:=">=" & Format(StrtDt, "mm/dd/yyyy"), _
  Operator:=xlAnd, _
  Criteria2:="<=" & Format(EndDt, "mm/dd/yyyy")

Спасибо.

Ответ 6

Сопоставьте свой "dd-mm-yyy" с форматом столбца, поэтому, если у вас есть "16-Aug-16" в качестве форматирования исходных данных, сделайте фильтр "dd-mmm-yy"

Ответ 7

Одним из решений является правильная настройка формата целевого столбца. Если он отформатирован как "пользовательский", даже если он выглядит как дата, он не будет работать.

Вы просите VBA фильтровать по дате, поэтому формат столбцов также должен быть датой.