Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск двух одновременных условий даты и времени макросом - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Поиск двух одновременных условий даты и времени макросом (Макросы/Sub)
Поиск двух одновременных условий даты и времени макросом
Jerdas Дата: Вторник, 07.11.2017, 21:16 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Доброго времени суток, господа форумчане!
Проконсультируйте, пожалуйста, либо помогите исправить код в макросе (см.вложение).
Задача такова, что имеется таблица с входными данными, обновляющаяся раз в минуту из CSV файла.
1-ый столбец - Дата
2-ой столбец - Время
3-ий и последующие - это значения на определенную дату и время.
Необходимо, чтобы макрос сравнивал данные введенные в пользовательскую UserForm с 4-мя text-boxами, 2 из которых Дата (с-по), 2 других Время (с-по)
с первым и вторым столбцом соответственно.
Например, вводя в форму такие данные: "с 03.11.2017 по 07.11.2017 и с 10:30 по 13:30"
Мы должны получить табличку начинающуюся строкой 03.11.2017 10:30 и оканчивающуюся строкой 07.11.2017 13:30, а между первой и последней строкой, соответсвенно должны располагаться строки попадающие в заданный диапазон.
По-моему мению, я могу очень сильно заблуждаться, макрос должен выполнить следующий алгоритм:
1. Найти в первом столбце первое вхождение даты равной либо большей от "Начальной" (т.е. введенной в ТекстБокс1), и найти во втором столбце время равное либо большее от "Начального" (т.е. введенного в ТекстБокс3) - при нахождении и совпадении двух этих параметров, код должен определить такую строку, как первую для дальнейшей выборки (копирования).
2. Найти в первом столбце последнее вхождение даты равной либо меньшей от "Конечной" (т.е. введенной в ТекстБокс2), и найти во втором столбце время равное либо меньшее от "Начального" (т.е. введенного в ТекстБокс4) - при нахождении и совпадении двух этих параметров, код должен определить такую строку, как последнюю для дальнейшей выборки (копирования).
Загводка в том, что удалось, на данный момент, сделать выборку только по дате, одновременно с временем форма выборки работать отказывается.
Возможно, дело в неправильно заданных операторах либо параметрах TimeStart/TimeFinish
[vba]
Код
          If Cells(i, 1) >= DateStart And Cells(i, 2) >= TimeStart Then 'Ищем начальную дату в первом столбце и начальное время во втором
    
                If Cells(i, 1) <= DateFinish And Cells(i, 2) <= TimeFinish Then 'Ищем конечную дату в первом столбце и конечное время
[/vba]
Прикладываю два файла:
1 - ый, где функционирует выборка по дате, и выборка времени просто "закомменчена"
2 - ой, где время и дата связаны через And, но не работает выборка, ни по дате, ни по времени
Пожалуйста, помогите разобраться! Заранее благодарна.
_______________________________________________
Отдельное спасибо Parovoznic за неоценимые помощь и поддержку на всех этапах реализации.
К сообщению приложен файл: __-__-.xlsm(55Kb) · ____-_-.xlsm(54Kb)
 
Ответить
СообщениеДоброго времени суток, господа форумчане!
Проконсультируйте, пожалуйста, либо помогите исправить код в макросе (см.вложение).
Задача такова, что имеется таблица с входными данными, обновляющаяся раз в минуту из CSV файла.
1-ый столбец - Дата
2-ой столбец - Время
3-ий и последующие - это значения на определенную дату и время.
Необходимо, чтобы макрос сравнивал данные введенные в пользовательскую UserForm с 4-мя text-boxами, 2 из которых Дата (с-по), 2 других Время (с-по)
с первым и вторым столбцом соответственно.
Например, вводя в форму такие данные: "с 03.11.2017 по 07.11.2017 и с 10:30 по 13:30"
Мы должны получить табличку начинающуюся строкой 03.11.2017 10:30 и оканчивающуюся строкой 07.11.2017 13:30, а между первой и последней строкой, соответсвенно должны располагаться строки попадающие в заданный диапазон.
По-моему мению, я могу очень сильно заблуждаться, макрос должен выполнить следующий алгоритм:
1. Найти в первом столбце первое вхождение даты равной либо большей от "Начальной" (т.е. введенной в ТекстБокс1), и найти во втором столбце время равное либо большее от "Начального" (т.е. введенного в ТекстБокс3) - при нахождении и совпадении двух этих параметров, код должен определить такую строку, как первую для дальнейшей выборки (копирования).
2. Найти в первом столбце последнее вхождение даты равной либо меньшей от "Конечной" (т.е. введенной в ТекстБокс2), и найти во втором столбце время равное либо меньшее от "Начального" (т.е. введенного в ТекстБокс4) - при нахождении и совпадении двух этих параметров, код должен определить такую строку, как последнюю для дальнейшей выборки (копирования).
Загводка в том, что удалось, на данный момент, сделать выборку только по дате, одновременно с временем форма выборки работать отказывается.
Возможно, дело в неправильно заданных операторах либо параметрах TimeStart/TimeFinish
[vba]
Код
          If Cells(i, 1) >= DateStart And Cells(i, 2) >= TimeStart Then 'Ищем начальную дату в первом столбце и начальное время во втором
    
                If Cells(i, 1) <= DateFinish And Cells(i, 2) <= TimeFinish Then 'Ищем конечную дату в первом столбце и конечное время
