Создание макроса

После того как на листе 2 выбраны бренд и ОС нужен макрос, который бы показывал данные в соответствии с критериями отбора. Предварительно на лист 2 в диапазон A9:Q9 скопируем «шапку» из листа 1. Таким образом, макрос должен показывать результат в диапазоне A10:Q10 и ниже. Вызов VBA редактора Alt+F11. Нужно создать новый модуль и писать макрос в этом модуле.

ВАЖНО. Нажимая F8, можно отслеживать работу макроса построчно.

Разберем текст макроса.

Option Explicit - все переменные необходимо явно объявлять с помощью инструкций Dim или ReDim.

Dim mass(), i As Long, n As Long, j As Long - объявление переменных.

Объявляется массив mass, количество элементов в нем изначально неизвестно.

i, j, n –три целых числа.

n= Sheets("Лист2").Range("A10").CurrentRegion.Rows.Count – 1

n – это количество строк предыдущего отбора, которые нужно удалить из листа 2 перед отображением результатов нового отбора.

Sheets("Лист2") – обращение к листу 2.

Range("A10") – обращение к ячейки A10

CurrentRegion – это использумый диапазон вокруг ячейки. Таким образом

Sheets("Лист2").Range("A10").CurrentRegion – используемый диапазон вокруг ячейки A10 на втором листе. Если нет результатов отбора, то этот диапазон равен A9:Q9. А если, например, отображены результаты по бренду –Samsung, ОС – android 4.0, то это диапазон A9:Q11.

Rows.Count – количество строк.

Для A9:Q11 Sheets("Лист2").Range("A10").CurrentRegion.Rows.Count равно 3. Соответственно, перед новым отбором нужно удалить 3-1=2 строки.

Sheets("Лист2").Range("A10").Resize(n, 1).EntireRow.Delete – перед выводом результата нового отбора, удаляет результаты старого отбора.

Resize(n, 1) – расширение диапазона на n строк и 1 столбец. Если отображены результаты по отбору: Samsung, android 4.0, (при этом n=2) то

Sheets("Лист2").Range("A10").Resize(n, 1) – результатом будет диапазон A10:A11

EntireRow- получить всю строку

Sheets("Лист2").Range("A10").Resize(n, 1).EntireRow – результат -диапазон A10:XFD11

Delete – удаляет весь диапазон

Sheets("Лист2").Range("A10").Resize(n, 1).EntireRow.Delete - диапазон A10:XFD11 удаляется.

mass() = Sheets("Лист1").Range("A1").CurrentRegion.Value

Sheets("Лист1").Range("A1").CurrentRegion - используемый диапазон вокруг ячейки A1 на первом листе. Это диапазон A1:Q25

Value – значения ячеек диапазона

Sheets("Лист1").Range("A1").CurrentRegion.Value - значения ячеек диапазона A1:Q25

mass() = Sheets("Лист1").Range("A1").CurrentRegion.Value – значения всех ячеек диапазона A1:Q25 помещаются в двумерный массив mass

По большому счету из диапазона A1:Q25 листа 1 нам нужны значения не всех ячеек, а только второго и пятого столбцов, но так удобнее писать код.

В результате во втором столбце массива оказались названия брендов, в пятом – названия ОС.

For i = 1 To UBound(mass, 1)

Next i

Организован цикл от 1 до UBound(mass, 1) – верхней границы массива mass по строкам.

UBound(mass, 1) – верхняя граница массива по строкам, равна 25. Число 1 – означает, что берется первое измерение массива, т.е. строки. UBound(mass, 2) – это верхняя граница по столбцам, равна 17, в данном случае она не нужна.

i - счетчик цикла.

If <условие>

End If

Условный оператор. Если <условие> выполняется, то выполняются все строки внутри условного оператора.

If mass(i, 2) = Sheets("Лист2").Range("C3").Value And (mass(i, 5) = Sheets("Лист2").Range("F3").Value Or IsEmpty(Sheets("Лист2").Range("F3"))) Then

Если i-e значение во втором столбце массива равно значению ячейки C3 на листе 2 (первый выпадающий список – бренд) и одновременно

i-e значение во пятом столбце массива равно значению ячейки F3 на листе 2 (второй выпадающий список – ОС) или

ячейкаF3 на листе 2 пуста,

то условие выполняется и i-ю строку листа 1 нужно скопировать на лист 2.

Копирование из листа 1 выполняется так

Sheets("Лист1").Rows(i).EntireRow.Copy – тут по аналогии, должно быть понятно.

Вставка на лист 2

Sheets("Лист2").Paste Destination:=Worksheets("Лист2").Range("A10").Offset(j, 0)

Range("A10").Offset(j, 0) - смещение от ячейки A10 на j строк и 0 столбцов.

Переменная j используется для определения номера строки на втором листе. После вставки строки ее нужно увеличивать

j = j + 1

В начале работы макроса j=0, поэтому первая подходящая по условия строка вставляется на место 10-й строки, следующая подходящая на место 11-й и т.д.

Application.ScreenUpdating = False

Application.ScreenUpdating = True

Выключение обновления экрана в начале работы макроса, и включение обновления в конце работы макроса.

Помимо этого, если в редакторе VBA выбрать не модуль module1, а модуль листа 2, то там можно увидеть код

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("C3").Address Then Range("F3").ClearContents

End Sub

Это нужно, чтобы при выборе нового значения в ячейке C3, ячейка F3 автоматически очищалась.

Как добавить кнопку на лист. На ленте вкладка Разработчик >> Вставить>> нужно выбрать верхний левый элемент.

Список литературы:

1. Бакаревич Ю.Б., Пушкина Н.В. Самоучитель Microsoft Access 2002. – СПб.: БХВ-Петербург, 2002

2. Бондарева Г.А., Сахарова Е.В., Королькова Л.Н., Информатика. Методические указания. Ставрополь, СТИС, 2006

3. Бойко В.В., Савинков В.М. Проектирование баз данных информационных систем. – М.: Финансы и статистика, 1989.

4. Гончаров А. Ю. Access 2003. Самоучитель с примерами., Москва, 2004г.

5. Григорьев В.А., Ревунков В.И. Банки данных. Учебник для вузов. – М., МВТУ им.Баумана, 2002.

6. Домашняя страница Access: справка и обучение http://office.microsoft.com/ru-ru/access/FX100487571049.aspx

7. Мейер М. Теория реляционных баз данных. – М.: Мир, 1987.

8. Основы проектирования реляционных баз данных. Электронное учебное пособие.

9. Симонович С. В., Евсеев Г. А., Алексеев А. Г. Специальная информатика: Учебное пособие. – М.: АСТ-ПРЕСС: Инфорком-Пресс, 2000

10. Хаббард Дж. Автоматизированное проектирование баз данных. М.: Мир, 1984.

Поиск по сайту: