Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
Наложение фильтра средствами VBA.
 
#1
17.02.2011 03:27:55
Не буду просить сделать за меня задание прошу только помочь с подбором материала по этому вопросу!! Задача состоит в следующем: есть большая таблица, из неё составляем отчёты, в отчётах присутствуют ссылки на листы с уже отфильтрованными данными и например применёнными к ним функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ!! В итоге надо избавиться от этих промежуточных листов и программно всё сделать, чтобы в VBA мы наложили фильтр, затем применили к нужному столбцу функцию и занесли результат в лист с отчётом.
 
 
 
#2
17.02.2011 05:29:56
и пока единственный.
 
 
 
#3
17.02.2011 12:35:25
Добавлю всё-таки кусочек файла, повторюсь: как в нём средствами VBA сделать фильтр по столбцу Счёт, а затем например на столбец Кол-во наложить функцию Промежуточные.Итоги() и занести это всё в отдельный лист?? Если что надумаете пишите.
 
 
 
#4
17.02.2011 12:52:08
И ещё вопрос сразу можно ли будет после такой фильтрации возвращать всё как было и накладывать другой фильтр, для подсчёта уже по другим значениям?
 
 
 
#5
17.02.2011 13:32:00
Добавьте в ваш файл Лист2 и запустите этот макрос  
 
Sub Автофильтр()  
Dim iRng As Range  
   With Worksheets("Лист1")  
       'если нет автофильтра, то ставим его  
       If .AutoFilterMode = False Then .Range("A2:N" & .UsedRange.Rows.Count).AutoFilter  
       'определяем ячейку со Счётом  
       Set iRng = .Rows("1:2").Find(What:="Счёт", LookIn:=xlFormulas, LookAt:=xlWhole)  
       'устанавливаем фильтр на столбец Счёт  
       If .AutoFilterMode = True Then .Range("A2").AutoFilter Field:=iRng.Column, Criteria1:="25/1"  
       'ищем столбец К-во  
       Set iRng = .Rows("1:2").Find(What:="К-во", LookIn:=xlFormulas, LookAt:=xlWhole)  
       'переносим сумму по столбцу К-во на Лист2 в ячейку А1  
       Worksheets("Лист2").Range("A1") = Application.Sum(Columns(iRng.Column).SpecialCells(xlVisible))  
       'отображаем всю информацию  
       .ShowAllData  
   End With  
End Sub
 
 
 
#6
17.02.2011 15:20:12
А можно поподробнее эту строчку, а то на ней ошибку выкидывает?  
 
.Range("A21:AY" & .UsedRange.Rows.Count).AutoFilter расширил диапазон "A2:N", применительно к исходному документу.  
 
Run-time error '1004':  
Метод AutoFilter из класса Range завершён неверно.  
 
И ещё что значит: .Rows("1:2") цифры 1:2?
 
 
 
#7
17.02.2011 15:43:36
См. приложенный файл.  
 
Rows("1:2") - диапазон строк (первая и вторая строка). Т.е. ищем текст в первой и во второй строке
 
 
 
#8
17.02.2011 16:14:42
Спасибо, щас буду до конца разбираться, но вроде всё окей!! Если что буду ещё спрашивать!!
 
 
 
#9
17.02.2011 17:20:10
А чем отличаются:  
.Range("A1:N" & .Cells(Rows.Count, "A")).AutoFilter  
и  
.Range("A2:N" & .UsedRange.Rows.Count).AutoFilter ??
 
 
 
#10
17.02.2011 17:26:05
И ещё достаточно ли просто вконце поставить AutoFilterMode = False чтобы выключить автофильтр!!
 
 
 
#11
17.02.2011 17:39:52
1) строка .Cells(Rows.Count, "A") идет последнюю заполненную строку в столбце А (поиск идёт снизу вверх) и возвращает номер этой строки (5,10,50,100 и т.д.)  
 
2) .UsedRange.Rows.Count - определяет рабочий диапазон на листе. Таблица у вас может заканчиваться, например, на 100-й строке, но когда-то таблица была больше, например, 150 строк, Excel это помнит, и вернёт количество рабочих (когда-либо используемых вами) строк. В вашем примере данная строка не всегда корректно будет определять границы таблицы, поэтому я её заменил  
 
3) .AutoFilterMode = False  - думаю будет достаточно  (если работает, значит достаточно ))
 
 
 
#12
17.02.2011 17:40:39
опечатался выше. Вместо слова "идет" читать "ищет"
 
 
 
#13
17.02.2011 18:24:38
Dim iRng As Range, iInvoiceNum As String  
     
   Application.ScreenUpdating = False  
     
   With Worksheets("Лист1")  
       'если нет автофильтра, то ставим его  
       If .AutoFilterMode = False Then .Range("B20:AY" & .Cells(Rows.Count, "B")).AutoFilter  
n = .Cells(Rows.Count, "B")  
       MsgBox n ПОЛУЧАЮ НА ВЫХОДЕ 0 видимо не отрабатывает  
       'определяем ячейку со Счётом  
       Set iRng = .Rows("19:20").Find(What:="Счёт", LookIn:=xlFormulas, LookAt:=xlWhole)  
       'запрашиваем номер Счёта  
       iInvoiceNum = Application.InputBox("Укажите номер счета по которому нужно отфильтровать", "Номер счета", , , , , , 2)  
       If iInvoiceNum = "" Then Exit Sub  
       'устанавливаем фильтр на столбец Счёт  
       If .AutoFilterMode = True Then .Range("A2").AutoFilter Field:=iRng.Column, Criteria1:=iInvoiceNum  
       'ищем столбец К-во  
       Set iRng = .Rows("19:20").Find(What:="К-во", LookIn:=xlFormulas, LookAt:=xlWhole)  
       'переносим сумму по столбцу К-во на Лист2 в ячейку А1  
       Worksheets("Лист2").Range("A1") = Application.Sum(.Columns(iRng.Column).SpecialCells(xlVisible))  
       'отображаем всю информацию  
       .ShowAllData  
   End With  
   MsgBox "Смотрите Лист2. Кол-во: " & Worksheets("Лист2").Range("A1")  
 
AutoFilterMode = False не знаю куда прилепить ибо не отрабатывает пока!!  
записей кстати много порядка 600!!
 
 
 
#14
17.02.2011 19:01:11
Грубо говоря не отрабатывает .Range("A2").AutoFilter ибо всё остальное вроде как имеет значения!!
 
 
 
#15
17.02.2011 21:44:41
4ygynOK, вы почему-то переделали макрос и теперь  
1) устанавливаете Автофильтр на 20-ю строку (а не на 2-ю)  
2) Ищите слово "Счёт" и "К-во" в 19 и 20 строках  
 
Так задумано? В вашем примере нет слов Счёт и К-во в 19 и 20-й строках  
 
В моём выложенном примере у меня всё работает.
 
 
 
#16
17.02.2011 21:56:50
Sub Автофильтр()  
Dim iRng As Range, iInvoiceNum As String  
     
   Application.ScreenUpdating = False  
   With Worksheets("Лист1")  
       'если нет автофильтра, то ставим его  
       If .AutoFilterMode = False Then .Range("A2:N" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter  
       'определяем ячейку со Счётом  
       Set iRng = .Rows("1:2").Find(What:="Счёт", LookIn:=xlFormulas, LookAt:=xlWhole)  
       'запрашиваем номер Счёта  
       iInvoiceNum = Application.InputBox("Укажите номер счета по которому нужно отфильтровать", "Номер счета", , , , , , 2)  
       If iInvoiceNum = "" Then Exit Sub  
       'устанавливаем фильтр на столбец Счёт  
       If .AutoFilterMode = True Then .Range("A2").AutoFilter Field:=iRng.Column, Criteria1:=iInvoiceNum  
       'ищем столбец К-во  
       Set iRng = .Rows("1:2").Find(What:="К-во", LookIn:=xlFormulas, LookAt:=xlWhole)  
       'переносим сумму по столбцу К-во на Лист2 в ячейку А1  
       Worksheets("Лист2").Range("A1") = Application.Sum(.Columns(iRng.Column).SpecialCells(xlVisible))  
       'отображаем всю информацию  
       .ShowAllData  
   End With  
   MsgBox "Смотрите Лист2. Кол-во: " & Worksheets("Лист2").Range("A1")  
End Sub
 
 
 
#17
18.02.2011 02:22:10
Да 19 и 20 строка действительно так нужно, просто я применяю уже ко всему документу, а он значительно больше чем пример, его я специально урезал(данные в документе с B20 по AY590 где-то так). А вообще ваш пример очень хорош вот только iRng почему-то Nothing(получаеться что не находит!! И поле счёт в исходном документе уже в S так что буду смотреть и стараться разбираться!!
 
 
 
#18
18.02.2011 02:33:26
Ну и тогда сразу ещё пару вопросов: после изменения .Range("A2:N" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter получаю на выходе один результат 1439 для любого вводимого параметра.
 
 
 
#19
18.02.2011 02:50:07
Также ещё заметил, что в .Cells(Rows.Count, "A") при изменении "A" на чтонить другое выдаваемое значение 0. Хотя в Application.Sum(.Columns(iRng.Column).SpecialCells(xlVisible)) значения iRng.Column=4 и xlVisible=12 так и остаються, правдо не знаю как внутри работает эта функция!!
 
 
 
#20
18.02.2011 11:19:14
К сожалению, ничем не могу помочь, т.к. нет НОРМАЛЬНОГО примера.  
 
Если ваши данные начинаются с 19-й строки, то зачем вы выкладывали пример с 1-й строки? Надеялись, что сами код сможете подправить? Правьте. Не можете сами править - выкладывайте пример приближённый к вашему рабочему файлу.
 
 
 
#21
18.02.2011 11:45:11
А начинаются с 19 так как перед таблицей есть ещё шапка, а так ничего не меняется особо ну ещё столбцы добавляються, но они мне для данной задачи не нужны поэтому и обрезал таблицу!!
 
 
 
#22
18.02.2011 11:56:31
вот и поговорили )
 
 
 
#23
19.02.2011 23:44:58
Ну вот так приблизительно!!
 
 
 
#24
20.02.2011 00:22:08
Невнимательно правите.  
Сравните  
Then .Range("A2:N" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter  
и  
Then .Range("B20:AY600" & .Cells(Rows.Count, "B")).AutoFilter
 
 
 
#25
20.02.2011 01:00:00
Не принципиально всёравно не работает покачто!!
 
 
 
#26
20.02.2011 01:41:04
4ygynok, потестируйте этот файл
 
 
 
#27
20.02.2011 02:26:04
Спасибо тестируемый файл отрабатывает как нужно, применю его на основной отпишусь как отработает!!
 
 
 
#28
20.02.2011 03:07:53
Tester, перенёс ваш код всё запускается без ошибок, теперь осталось узнать почему некоторые числа не совпадают Так что буду разбираться!!
 
 
 
#29
20.02.2011 03:40:19
Нет извини Tester, значения неправильные не из-за кода, так что твоё вроде как работает всё!! Спасибо огромное!!
 
 
 
#30
20.02.2011 03:56:12
4ygynOK  
 
Замените строку:  
 
If iInvoiceNum = "" Then Exit Sub    
 
на    
 
If iInvoiceNum = False Then Exit Sub  
 
Так будет правильнее отрабатываться кнопка "Отмена" при запросе номера Счёта
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх