Переход к ячейке по адресу из формулы рабочего листа вида =Лист!АдресЯчейки с помощью функций для работы с текстом из кода VBA Excel.
Преобразование формулы рабочего листа
Преобразование формулы рабочего листа для получения адреса ячейки рассмотрим для следующих примеров формул: =Лист1!A1 и ='Имя Листа'!A1. Вторая формула примечательна тем, что имя листа в ней содержит пробел и заключено в одинарные кавычки (апострофы).
Если для выбора целевого рабочего листа использовать его имя из формулы, заключенное в одинарные кавычки, произойдет ошибка.
Для извлечения имени листа и адреса ячейки будем использовать следующие функции VBA Excel для работы с текстом: Split, Mid, Left и Len. Функция Len возвращает длину строки, выраженную в количестве составляющих ее знаков.
Порядок извлечения имени целевого листа и адреса целевой ячейки из формулы, размещенной в исходной ячейке:
- Удаляем из формулы знак равенства (
"="). - С помощью функции Split делим формулу на имя листа и адрес ячейки, указав в качестве разделителя восклицательный знак (
"!"). - Если имя листа заключено в апострофы, удаляем их.
- Теперь имя листа и адрес ячейки, выделенные из формулы, можно использовать для идентификации целевой ячейки.
Код для перехода к ячейке из формулы
Рассмотрим пример кода VBA Excel для извлечения адреса целевой ячейки из формулы в активной ячейке на текущем листе и активация целевой ячейки на целевом листе по его имени из формулы.
Код для активации целевой ячейки по ее адресу из формулы, размещенной в другой ячейке:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub Primer1() Dim s As String, a() As String 'В переменную s записываем формулу без знака "=" s = Mid(ActiveCell.FormulaLocal, 2) 'В массив a записываем по отдельности имя листа и адрес ячейки a = Split(s, "!") 'Удаляем апострофы, в которые заключены имена листов с пробелами If Left(a(0), 1) = "'" Then a(0) = Mid(a(0), 2, Len(a(0)) - 2) End If 'Выбираем рабочий лист по его имени из формулы ThisWorkbook.Worksheets(a(0)).Select 'Активируем ячейку по адресу из формулы Range(a(1)).Activate End Sub |
Если нужно внести какие-то изменения в целевую ячейку без ее активации, обратиться к ней можно следующим образом:
|
1 2 3 4 5 |
With ThisWorkbook.Worksheets(a(0)).Range(a(1)) .Value = "Новое значение" .Borders.LineStyle = True .Interior.Color = 4569006 End With |
Самый простой вариант перехода
Первый пример перехода по ссылке из формулы предназначен, в том числе, для демонстрации применения функций для работы с текстом. Переход к ячейке по адресу из формулы, расположенной в другой ячейке текущей книги, можно осуществить проще:
|
1 2 3 4 5 6 7 8 9 10 |
Sub Primer2() Dim s As String s = ActiveCell.Formula With Range(s) 'Выбор листа, указанного в формуле Sheets(.Parent.Name).Select 'Активация ячейки по адресу, указанному в формуле Cells(.Row, .Column).Activate End With End Sub |
Обращение к ячейке из формулы, расположенной в другой ячейке, для внесения изменений:
|
1 2 3 4 |
With Range(s) .Value = "Самое новое значение" .Font.Color = vbBlue End With |
Код второго примера аналогичен коду VBA Excel для перехода по ссылке к ячейке в другой книге, рассмотренному в следующей статье.
Добрый вечер!
Подскажите пожалуйста, какую применить формулу к ячейкам?
Допустим, основные ячейки для заполнения A1, B1, C1, D1, если в этих ячейках имеются данные, то автоматически новые данные должны попасть G1, H1, I1, J1 и наоборот, если ячейки G1, H1, I1, J1 имеют данные, то автоматически новые данные должны попасть A1, B1, C1, D1.
Если есть что-то подобное, очень прошу помочь!
Спасибо!
Здравствуйте, Константин!
Как решить с помощью формул — не знаю. С помощью кода VBA можно так:
Обсуждение закрыто.