Работа процедуры с ячейками таблицы

Ребята помогите выполнить задачу!
Откройте новую рабочую книгу. Введите следующие значения в первый лист рабо¬чей книги.
Ячейки Значения
————————————————————
А1 Товар
А2:А6 Названия каких-либо товаров
B1 Цена
B2 15
B4 3
B5 7
B6 9

Создайте процедуру с именем СнижениеЦен. Процедура должна уменьшить значения всех ячеек из диапазона В2:В6 на 5. Если цена будет меньше или равна нулю, вы¬делите ее и название товара красным полужирным шрифтом. Также в этом случае (цена меньше или равна нулю) процедура должна вывести окно сообщения с соответ¬ствующим текстом предупреждения. Выполните процедуру.

0

serb,
а к какой это теме по VBA относится (что вы изучали). И почему нет в задании B3?

0

Объект Range
Объект Range (Диапазон), наиболее часто используемый в процедурах VBA объект Excel.
Цель: Изучить различные методы работы с диапозонами.
Задачи:
1. Рассмотреть роль объекта Range в VBA
2. Освоить использование оператора With
3. Освоить использование оператора For Each
4. Выполнить анализ процедур, использующих оператор Range

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

Свойства объекта Range
Изучение любого объекта — это, прежде всего изучение его свойств и методов. Среди всех свойств объекта Range выделим следующие.
 Address (Адрес) — возвращает текущее положение диапазона.
 Count (Счет) — возвращает количество ячеек в диапазоне.
 Formula (Формула) - возвращает формулу, по которой вычисляется значение, отображаемое в ячейке.
 Offset (Смещение) — возвращает величину смещения одного диапазона относительно другого.
 Resize (Изменение размеров) — позволяет изменять текущее выделение диа¬пазона.
 Value (Значение) — возвращает значения ячеек, составляющих диапазон.

Чтобы поэкспериментировать со свойствами объекта Range, создадим процедуру
Сначала закройте все открытые рабочие книги, а затем откройте новую рабочую книгу. Будем считать, что текущий рабочий лист у вас Лист1.
1. Введите число 100 в ячейку В1, число 200 — в ячейку В2 и 300 — в ячейку ВЗ.
2. В ячейку В4 введите формулу =СУММ (В1: ВЗ).
3. Нажмите комбинацию клавиш <Alt+Fll>, чтобы открыть редактор Visual Basic, и вставьте модуль в текущую рабочую книгу.
4. Создайте новую процедуру и назовите ее СвойстваДиапазона.
5. Введите код процедуры
6. Перейдите на Лист1 рабочей книги и выполните процедуру СвойстваДиа¬пазона. Появится первое окно с сообщением, что текущей ячейкой является ячейка СЗ.
7. Щелкните на кнопке ОК. Следующее окно сообщения покажет значение, отображаемое в ячейке В4.
8. Щелкните на кнопке ОК. Последнее окно покажет формулу, содержащуюся в ячейке В4. (Обратите внимание, что хотя формула в ячейке В4 введена русскими буквами, процедура корректно ее обработала и преобразовала в формулу англоязычной версии Excel)
9. Щелкните на кнопке ОК для закрытия окна сообщения.
Рассмотрим эту процедуру подробнее.
Процедура начинается с активизации ячейки A1 (т.е. установки на ней табличного курсора): После активизации ячейки Al свойство Offset перемещает табличный курсор на ячейку СЗ, процедура показывает адрес новой активной ячейки в окне сообщения:
Свойство Offset позволяет перемещаться от одного диапазона к другому и имеет следующий синтаксис:
имя_диапазона..Offset(смещ_строк, смещ_ столбцов)
Аргументы смещ_строк и смещ_столбцов задают направление перемещения табличного курсора. В нашей процедуре оба аргумента принимают значение 2, что позволяет перейти из ячейки А1 в ячейку С3.
Последние два оператора процедуры с помощью окон сообщения отображают зна¬чение и формулу ячейки В4:
Эти операторы показывают различие между свойствами Value и Formula. Свойст во Value возвращает то, что отображается в ячейке, а свойство Formula — то, что на¬ходится в ячейке.
Методы объекта Range
Объект Range имеет много методов, среди которых следующие.
 Activate (Активизировать) — активизирует (выделяет) диапазон.
 Clear (Очистить) — очищает содержимое диапазона.
 Copy (Копировать) — копирует содержимое диапазона в буфер обмена.
 Cut (Вырезать) — перемещает содержимое диапазона в буфер обмена.
 PasteSpecial (Специальная вставка) — вставляет содержимое буфера обмена в диапазон, используя при этом различные аргументы.
 Select (Выделить) — выделяет диапазон.
Использование оператора With
Изучим несколько конст¬рукций языка VBA, значительно облегчающих работу с объектами. Одна из задач, которую часто придется решать с помощью VBA, - задание различных параметров форматирова¬ния объекта. В листинге 10.1 показан пример форматирования диапазона ячеек.
Листинг 10.1. Пример форматирования диапазона
Чтобы ввести код этого листинга, вам надо снова и снова набирать Range ("А1:А6"). Конечно, можно использовать переменную-объект для ссылки на объект Range ("А1:А6"), но в этом случае вам столько же раз придется вводить имя переменной. Из подобных ситуаций VBA предлагает другой выход — оператор With, который применяется тогда, когда необходимо задать много свойств или выполнить большое количество методов одного объекта. Листинг 10.2 показывает тот же код лис-тинга 10.2, но с использованием оператора with.
Листинг 10.2. Пример использования оператора With
Оператор With имеет следующий синтаксис:
With объект
[опера торы]
End With
Объект — это тот объект, чьи свойства и методы указаны в секции операторы. Каждая строка в этой секции начинается с точки, как показано в листинге 10.2. Операторы With можно вкладывать друг в друга. В нашем листинге оператор With объекта Font вложен в оператор With объекта Range. В листинге 10.3 приведен другой пример использования оператора With.
Листинг 10.3. Оператор With, содержащий оператор и метод
Оператор For Each
Если оператор With используется для выполнения нескольких операторов для од¬ного объекта, то оператор For Each применяется для выполнения одного и того же набора операторов для нескольких объектов. Этот оператор дает возможность повто¬рить набор операторов для всех элементов коллекции.
Оператор For Each можно применять к массивам Оператор For Each имеет следующий синтаксис:
For Each элемент In группа
[Опера торы]
[Exit For]
[Опера торы]
Next
Отметим, что синтаксис предусматривает выражение Exit For. Так же, как и в других подобных выражениях Exit, для организации досрочного выхода из оператора For Each обычно применяется оператор If.
В листинге 10.4 оператор For Each применяется для изменения значений всех ячеек диапазона.
Листинг 10.4. Пример использования оператора For Each____
Использование объекта Range
В первом примере выполняется форматирование определенных строк диапазона. По¬добная операция часто применяется перед выводом на печать различных списков. Предпо¬ложим, что первая строка вашего рабочего листа содержит заголовки столбцов. Необходимо сделать полужирным шрифт в каждой второй строке. Процедура, выполняющая эту задачу, представлена в листинге 10.5.
Листинг 10.5. Применение объекта Row и оператора цикла
Основным в этой процедуре является оператор цикла For... Next. Отметим, что переменная-счетчик этого цикла iCounter имеет начальное значение 3, а затем уве¬личивается с шагом 2, обеспечивая тем самым выделение полужирным начертанием каждой второй строки.
Очевиден недостаток приведенной выше процедуры: здесь заранее указывается размер диапазона. На практике размер диапазона может часто изменяться, например в результате импорта данных из какой-либо базы данных или в результате внесения новых либо удаления старых данных. Другими словами, в процедуре не должен быть указан конкретный размер диапазона. Код листинга 10.6 выполняет выбор диапазона без указания его размера и местоположения.

Листинг 10.6. Выделение диапазона
Ключевой элемент в этой процедуре - свойство CurrentRegion (Текущая об¬ласть). Это свойство возвращает диапазон ячеек, содержащий активную ячейку и ог¬раниченный пустыми строками и пустыми столбцами. При выделении диапазона с помощью свойства CurrentRegion нет необходимости заранее знать размер и место¬положение выделяемого диапазона.
Пример следующей процедуры показывает выполнение операции "копировать и вставить". Код листинга 10.7 копирует любой заранее выделенный объект в буфер обмена, а затем вставляет его в новое положение на рабочем листе.
Листинг 10.7. Копирование и вставка диапазонов
Оператор Selection. Copy помещает выделенный диапазон в буфер обмена. Сле¬дующий оператор перемещает табличный курсор в ту ячейку, начиная с которой будет вставлен скопированный диапазон. Вставка диапазона из буфера обмена осуществля¬ется оператором ActiveSheet. Paste. Последний оператор устанавливает значение свойства CutCopyMode (Режим вырезать-вставить) объекта Application как False (Ложь). Если не выполнить этот оператор, то вокруг исходного диапазона (того, что копировался) сохранится пунктирная рамка выделения, а в строке состояния будет по-прежнему отображаться инструкция по копированию диапазона.

2

Сообщение от Busine2009 И почему нет в задании B3?

И ещё вот это.

0

А1 Товар
А2:А6 Названия каких-либо товаров
B1 Цена
B2 15
B3 5
B4 3
B5 7
B6 9

0

serb,

1

как поместить таблицу excel внутри процедуры или функции
и использовать ее для решения(задачи)

0

Возможно ли в VBA обращение к заголовку именованного диапазона?
Задача следующая:
Нужно найти номер столбца заголовка по его имени("количество")


В ячейке это выглядит так: =ПОИСКПОЗ("Количество";Фрукты[#Заголовки];0) это работает
но в VBA не может распознать диапазон Range("Фрукты[#Заголовки]")

Код VBA:
Sub Кнопка1_Щелчок()
Имя_диапазона = "Фрукты"
Искомое = "Яблоко"
Set r = Range(Имя_диапазона + "[#Заголовки]") 'как задать диапазон из заголовка в таблице Фрукты?' здесь ошибка
номер_заголовка = WorksheetFunction.Match("Количество", "Экспедиторы[#Заголовки]", 0)
значение = WorksheetFunction.VLookup(Искомое, Range(Имя_диапазона), номер_заголовка, False)
Set r = Nothing
End Sub

0

Сообщение от jinix но в VBA не может распознать диапазон Range("Фрукты[#Заголовки]")

Если заголовок(шапка) это одна строка, то можно и так :

Если количество строк неизвестно, то :

Или

1

Сообщение от pashulka Если заголовок(шапка) это одна строка, то можно и так : Visual Basic 1 Set r = Range("Фрукты").Rows(0) Если количество строк неизвестно, то : Visual Basic 1 Set r = Range("Фрукты[#Headers]") Или Visual Basic 1 Set r = ActiveSheet.ListObjects("Фрукты").HeaderRowRange

Ход мыслей у меня был правильный. Ошибка была в том что VBA воспринимает заголовки на английском языке, а в экселе они прописаны на русском.
Range("Фрукты[#Headers]")= Range("Фрукты[#Заголовки]")

Огромное спасибо.

0