[/vba]
Прикладываю два файла:
1 - ый, где функционирует выборка по дате, и выборка времени просто "закомменчена"
2 - ой, где время и дата связаны через And, но не работает выборка, ни по дате, ни по времени
Пожалуйста, помогите разобраться! Заранее благодарна.
_______________________________________________
Отдельное спасибо Parovoznic за неоценимые помощь и поддержку на всех этапах реализации.

Автор - Jerdas
Дата добавления - 07.11.2017 в 21:16
Kuzmich Дата: Вторник, 07.11.2017, 21:46 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 318
Репутация: 60 ±
Замечаний: 0% ±

Excel 2003
Цитата
2 - ой, где время и дата связаны через And, но не работает выборка, ни по дате, ни по времени

[vba]
Код

If Cells(i, 1) >= DateStart And Cells(i, 2) >= Val(TimeStart) / 24 Then 'Ищем начальную дату в первом столбце и начальное время во втором
          
'         ---------------------- ДОБАВКА (МОЖЕТ И НЕПРАВИЛЬНО ?---------------------------------------------------
                If Cells(i, 1) <= DateFinish And Cells(i, 2) <= Val(TimeFinish) / 24 Then
[/vba]
 
Ответить
Сообщение
Цитата
2 - ой, где время и дата связаны через And, но не работает выборка, ни по дате, ни по времени

[vba]
Код

If Cells(i, 1) >= DateStart And Cells(i, 2) >= Val(TimeStart) / 24 Then 'Ищем начальную дату в первом столбце и начальное время во втором
          
'         ---------------------- ДОБАВКА (МОЖЕТ И НЕПРАВИЛЬНО ?---------------------------------------------------
                If Cells(i, 1) <= DateFinish And Cells(i, 2) <= Val(TimeFinish) / 24 Then
[/vba]

Автор - Kuzmich
Дата добавления - 07.11.2017 в 21:46
Jerdas Дата: Вторник, 07.11.2017, 22:02 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Kuzmich, Добрый вечер!
К сожалению, не сработало...:-(
Не могли бы Вы глянуть наметанным взглядом на весь код в целом?
Примеры я выложила, но могу вставить и в тело сообщения, если необходимо.
Благодарю.
 
Ответить
СообщениеKuzmich, Добрый вечер!
К сожалению, не сработало...:-(
Не могли бы Вы глянуть наметанным взглядом на весь код в целом?
Примеры я выложила, но могу вставить и в тело сообщения, если необходимо.
Благодарю.

Автор - Jerdas
Дата добавления - 07.11.2017 в 22:02
Kuzmich Дата: Вторник, 07.11.2017, 22:24 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 318
Репутация: 60 ±
Замечаний: 0% ±

Excel 2003
Я смотрел 2-ой пример, время вводил от 10 до 12, отчет сформировался
 
Ответить
СообщениеЯ смотрел 2-ой пример, время вводил от 10 до 12, отчет сформировался

Автор - Kuzmich
Дата добавления - 07.11.2017 в 22:24
Jerdas Дата: Вторник, 07.11.2017, 23:05 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Kuzmich, Спасибо Вам огромное! Прошу прощения, после Вашего комментария, проверила на другом компьютере с другой версией Excel и действительно все формируется как надо.
Если Вам не сложно, подскажите, пожалуйста, почему время прописывается именно так [vba]
Код
Val(TimeStart) / 24
[/vba]
Не отказалась бы и от ссылочки на литературу, если такая имеется, так как только учусь VBA ^_^
 
Ответить
СообщениеKuzmich, Спасибо Вам огромное! Прошу прощения, после Вашего комментария, проверила на другом компьютере с другой версией Excel и действительно все формируется как надо.
Если Вам не сложно, подскажите, пожалуйста, почему время прописывается именно так [vba]
Код
Val(TimeStart) / 24
[/vba]
Не отказалась бы и от ссылочки на литературу, если такая имеется, так как только учусь VBA ^_^

Автор - Jerdas
Дата добавления - 07.11.2017 в 23:05
Kuzmich Дата: Вторник, 07.11.2017, 23:17 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 318
Репутация: 60 ±
Замечаний: 0% ±

Excel 2003
На этом сайте посмотрите и скачайте литературу
http://www.excelworld.ru/index/vba/0-12
Время у вас вводится в текстовом формате, поэтому его надо преобразовать в значение.
Время в VBA - это дробная часть от 24 часового дня, поэтому делим на 24.
Удачи!
 
Ответить
СообщениеНа этом сайте посмотрите и скачайте литературу
http://www.excelworld.ru/index/vba/0-12
Время у вас вводится в текстовом формате, поэтому его надо преобразовать в значение.
Время в VBA - это дробная часть от 24 часового дня, поэтому делим на 24.
Удачи!

Автор - Kuzmich
Дата добавления - 07.11.2017 в 23:17
Jerdas Дата: Вторник, 07.11.2017, 23:25 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Kuzmich, Еще раз благодарю Вас за ответ!
Внесу небольшую ремарку в предыдущий отзыв: Данный код работает только для "круглых" часов (10:00, 11:00 и тп). Собственно потому у меня ничего в первые разы и не формировалось, так как я вводила часы и минуты.
 
Ответить
СообщениеKuzmich, Еще раз благодарю Вас за ответ!
Внесу небольшую ремарку в предыдущий отзыв: Данный код работает только для "круглых" часов (10:00, 11:00 и тп). Собственно потому у меня ничего в первые разы и не формировалось, так как я вводила часы и минуты.

Автор - Jerdas
Дата добавления - 07.11.2017 в 23:25
Kuzmich Дата: Среда, 08.11.2017, 08:39 | Сообщение № 8
Группа: Проверенные
Ранг: Обитатель
Сообщений: 318
Репутация: 60 ±
Замечаний: 0% ±

Excel 2003
Цитата
так как я вводила часы и минуты.

В каком формате?
 
Ответить
Сообщение
Цитата
так как я вводила часы и минуты.

В каком формате?

Автор - Kuzmich
Дата добавления - 08.11.2017 в 08:39
Jerdas Дата: Среда, 08.11.2017, 12:02 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
В каком формате?

Вводила в TextBox формы, так что в текстовом.
Кстати, обнаружилась еще одна немаловажная деталь. Если использовать при введении в ActivForm дату, которая раньше, чем начальная дата из основной таблицы, то время учитывается на следующую найденную с того, которое задано в ActiveForm.
Поясню на примере:
Имеем входные данные в таблице:
Дата Время Значение 1
03.11.2017 10:00 1
03.11.2017 11:00 2
04.11.2017 10:00 3
04.11.2017 12:00 4
05.11.2017 12:30 5

Выбираем в ActiveForm начальную дату 02.11.2017 / конечную дату 05.11.2017
Выбираем в ActiveForm начальное время 11:00 / конечное время 12:00
В итоге получаем выборку в таком виде:

Дата Время Значение 1
03.11.2017 11:00 2
04.11.2017 10:00 3
04.11.2017 12:00 4

А должно было быть:
Дата Время Значение 1
03.11.2017 10:00 1
03.11.2017 11:00 2
04.11.2017 10:00 3
04.11.2017 12:00 4
Получается, что конечная дата и время отрабатываются правильно, а начальная ищет совпадение следующим образом, если в первом столбце не найдена дата, то +1 день, если не найдена, то снова +1 день и только после этого ищет еще и совпадение по времени. Получается, что если у меня в исходной таблице будут данные за 3 число с 0:01 по 23:59, то при заданных параметрах 02.11.2017 / 05.11.2017 11:00 / 12:00, выборка начнется не с 03.11.2017 0:01, а с 03.11.2017 11:00.
Попробовала задать через Else, но тогда вообще не происходит формирования, но и ошибок не выдает. Просто пустая таблица выборки.
Господа форумчане, подскажите, пожалуйста, в чем может быть дело?
 
Ответить
Сообщение
В каком формате?

Вводила в TextBox формы, так что в текстовом.
Кстати, обнаружилась еще одна немаловажная деталь. Если использовать при введении в ActivForm дату, которая раньше, чем начальная дата из основной таблицы, то время учитывается на следующую найденную с того, которое задано в ActiveForm.
Поясню на примере:
Имеем входные данные в таблице:
Дата Время Значение 1
03.11.2017 10:00 1
03.11.2017 11:00 2
04.11.2017 10:00 3
04.11.2017 12:00 4
05.11.2017 12:30 5

Выбираем в ActiveForm начальную дату 02.11.2017 / конечную дату 05.11.2017
Выбираем в ActiveForm начальное время 11:00 / конечное время 12:00
В итоге получаем выборку в таком виде:

Дата Время Значение 1
03.11.2017 11:00 2
04.11.2017 10:00 3
04.11.2017 12:00 4

А должно было быть:
Дата Время Значение 1
03.11.2017 10:00 1
03.11.2017 11:00 2
04.11.2017 10:00 3
04.11.2017 12:00 4
Получается, что конечная дата и время отрабатываются правильно, а начальная ищет совпадение следующим образом, если в первом столбце не найдена дата, то +1 день, если не найдена, то снова +1 день и только после этого ищет еще и совпадение по времени. Получается, что если у меня в исходной таблице будут данные за 3 число с 0:01 по 23:59, то при заданных параметрах 02.11.2017 / 05.11.2017 11:00 / 12:00, выборка начнется не с 03.11.2017 0:01, а с 03.11.2017 11:00.
Попробовала задать через Else, но тогда вообще не происходит формирования, но и ошибок не выдает. Просто пустая таблица выборки.
Господа форумчане, подскажите, пожалуйста, в чем может быть дело?

Автор - Jerdas
Дата добавления - 08.11.2017 в 12:02
RAN Дата: Среда, 08.11.2017, 12:44 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4602
Репутация: 925 ±
Замечаний: 0% ±

2010
[vba]
Код
     If (CDbl(Cells(i, 1)) + CDbl(Cells(i, 2))) >= (CDbl(DateStart) + CDbl(1 / 24 * TimeStart)) Then    'Ищем начальную дату в первом столбце и начальное время во втором
                If CDbl(Cells(i, 1)) + CDbl(Cells(i, 2)) <= CDbl(DateFinish) + CDbl(1 / 24 * TimeFinish) Then    'Ищем конечную дату в первом столбце и конечное время
                    .Cells(LR, 1).Resize(, 7).Value = Cells(i, 1).Resize(, 7).Value
                    .Cells(LR, 1).NumberFormat = "m/d/yyyy"
                    .Cells(LR, 2).NumberFormat = "[$-F400]h:mm:ss AM/PM"
                    LR = LR + 1    'Увеличили на единичку номер первой свободной строки отчета
                End If
            End If
[/vba]


Быть или не быть, вот в чем загвоздка!
 
Ответить
Сообщение[vba]
Код
     If (CDbl(Cells(i, 1)) + CDbl(Cells(i, 2))) >= (CDbl(DateStart) + CDbl(1 / 24 * TimeStart)) Then    'Ищем начальную дату в первом столбце и начальное время во втором
                If CDbl(Cells(i, 1)) + CDbl(Cells(i, 2)) <= CDbl(DateFinish) + CDbl(1 / 24 * TimeFinish) Then    'Ищем конечную дату в первом столбце и конечное время
                    .Cells(LR, 1).Resize(, 7).Value = Cells(i, 1).Resize(, 7).Value
                    .Cells(LR, 1).NumberFormat = "m/d/yyyy"
                    .Cells(LR, 2).NumberFormat = "[$-F400]h:mm:ss AM/PM"
                    LR = LR + 1    'Увеличили на единичку номер первой свободной строки отчета
                End If
            End If
[/vba]

Автор - RAN
Дата добавления - 08.11.2017 в 12:44
Jerdas Дата: Среда, 08.11.2017, 14:38 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
RAN, Здравствуйте!Благодарю Вас за ответ. К сожалению, выдает ошибку type mismatch на участках кода CDbl(1 / 24 * TimeFinish) и CDbl(1 / 24 * TimeStart)
С чем это может быть связанно? Возможно, виноват текстовый формат даты?
 
Ответить
СообщениеRAN, Здравствуйте!Благодарю Вас за ответ. К сожалению, выдает ошибку type mismatch на участках кода CDbl(1 / 24 * TimeFinish) и CDbl(1 / 24 * TimeStart)
С чем это может быть связанно? Возможно, виноват текстовый формат даты?

Автор - Jerdas
Дата добавления - 08.11.2017 в 14:38
Kuzmich Дата: Среда, 08.11.2017, 15:27 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 318
Репутация: 60 ±
Замечаний: 0% ±

Excel 2003
Я бы на листе "данные" в столбце H сделал колонку с датой и временем (в H4 =A4 + B4 и протянуть вниз)
Процедура Report тогда будет такая
[vba]
Код

Option Explicit
Public DateStart As Date 'Начало периода
Public DateFinish As Date 'Конец периода
Public TimeStart As String
Public TimeFinish As String
Public BeginDate As Date
Public EndDate As Date

Sub Report()
Dim i As Long, LastRow As Long, LR As Long
LR = 5 ' строка для нового отчёта будет =5
With Sheets("отчет")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row 'Нашли последнюю строку на втором листе
Range(.Cells(5, 1), .Cells(LastRow + 5, 7)).Clear 'Очистили диапазон отчета полностью
.Cells(1, 6) = "Сформировано :" & " " & Format(Now, "dd:mm:yy" & " " & "в" & " " & "hh:mm:ss") & ""
.Cells(3, 2) = "Отчет с :" & " " & DateStart & " по " & DateFinish & " " 'Заполнили заголовок отчета
LastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Нашли последнюю строку на первом листе
BeginDate = DateStart + Split(TimeStart, ":")(0) / 24 + Split(TimeStart, ":")(1) / 24 / 60
EndDate = DateFinish + Split(TimeFinish, ":")(0) / 24 + Split(TimeFinish, ":")(1) / 24 / 60
For i = 4 To LastRow 'Начали цикл (на первом листе) со строки № 4 по последнюю
If Cells(i, 8) >= BeginDate Then
'If Cells(i, 1) >= DateStart And Cells(i, 2) >= Val(TimeStart) / 24 Then 'Ищем начальную дату в первом столбце и начальное время во втором

' ---------------------- ДОБАВКА (МОЖЕТ И НЕПРАВИЛЬНО ?---------------------------------------------------
If Cells(i, 8) <= EndDate Then
[/vba]


Сообщение отредактировал Kuzmich - Среда, 08.11.2017, 15:29
 
Ответить
СообщениеЯ бы на листе "данные" в столбце H сделал колонку с датой и временем (в H4 =A4 + B4 и протянуть вниз)
Процедура Report тогда будет такая
[vba]
Код

Option Explicit
Public DateStart As Date 'Начало периода
Public DateFinish As Date 'Конец периода
Public TimeStart As String
Public TimeFinish As String
Public BeginDate As Date
Public EndDate As Date

Sub Report()
Dim i As Long, LastRow As Long, LR As Long
LR = 5 ' строка для нового отчёта будет =5
With Sheets("отчет")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row 'Нашли последнюю строку на втором листе
Range(.Cells(5, 1), .Cells(LastRow + 5, 7)).Clear 'Очистили диапазон отчета полностью
.Cells(1, 6) = "Сформировано :" & " " & Format(Now, "dd:mm:yy" & " " & "в" & " " & "hh:mm:ss") & ""
.Cells(3, 2) = "Отчет с :" & " " & DateStart & " по " & DateFinish & " " 'Заполнили заголовок отчета
LastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Нашли последнюю строку на первом листе
BeginDate = DateStart + Split(TimeStart, ":")(0) / 24 + Split(TimeStart, ":")(1) / 24 / 60
EndDate = DateFinish + Split(TimeFinish, ":")(0) / 24 + Split(TimeFinish, ":")(1) / 24 / 60
For i = 4 To LastRow 'Начали цикл (на первом листе) со строки № 4 по последнюю
If Cells(i, 8) >= BeginDate Then
'If Cells(i, 1) >= DateStart And Cells(i, 2) >= Val(TimeStart) / 24 Then 'Ищем начальную дату в первом столбце и начальное время во втором

' ---------------------- ДОБАВКА (МОЖЕТ И НЕПРАВИЛЬНО ?---------------------------------------------------
If Cells(i, 8) <= EndDate Then
[/vba]

Автор - Kuzmich
Дата добавления - 08.11.2017 в 15:27
Jerdas Дата: Среда, 08.11.2017, 16:17 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Kuzmich, Премного благодарна. Таким способом все работает идеально!
 
Ответить
СообщениеKuzmich, Премного благодарна. Таким способом все работает идеально!

Автор - Jerdas
Дата добавления - 08.11.2017 в 16:17
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Поиск двух одновременных условий даты и времени макросом (Макросы/Sub)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!