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

Вход

Регистрация

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

 

= Мир MS Excel/Условие в макросе раскрывающем все фильтры - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Условие в макросе раскрывающем все фильтры (Макросы/Sub)
Условие в макросе раскрывающем все фильтры
maverick_77 Дата: Пятница, 07.11.2014, 01:28 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Здравствуйте.
Прошу помощи знатоков =)

Суть проблемы:
есть файл с огромной таблицей. Таблица постепенно заполняется (заносится персонал со своими признаками). в некоторых ячейках вставлены формулы. Но надо чтобы все заполненные строки "фиксировались" (т.е. чтобы в ячейках с формулами с момента заполнения данными формулы больше небыло, а оставалось только значение, которое формула создала; ну чтобы на будущее значение не менялось в зависимости от изменения исходных данных).
Я пока не придумал ничего умнее, как вставить макрос, который перед закрытием копирует всю заполненную часть и вставляет только значение.

[vba]
Код
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Range("A5:AY5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
ActiveCell.Select

End Sub

Наверное, не самое изящное решение, но и оно, в принципе, устроило бы.

Но обнаружил баг: если в таблице был выставлен какой-то фильтр, копирование и вставка "только значений" происходит некорректно. Не буду описывать подробно; думаю, вы понимаете почему так происходит и без меня =)
Я начал думать, как решить проблему.
В тот же макрос вставил оператор, раскрывающий все фильтры перед копированием. Получилось так:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveSheet.ShowAllData
Range("A5:AY5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
ActiveCell.Select

End Sub
[/vba]

Но появилась другая проблема: если фильтры были, то всё отлично - фильтры снялись, всё нужное поле откопировалось как надо. Но если фильтров не было, то при закрытии выходит сообщение: "Run-time error '1004': Метод ShowAllData из класса Worksheet завершен неверно" (ну т.к. нет никаких фильтров) и предлагает либо прекратить работу макроса вообще, либо перейти к его редактированию.

Вопрос: как поставить условие - если есть хоть один выбранный выбранный фильтр, то снять все фильтры; если фильтров не выбрано, то перейти к копированию/вставке?
Ну или может быть более грамотное решение основной задачи (в примере, в столбцах E, F и I должны быть значения с момента заполнения ячейки в столбце D, а ниже в тех же столбцах должны оставаться формулы)

если нельзя, но очень хочется, то можно!
 
Ответить
СообщениеЗдравствуйте.
Прошу помощи знатоков =)

Суть проблемы:
есть файл с огромной таблицей. Таблица постепенно заполняется (заносится персонал со своими признаками). в некоторых ячейках вставлены формулы. Но надо чтобы все заполненные строки "фиксировались" (т.е. чтобы в ячейках с формулами с момента заполнения данными формулы больше небыло, а оставалось только значение, которое формула создала; ну чтобы на будущее значение не менялось в зависимости от изменения исходных данных).
Я пока не придумал ничего умнее, как вставить макрос, который перед закрытием копирует всю заполненную часть и вставляет только значение.

[vba]
Код
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Range("A5:AY5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
ActiveCell.Select

End Sub

Наверное, не самое изящное решение, но и оно, в принципе, устроило бы.

Но обнаружил баг: если в таблице был выставлен какой-то фильтр, копирование и вставка "только значений" происходит некорректно. Не буду описывать подробно; думаю, вы понимаете почему так происходит и без меня =)
Я начал думать, как решить проблему.
В тот же макрос вставил оператор, раскрывающий все фильтры перед копированием. Получилось так:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveSheet.ShowAllData
Range("A5:AY5").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5").Select
Selection.End(xlDown).Select
Application.CutCopyMode = False
ActiveCell.Select

End Sub
[/vba]

Но появилась другая проблема: если фильтры были, то всё отлично - фильтры снялись, всё нужное поле откопировалось как надо. Но если фильтров не было, то при закрытии выходит сообщение: "Run-time error '1004': Метод ShowAllData из класса Worksheet завершен неверно" (ну т.к. нет никаких фильтров) и предлагает либо прекратить работу макроса вообще, либо перейти к его редактированию.

Вопрос: как поставить условие - если есть хоть один выбранный выбранный фильтр, то снять все фильтры; если фильтров не выбрано, то перейти к копированию/вставке?
Ну или может быть более грамотное решение основной задачи (в примере, в столбцах E, F и I должны быть значения с момента заполнения ячейки в столбце D, а ниже в тех же столбцах должны оставаться формулы)
Автор - maverick_77
Дата добавления - 07.11.2014 в 01:28
ShAM Дата: Пятница, 07.11.2014, 05:12 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1347
Репутация: 249 ±
Замечаний: 0% ±

Excel 2010
Цитата maverick_77, 07.11.2014 в 01:28, в сообщении № 1
в примере, в столбцах E, F и I
Ну, так пример покажите.
А так, в код второй строкой попробуйте добавить (может, поможет):
[vba]
Код
On Error Resume Next
[/vba]
ЗЫ: и свои коды оформляйте соответствующим тэгом (кнопка #).


Сообщение отредактировал ShAM - Пятница, 07.11.2014, 05:14
 
Ответить
Сообщение
Цитата maverick_77, 07.11.2014 в 01:28, в сообщении № 1
в примере, в столбцах E, F и I
Ну, так пример покажите.
А так, в код второй строкой попробуйте добавить (может, поможет):
[vba]
Код
On Error Resume Next
[/vba]
ЗЫ: и свои коды оформляйте соответствующим тэгом (кнопка #).
Автор - ShAM
Дата добавления - 07.11.2014 в 05:12
Wasilich Дата: Пятница, 07.11.2014, 09:17 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1232
Репутация: 326 ±
Замечаний: 0% ±

2003
А так не пойдет?
[vba]
Код
Sub qqq()
     Range("A5:AY5").AutoFilter
     Range("A5:AY5").Value = Range("A5:AY5").Value
End Sub
[/vba]
 
Ответить
СообщениеА так не пойдет?
[vba]
Код
Sub qqq()
     Range("A5:AY5").AutoFilter
     Range("A5:AY5").Value = Range("A5:AY5").Value
End Sub
[/vba]
Автор - Wasilich
Дата добавления - 07.11.2014 в 09:17
maverick_77 Дата: Пятница, 07.11.2014, 16:29 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
ShAM, по поводу оформления - принято.
Что касается примера, то странно... он должен был приложиться... вот исправляю

Wasilic, не... не пойдёт. Так просто пропадают фильтры. А надо, чтобы они оставались, но всё отфильтрованное, чтобы раскрывалось.
К сообщению приложен файл: 2230797.xlsm (22.4 Kb)

если нельзя, но очень хочется, то можно!

Сообщение отредактировал maverick_77 - Пятница, 07.11.2014, 17:23
 
Ответить
СообщениеShAM, по поводу оформления - принято.
Что касается примера, то странно... он должен был приложиться... вот исправляю

Wasilic, не... не пойдёт. Так просто пропадают фильтры. А надо, чтобы они оставались, но всё отфильтрованное, чтобы раскрывалось.
Автор - maverick_77
Дата добавления - 07.11.2014 в 16:29
RAN Дата: Пятница, 07.11.2014, 17:31 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
[vba]
Код
Private Sub Workbook_BeforeClose(Cancel As Boolean)
     ActiveSheet.ShowAllData
     Range(Cells(4, "L"), Cells(Rows.Count, "A").End(xlUp)).Value = _
     Range(Cells(4, "L"), Cells(Rows.Count, "A").End(xlUp)).Value
End Sub
[/vba]

Быть или не быть, вот в чем загвоздка!
 
Ответить
Сообщение[vba]
Код
Private Sub Workbook_BeforeClose(Cancel As Boolean)
     ActiveSheet.ShowAllData
     Range(Cells(4, "L"), Cells(Rows.Count, "A").End(xlUp)).Value = _
     Range(Cells(4, "L"), Cells(Rows.Count, "A").End(xlUp)).Value
End Sub
[/vba]
Автор - RAN
Дата добавления - 07.11.2014 в 17:31
SAGE Дата: Пятница, 07.11.2014, 18:19 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 5 ±
Замечаний: 0% ±

Excel 2007
maverick_77, пробуйте вставить так
[vba]
Код
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
[/vba]

т.е. если есть примененный фильтр то он раскрывает его

Best regards,
SAGE

Сообщение отредактировал SAGE - Пятница, 07.11.2014, 18:38
 
Ответить
Сообщениеmaverick_77, пробуйте вставить так
[vba]
Код
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
[/vba]

т.е. если есть примененный фильтр то он раскрывает его
Автор - SAGE
Дата добавления - 07.11.2014 в 18:19
maverick_77 Дата: Пятница, 07.11.2014, 19:40 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 39
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
SAGE, крутотенечка! =))) Просто и эффективно (в моём случае)
Спасибо!

если нельзя, но очень хочется, то можно!
 
Ответить
СообщениеSAGE, крутотенечка! =))) Просто и эффективно (в моём случае)
Спасибо!
Автор - maverick_77
Дата добавления - 07.11.2014 в 19:40
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Условие в макросе раскрывающем все фильтры (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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