Страницы: 1 2 След.
RSS
Наложение фильтра средствами VBA.
 
Не буду просить сделать за меня задание прошу только помочь с подбором материала по этому вопросу!! Задача состоит в следующем: есть большая таблица, из неё составляем отчёты, в отчётах присутствуют ссылки на листы с уже отфильтрованными данными и например применёнными к ним функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ!! В итоге надо избавиться от этих промежуточных листов и программно всё сделать, чтобы в VBA мы наложили фильтр, затем применили к нужному столбцу функцию и занесли результат в лист с отчётом.
 
и пока единственный.
 
Добавлю всё-таки кусочек файла, повторюсь: как в нём средствами VBA сделать фильтр по столбцу Счёт, а затем например на столбец Кол-во наложить функцию Промежуточные.Итоги() и занести это всё в отдельный лист?? Если что надумаете пишите.
 
И ещё вопрос сразу можно ли будет после такой фильтрации возвращать всё как было и накладывать другой фильтр, для подсчёта уже по другим значениям?
 
Добавьте в ваш файл Лист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
 
А можно поподробнее эту строчку, а то на ней ошибку выкидывает?  
 
.Range("A21:AY" & .UsedRange.Rows.Count).AutoFilter расширил диапазон "A2:N", применительно к исходному документу.  
 
Run-time error '1004':  
Метод AutoFilter из класса Range завершён неверно.  
 
И ещё что значит: .Rows("1:2") цифры 1:2?
 
См. приложенный файл.  
 
Rows("1:2") - диапазон строк (первая и вторая строка). Т.е. ищем текст в первой и во второй строке
 
Спасибо, щас буду до конца разбираться, но вроде всё окей!! Если что буду ещё спрашивать!!
 
А чем отличаются:  
.Range("A1:N" & .Cells(Rows.Count, "A")).AutoFilter  
и  
.Range("A2:N" & .UsedRange.Rows.Count).AutoFilter ??
 
И ещё достаточно ли просто вконце поставить AutoFilterMode = False чтобы выключить автофильтр!!
 
1) строка .Cells(Rows.Count, "A") идет последнюю заполненную строку в столбце А (поиск идёт снизу вверх) и возвращает номер этой строки (5,10,50,100 и т.д.)  
 
2) .UsedRange.Rows.Count - определяет рабочий диапазон на листе. Таблица у вас может заканчиваться, например, на 100-й строке, но когда-то таблица была больше, например, 150 строк, Excel это помнит, и вернёт количество рабочих (когда-либо используемых вами) строк. В вашем примере данная строка не всегда корректно будет определять границы таблицы, поэтому я её заменил  
 
3) .AutoFilterMode = False  - думаю будет достаточно  (если работает, значит достаточно ))
 
опечатался выше. Вместо слова "идет" читать "ищет"
 
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!!
 
Грубо говоря не отрабатывает .Range("A2").AutoFilter ибо всё остальное вроде как имеет значения!!
 
4ygynOK, вы почему-то переделали макрос и теперь  
1) устанавливаете Автофильтр на 20-ю строку (а не на 2-ю)  
2) Ищите слово "Счёт" и "К-во" в 19 и 20 строках  
 
Так задумано? В вашем примере нет слов Счёт и К-во в 19 и 20-й строках  
 
В моём выложенном примере у меня всё работает.
 
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
 
Да 19 и 20 строка действительно так нужно, просто я применяю уже ко всему документу, а он значительно больше чем пример, его я специально урезал(данные в документе с B20 по AY590 где-то так). А вообще ваш пример очень хорош вот только iRng почему-то Nothing(получаеться что не находит!! И поле счёт в исходном документе уже в S так что буду смотреть и стараться разбираться!!
 
Ну и тогда сразу ещё пару вопросов: после изменения .Range("A2:N" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter получаю на выходе один результат 1439 для любого вводимого параметра.
 
Также ещё заметил, что в .Cells(Rows.Count, "A") при изменении "A" на чтонить другое выдаваемое значение 0. Хотя в Application.Sum(.Columns(iRng.Column).SpecialCells(xlVisible)) значения iRng.Column=4 и xlVisible=12 так и остаються, правдо не знаю как внутри работает эта функция!!
 
К сожалению, ничем не могу помочь, т.к. нет НОРМАЛЬНОГО примера.  
 
Если ваши данные начинаются с 19-й строки, то зачем вы выкладывали пример с 1-й строки? Надеялись, что сами код сможете подправить? Правьте. Не можете сами править - выкладывайте пример приближённый к вашему рабочему файлу.
 
А начинаются с 19 так как перед таблицей есть ещё шапка, а так ничего не меняется особо ну ещё столбцы добавляються, но они мне для данной задачи не нужны поэтому и обрезал таблицу!!
 
вот и поговорили )
 
Ну вот так приблизительно!!
 
Невнимательно правите.  
Сравните  
Then .Range("A2:N" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter  
и  
Then .Range("B20:AY600" & .Cells(Rows.Count, "B")).AutoFilter
 
Не принципиально всёравно не работает покачто!!
 
4ygynok, потестируйте этот файл
 
Спасибо тестируемый файл отрабатывает как нужно, применю его на основной отпишусь как отработает!!
 
Tester, перенёс ваш код всё запускается без ошибок, теперь осталось узнать почему некоторые числа не совпадают Так что буду разбираться!!
 
Нет извини Tester, значения неправильные не из-за кода, так что твоё вроде как работает всё!! Спасибо огромное!!
 
4ygynOK  
 
Замените строку:  
 
If iInvoiceNum = "" Then Exit Sub    
 
на    
 
If iInvoiceNum = False Then Exit Sub  
 
Так будет правильнее отрабатываться кнопка "Отмена" при запросе номера Счёта
Страницы: 1 2 След.
Читают тему
Наверх