Remkomplekty.ru

IT Новости из мира ПК
9 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba перебор ячеек в диапазоне

VBA Excel. Переменная диапазона ячеек (As Range)

Присвоение диапазона ячеек объектной переменной в VBA Excel. Адресация ячеек в переменной диапазона и работа с ними. Определение размера диапазона. Примеры.

Присвоение диапазона ячеек переменной

Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:

Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.

Присваивается переменной диапазон ячеек с помощью оператора Set:

В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.

Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.

Адресация ячеек в диапазоне

К ячейкам присвоенного диапазона можно обращаться по их индексам, а также по индексам строк и столбцов, на пересечении которых они находятся.

Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:

Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:

Обращаться в переменной диапазона можно не только к отдельным ячейкам, но и к части диапазона (поддиапазону), присвоенного переменной, например,

обращение к первой строке присвоенного диапазона размерностью 5х5:

и обращение к первому столбцу присвоенного диапазона размерностью 5х5:

Работа с диапазоном в переменной

Работать с диапазоном в переменной можно точно также, как и с диапазоном на рабочем листе. Все свойства и методы объекта Range действительны и для диапазона, присвоенного переменной. При обращении к ячейке без указания свойства по умолчанию возвращается ее значение. Строки

равнозначны. В обоих случаях информационное сообщение MsgBox выведет значение ячейки с индексом 6.

Важно: если вы планируете работать только со значениями, используйте переменные массивов, код в них работает значительно быстрее.

Преимущество работы с диапазоном ячеек в объектной переменной заключается в том, что все изменения, внесенные в переменной, применяются к диапазону (который присвоен переменной) на рабочем листе.

Пример 1 – работа со значениями

Скопируйте процедуру в программный модуль и запустите ее выполнение.

Обратите внимание, что ячейки диапазона на рабочем листе заполнились так же, как и ячейки в переменной диапазона, что доказывает их непосредственную связь между собой.

Пример 2 – работа с форматами

Продолжаем работу с тем же диапазоном рабочего листа «C6:E8»:

Опять же, обратите внимание, что все изменения форматов в присвоенном диапазоне отобразились на рабочем листе, несмотря на то, что мы непосредственно с ячейками рабочего листа не работали.

Пример 3 – копирование и вставка диапазона из переменной

Значения ячеек диапазона, присвоенного переменной, передаются в другой диапазон рабочего листа с помощью оператора присваивания.

Скопировать и вставить диапазон полностью со значениями и форматами можно при помощи метода Copy, указав место вставки (ячейку) на рабочем листе.

В примере используется тот же диапазон, что и в первых двух, так как он уже заполнен значениями и форматами.

Сравнение таблиц в Excel с помощью макросов VBA

Статья даёт ответы на следующие вопросы:

  • Как сравнить две таблицы в Excel с помощью макросов VBA?
  • Как обращаться к ячейкам таблицы Excel с помощью VBA?
  • Как осуществлять перебор ячеек таблицы в цикле с помощью VBA?

В предыдущей статье Сравнение таблиц в Excel мы рассмотрели подход к сравнению сложных таблиц с использованием формул и без программирования.

В данной статье рассмотрим способ сравнения таблиц Excel с помощью VBA макросов на примере тех же исходных данных.

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

Для начала напишем алгоритм наших действий по сравнению таблиц.

  1. Определим диапазоны данных первой и второй таблицы, то есть найдем последние значимые строки и сохраним их номера в переменных (последняя строка таблицы 1 — last_i и последняя строка таблицы 2 — last_j).
  2. Начнем проходить по каждой строке таблицы 2 (внешний цикл), данные из которой нужно перенести в таблицу 1. С первой строки данных (в примере это строка 3) до последней строки таблицы 2.
  3. Для каждой строки таблицы 2 определим идентификатор строки, путем формирования строки, содержащей полный адрес квартиры (значения из нескольких колонок, разделенные дефисами).
  4. Начнем проходить по каждой строке таблицы 1 (внутренний цикл) с первой строки данных (в примере это строка 3) до последней строки таблицы 1, определяя при этом идентификатор строки.
  5. Сравним значения идентификаторов строк таблицы 1 и таблицы 2.
  6. Если идентификаторы равны, перепишем ФИО покупателя из ячейки таблицы 2 в соответствующую ячейку таблицы 1; прервем внутренний цикл по таблице 1 и перейдем к следующей строке таблицы 2 (переход к п.2).

Теперь остается реализовать алгоритм в виде программного кода макроса.

Для этого откроем вкладку Вид ленты функций Excel. Щелкнем на нижнюю часть со стрелкой кнопки Макросы. В открывшемся подменю выберем Запись макроса. В результате начнется запись нового макроса. Поскольку код мы будем формировать вручную, то еще раз зайдем в подменю макросов и выберем Остановить запись. Далее еще раз войдем в подменю макросов и выберем Макросы.

В появившемся диалоге выделим наш макрос и нажмем Изменить.

На экране откроется окно редактора макросов Visual Basic for Applications. В области кода (правая верхняя область) отображается код только что созданного пустого макроса.

Читать еще:  Как создать olap куб excel

В процедуру Макрос1 (между объявлениями начала и конца процедуры: Sub и End Sub) необходимо вставить код, решающий поставленную задачу. Образец кода представлен ниже.

Результат решения задачи:

Работа со списками в Visual Basic

Приведённые практические работы по программированию могут быть использованы при изучении элементов выбора Visual Basic. Списки целесообразно изучать после знакомства с переключателями (OptionButton) и флажками (CheckBox).

При объяснении нового материала удобно использовать распечатки с изложением теоретического материала и примерами программ, которые выдаются каждому студенту (ученику), либо методические пособия (мы используем авторское пособие “Практикум по решению задач в среде Visual Basic”, часть 1, 2).

Приведённые практические работы рассчитаны на 3 занятия по 2 часа. Задания самостоятельной работы можно использовать в качестве домашних заданий.

В Visual Basic существует 2 вида списков:

  • комбинированный список ComboBox;
  • список ListBox.

1. Комбинированный список ComboBox

Комбинированный список представляет комбинацию раскрывающегося списка и текстового поля. Пользователь имеет возможность выбрать определенное значение какого-либо параметра в раскрывающемся списке или ввести необходимое значение в текстовое поле. Если элементы не помещаются в границах стандартного окна, то автоматически появляются полосы прокрутки, с помощью которых можно выбирать требуемый элемент в списке.

Свойство Style задаёт внешний вид комбинированного списка (по умолчанию оно равно 0 — элемент ComboBox отображается в виде текстового поля со стрелкой справа, позволяющей развернуть весь список и выбрать требуемое значение). Если Style = 1, то постоянно отображается весь список (или список с полосой прокрутки).

Основные свойства ComboBox

0 — Dropdown Combo (раскрывающийся комбинированный список);

1 — Simple Combo (простой комбинированный список);

2 — Dropdown List (раскрывающийся список).

Для добавления нового элемента в список используется метод AddItem.

Пример: Combo1.AddItem “Пение” — добавить в список Combo1 слово Пение.

Добавляемые в список элементы имеют тип String; [index] — порядковый номер в списке, под которым будет находиться новый элемент (необязательный параметр).

Добавить элементы в комбинированный список можно также на этапе разработки с помощью свойства List. Для добавления очередного элемента в список нужно ввести этот элемент в свойстве List и нажать комбинацию клавиш Ctrl + Enter.

2. Список ListBox

Данный элемент применяется в том случае, когда пользователю необходимо выбрать один элемент из имеющегося списка для выполнения определенных действий. Объекты ListBox используются обычно в сочетании с управляющими кнопками, которые позволяют обрабатывать элементы списка (добавлять, удалять и т. д.).

В отличие от ComboBox список ListBox можно создать многоколончатым и осуществлять выбор нескольких элементов одновременно.

Свойство MultiSelect позволяет задать режим множественного выбора элементов в списке. В режиме MultiSelect = 1 добавление элемента в выделенную группу или исключение из нее осуществляется с помощью щелчка мыши или нажатия пробела. Если MultiSelect = 2, то в этом случае выделение элементов выполняется по аналогии с программой Проводник:

  • щелчок мыши при нажатой клавише Shift позволяет выделить несколько подряд расположенных элементов;
  • щелчок мыши при нажатой клавише Ctrl даёт возможность выделять группу несмежных элементов списка.

Основные свойства элемента ListBox

0 — одноколончатый список с вертикальной прокруткой;

1 — одноколончатый список с горизонтальной прокруткой;

>1 — многоколончатый список с горизонтальной прокруткой

0 — None (обычный список);

1 -Simple (простой множественный выбор — щелчок мыши или нажатие Пробел выделяет очередной элемент или снимает выделение);

2 — Extended (расширенный множественный выбор) — выделение с использованием вспомогательных клавиш.

Методы ListBox аналогичны методам ComboBox.

Практическая работа 1. Проект “Результаты сессии”

Задание: создать проект, содержащий 3 списка: список студентов группы; список студентов, получивших зачёт и список неаттестованных. Двойным щелчком мыши в списке студентов (List1) выбирается фамилия и добавляется в список “Зачёт” (List2). Двойным щелчком в списке “Зачёт” фамилия возвращается назад. Кнопкой “Добавить в список” фамилия, введённая в текстовое поле Text1, заносится в список List1. Кнопка “н/а” заносит выбранную фамилию из списка студентов в список List3 ( н/а).

Private Sub Form_Load() ‘загрузка формы

List1.AddItem «Чесноков»: List1.AddItem «Симонов»

List1.AddItem «Трушков»: List1.AddItem «Морозова»

Private Sub List1_DblClick() ‘перенести из списка в зачёт

List1.RemoveItem List1.ListIndex ‘удалить из списка

Private Sub List2_DblClick() ‘перенести назад в Список из Зачёт

Private Sub List3_DblClick() ‘перенести назад в список из н/а

Private Sub Command1_Click() ‘добавить в список из текст. поля

Private Sub Command3_Click() ‘добавить в н/а

List1.RemoveItem List1.ListIndex ‘удалить из списка

Задания

  1. Добавьте в проект список оценок (2, 3, 4, 5) – List4.
  2. Измените проект таким образом, чтобы можно было добавлять в результаты фамилию и оценку ученика.
  3. Создайте кнопку “Очистить результаты”.

Примерный вид формы показан на рисунке.

Практическая работа 2. Проект “Переводчик”

Задание: создать проект для проверки знания иностранных слов (англо-русский и русско-английский переводчик).

Элементы интерфейса проекта:

  1. Комбинированные списки Combo1 и Combo2 – для хранения русских и английских слов. Индексы русских слов в списке должны соответствовать индексам соответствующих им английских слов.
  2. Текстовое поле Text1 — для вывода случайного слова из списка, которое необходимо перевести.
  3. Текстовое поле Text2 — для вывода комментария “Верно” или “Неверно”.
  4. Текстовые поля Text3, Text4 — для вывода количества вопросов и верных ответов.
  5. Таймер Timer1 – для временной задержки при выводе очередного вопроса.
  6. Image1 – для вывода рисунка, соответствующего слову.
  7. Элемент MMControl1 — для воспроизведения звука.

Элемент MMControl является дополнительным компонентом VB (Activ X). Для его установки необходимо:

  • Открыть в меню Проект – Компоненты вкладку Управление (Control);
  • Установить флажок MicrosoftMultimedia Control 6.0;
  • На панели элементов управления появится новый значок MMControl.

Рисунки к проекту сохраните в папке с проектом в порядке следования слов в списке Combo1:

Ris0.jpg – рисунок к первому слову списка;

Ris1.jpg – рисунок ко второму слову списка и т.д.

Для определения имени файла рисунка в проекте используем переменную Path:

Path = «Ris» & Mid(Str(n), 2, 1) & «.jpg»

Функция Mid используется для того, чтобы убрать пробел перед числом в имени файла, т.к. функция Str(n) для положительного числа формирует строку с пробелом перед числом. Таким образом, получаем неверное имя файла Ris 0.jpg, вместо Ris0.jpg.

Рассмотрим основные процедуры проекта.

1) В процедуре загрузки формы нужно заполнить списки русских и английских слов.

Dim n, k, z, p As Integer, Path As String

‘n – индекс слова в списке; k – количество верных ответов; z – кол. вопросов; p – признак направления перевода; Path – путь к файлу с рисунком на диске

Private Sub Form_Load()

Combo1.AddItem «собака»: Combo1.AddItem «дельфин»

Combo1.AddItem «осёл»: Combo1.AddItem «красный»

Combo1.AddItem «зеленый»: Combo2.AddItem «Dog»

Combo2.AddItem «Dolphin»: Combo2.AddItem «Donkey»

Combo2.AddItem «Red»: Combo2.AddItem «Green»

k = 0 ’количество верных ответов

z = 0 ‘количество вопросов

2) При выборе кнопки “Перевод рус/англ” должно появляться русское слово. Для данного слова нужно найти перевод в списке английских слов. Список русских слов при этом должен скрываться.

Private Sub Command5_Click() ‘Перевод рус/англ

p = 1 ‘признак перевода – рус/англ

Combo1.Visible = False ‘скрыть список русских слов

Combo2.Visible = True ‘показать список англ. слов

Label1.Caption = «Русское слово»

Timer1.Enabled = True ‘включить таймер

3) В процедуре таймера случайным образом определяем индекс слова в списке слов. Если p=1, то было выбрано направление перевода рус/англ., поэтому слово выбирается из списка русских слов Combo1.

Private Sub Timer1_Timer()

z = z + 1 ‘подсчёт кол. вопросов

n = Int(Rnd * 4) ‘случайный выбор индекса слова в списке слов

If p = 1 Then Text1.Text = Combo1.List(n)

4) Для выбора перевода заданного русского слова пользователь делает щелчок по списку английских слов. Если индекс исходного русского слова n совпадает с индексом слова-перевода, выбранного пользователем, то ответ верный.

Private Sub Combo2_Click() ‘список англ. слов

If n = Combo2.ListIndex Then ‘если ответ верный

Image1.Visible = True ‘показать рисунок

MMControl1.FileName = «da.wav» ‘загрузить звуковой файл

Path = «Ris» & Mid(Str(n), 2, 1) & «.jpg»

Image1.Picture = LoadPicture(Path) ‘загрузить файл с соответствующим рисунком

Else ‘если ответ неверный

MMControl1.Command = «sound» ‘воспроизвести звук. файл

Задания:

  1. Создайте процедуру кнопки “Перевод англ/рус”.
  2. Добавьте кнопку Сброс.
  3. Подберите 2 звуковых файла для подтверждения верного и неверного ответа, подключите эти файлы к проекту.
  4. Создайте заставку к проекту.

Самостоятельная работа

  1. Создать проект “Выбор шрифта”, позволяющий выбрать с помощью списков тип шрифта, размер, начертание (свойства): жирный, подчёркнутый, курсив. Выбранные параметры должны применяться к метке Образец.

Свойства шрифта в метке задаются следующими командами:

Label1.Font.Italic = True – установить курсив

Label1.Font.Bold = False – отменить жирный шрифт

Label1.Font.Underline = True – установить подчёркнутый шрифт

Label1.Font = Combo1.Text – применить к метке тип шрифта, выбранный в комбинированном списке 1

Label1.Font.Size = Combo1.Text — применить к метке размер шрифта, выбранный в комбинированном списке 1

  • Создать проект “Заказы CD/DVD дисков по каталогам”. На форме разместить 2 комбинированных списка: “Игры” и “Обучающие программы”; список “Заказано”. Кнопки “Добавить в заказ” добавляют выбранный элемент в список “Заказано”. Кнопка “Удалить из заказа” удаляет выбранный элемент из заказа. Предусмотреть подсчёт количества заказов в списке.
  • Создать проект “Результаты кросса” с использованием 3-х списков. Двойным щелчком мыши в списке студентов (List1) выбирается фамилия и добавляется в список “Зачёт” (List2). Двойным щелчком в списке “Зачёт” фамилия возвращается назад. Кнопкой “Добавить в список” фамилия, введённая в текстовое поле Text1, заносится в список List1. Кнопка “н/а” заносит выбранную фамилию из списка в список List3 (н/а).
  • Литература

    1. Глушаков С.В. , Мельников В.В., Сурядный А.С. Программирование в среде Windows. Visual Basic 6.0. М.: ООО “Издательство АСТ”, 2001
    2. Браун С. Visual Basic. Учебный курс. – Спб.: Питер, 2002

    Объекты Range и Selection

    В иерархии Excel объект Range (диапазон) идет сразу после объекта worksheet. Объект Range является одним из ключевых объектов VBA. Объект Selection (выбор) возникает в VBA двояко — либо как результат работы метода Select, либо при вызове свойства selection. Тип получаемого объекта зависит от типа выделенного объекта. Чаще всего объект Selection принадлежит классу Range и при работе с ним можно использовать свойства и методы объекта Range. Интересной особенностью объектов Range и selection является то, что они не являются элементами никакого семейства объектов.

    Адресация ячеек

    При работе с объектом Range необходимо помнить, как в Excel ссылаются на ячейку рабочего листа. Имеются два способа ссылки на ячейки рабочего листа: относительная адресация (т. е. когда начало координат, задающее нумерацию строк и столбцов, связывается с объектом, вызвавшим Range) и абсолютная адресация.

    Имя ячейки состоит из имени столбца (их 256 — А, В, . Z, АВ, . HZ, IA, . IV) и номера (1, . 16384).

    Адресация задается индексом строки и индексом столбца. Например, R1C1, R2C3

    Признаком абсолютной адресации является знак «$», предшествующий имени строки (абсолютной адресации на строку) или столбца (абсолютной адресации на столбец). Например, $А10, А$10 и $А$10 задают абсолютную адресацию на столбец А, строку 10 и ячейку А10 соответственно

    Указывается смещение по отношению к активной ячейке. Смещение приводится в квадратных скобках, причем знак указывает на направление смещения. Например, если активной ячейкой является касз, то R[i]C[-1] дает ссылку на ячейку кзс2

    Адресация ячейки рабочего листа является лишь частью полного адреса ячейки, который в общем случае включает имя рабочего листа и адрес книги. При задании полного адреса за именем листа следует знак «!», а адрес книги заключается в скобки. Например,

    В первой строке данного примера дана относительная ссылка на ячейку AI активного рабочего листа, во второй — на ячейку AI рабочего листа листа активной книги, а в третьей на ячейку AI рабочего листа лист2 книги моякнига-xls текущего рабочего каталога.

    Задание групп строк и столбцов с помощью объекта Range

    Если в диапазоне указываются только имена столбцов или строк, то объект Range задает диапазон, состоящий из указанных столбцов или строк. Например, Range («А: с») задает диапазон, состоящий из столбцов А, в и с, а Range <"2: 2") - из второй строки. Другим способом работы со строками и столбцами являются методы ROWS (строки) и Columns (столбцы), возвращающие коллекции строк и столбцов. Например, столбцом А является columns (1), а второй строкой - ROWS (2).

    Связь объекта Range и свойства Cells

    Так как ячейка является частным случаем диапазона, состоящим только из единственной ячейки, объект Range также позволяет работать с ней. Объект cells (ячейки) — это альтернативный способ работы с ячейкой. Например, ячейка А2 как объект описывается Range («A2») или cells (1,2). В свою очередь объект ceils, вкладываясь в Range, также позволяет записывать диапа зон в альтернативном виде, который иногда удобен для работы, а именно,

    Range («А2:C3») И Range(Cells(1,2), Cells(3,3))

    Определяют один и тот же диапазон.

    Свойства и методы объекта Range

    Объект Range позволяет сочетать гибкость VBA и мощь рабочего листа Excel. Более 400 встроенных функций рабочего листа существенно упрощают и делают более наглядным программирование на VBA.

    Далее приводятся наиболее часто используемые свойства и методы объекта

    Перечислим основные свойства объекта Range.

    Перебор ячеек в диапазоне

    В настоящее время я использую следующий код для проверки столбца A в определенном диапазоне ячеек на значение #N/A, и если он найден, я удаляю эту строку.

    Мне нужно расширить это, поэтому я проверяю все столбцы с 1 по 10, а не просто A. я попробовал эту небольшую модификацию (вложив еще один цикл), но она не работает. Есть предложения?

    4 Ответов

    здесь два вопроса:

    on на любой заданной строке после того, как N/A найден, вам нужно прервать цикл

    Это может не сработать на других языках, кроме английского

    Измените значение A29:F49 в соответствии с вашими данными.

    Вы могли бы использовать несколько иной подход

    Я полагаю, что были проблемы с вложенными предложениями ‘with’, которые вы использовали.

    Вы можете определить правильный диапазон и использовать цикл ‘for each’, что сделает вещи более понятными и простыми для чтения. Я назвал диапазон как ‘MyRange’ для целей тестирования.

    Похожие вопросы:

    Я пытаюсь написать код VBA, чтобы найти конкретную строку в диапазоне ячеек, после того как найду строку, которую мне нужно записать в ячейку ниже. Пример У меня есть слово Test на raw 1, поэтому.

    Я использую библиотеку Apache POI для чтения/записи в XLSX. На мой оригинал документа, у меня есть элемент управления namedrange XYZ. Я хочу обновить значения ячеек в этом диапазоне имен. Как я могу.

    Мне нужно очистить имена всех именованных ячеек в диапазоне перед выполнением процедуры. Я пробовал следующий код без успеха до сих пор: Sub EraseCellNames() Worksheets(SheetA).Range(A1:G5).Select.

    Я хочу удалить пустые ячейки в диапазоне (E1:E130). Я пытался использовать, если ячейка value = тогда как можно увидеть в приведенном ниже коде : For Each cell In ranger If cell.Value = Then.

    Я хочу ограничить число пользователей моей книги 1000 символами в диапазоне ячеек (пример: A5:A30 ). Другими словами, ограничьте общее количество символов в диапазоне A5:A30 до 1000 символов.

    У меня есть лист, где я хочу изменить значение в диапазоне ячеек в другом столбце, чем мой выбор. Я использую VBA, потому что это часть кода, который отправляет информацию на определенный принтер. Я.

    Я пытаюсь найти формулу в Excel VBA, которая будет подсчитывать количество чисел в диапазоне ячеек. Я пробовал формулу, но она подсчитывает только количество чисел в одной ячейке. Может ли.

    Попытка подсчитать количество ячеек в пределах диапазона, отображаемого в строке состояния. Не знаю, как подсчитать количество ячеек в диапазоне, который будет использоваться в качестве знаменателя.

    Мне нужна формула, которая будет искать, если A4 находится в диапазоне ячеек (столбец D

    400 значений) и писать YES или нет в B4. Мне нужно это, чтобы проверить более 1000 ячеек в столбце.

    Я работаю над оптимизационной задачей для нашей рабочей книги по сверке данных, и часть работы, которую выполняет код, заключается в том, чтобы окрасить определенные ячейки определенным цветом на.

    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector
    ×
    ×