Алексей Гладкий - Excel. Трюки и эффекты

Dim cell As Range

' Просмотр всех ячеек диапазона G1:K20 и замена искомого

текста

For Each cell In [G1:K20]

If cell.Value Like «*Доход*» Then

cell.Value = «Выручка»

cell.Interior.Color = RGB(255, 255, 0)

Else

cell.Interior.Color = RGB(255, 255, 255)

End If

Next

End Sub

После выполнения данного макроса слово Доход, встречающееся в диапазоне G1:K20, заменяется словом Выручка, а соответствующие ячейки выделяются желтым цветом. Вся остальная часть диапазона заливается белым цветом (при этом содержимое всех ячеек по-прежнему отображается, а сетка исчезает).

Можно настроить параметры поиска требуемого значения таким образом, что его результат будет выведен в отдельном информационном окне. Вот пример макроса (листинг 2.29), который позволяет решить эту задачу.

Sub Search()

Dim rgResult As Range

' Поиск заданного значения в диапазоне B1:B20 и вывод результата

Set rgResult = Range(«B1:B20»).Find(9999, , xlValues)

If rgResult Is Nothing Then

MsgBox «Поиск не дал результатов»

Else

MsgBox rgResult.Address

End If

End Sub

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

Достаточно удобным является вид поиска, в котором используется выделение найденных ячеек. Применение данного способа весьма целесообразно при работе с большими объемами данных. Для настройки поиска можно использовать, например, такой макрос (листинг 2.30).

Sub FindAndSelect()

Dim strStartAddr As String ' Хранит координаты первого найденного _ значения

Dim rgResult As Range

' Поиск первого входжения искомого слова

Set rgResult = Range(«B1:B10»).Find(«Прибыль», , xlValues)

If Not rgResult Is Nothing Then

' Сохраним адрес найденной ячейки (чтобы контролировать _

зацикливание поиска)

strStartAddr = rgResult.Address

End If

Do While Not rgResult Is Nothing

' Обработка результата поиска

rgResult.Interior.Color = RGB(255, 255, 0)

' Новый поиск

Set rgResult = Range(«B1:B10»).FindNext(rgResult)

If rgResult.Address = strStartAddr Then

' Поиск завершен

Exit Do

End If

Loop

End Sub

Этот макрос обрабатывает диапазон В1:В10 и ищет в нем значение Прибыль. Все ячейки, в которых обнаружено данное значение, будут выделены желтым цветом (строка rgResult. Interior. Color = RGB (255, 255, 0)). С помощью подобных макросов можно обрабатывать любые диапазоны и находить в них любые значения.

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

Sub RangeBorder()

Dim rgRange As Range

Set rgRange = Range(«B2:D5»)

' Оформление верхней границы диапазона

With rgRange.Borders(xlEdgeTop)

.Weight = xlThick

.LineStyle = xlContinuous

.Color = RGB(0, 0, 255)

End With

' Оформление нижней границы диапазона

With rgRange.Borders(xlEdgeBottom)

.Weight = xlMedium

.LineStyle = xlDash

.Color = RGB(255, 0, 255)

End With

End Sub

Очевидно, что цвета и толщину линии можно изменять по своему усмотрению.

Можно настроить программу таким образом, что при щелчке кнопкой мыши на любом месте рабочего листа на экране будет появляться окно с информацией об адресе текущей ячейки, причем в разных форматах. Код макроса, который позволяет решить эту задачу, приведен в листинге 2.32 (следует учитывать, что данный код должен быть помещен в модуль рабочего листа).

Sub Worksheet_SelectionChange(ByVal Target As Range)

' Вывод адреса ячейки в различных форматах

MsgBox Target.Address() & vbCr & _

Target.Address(RowAbsolute:=False) & vbCr & _

Target.Address(ReferenceStyle:=xlR1C1) & vbCr & _

Target.Address(ReferenceStyle:=xlR1C1, _

RowAbsolute:=False, ColumnAbsolute:=False, _

RelativeTo:=Worksheets(1).Cells(2, 2))

End Sub

Теперь при щелчке кнопкой мыши на ячейке, например, Е9 на экране отобразится окно со следующим сообщением:

$E$9

$E9

R9C5

R[7]C[3]

Адрес ячейки представлен в нескольких форматах, в том числе и относительно другой ячейки. В качестве ячейки, относительно которой определяется адресация, в данном случае используется ячейка В2 (см. в коде значение параметра RelativeTo).