Перейти к содержимому
Наше приложение «Дешевые авиабилеты» в AppGallery >>

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

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

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

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

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

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

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

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

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

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

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

    1 2 3 4 5
    6 7 8 9 10
    11 12 13 14 15
    16 17 18 19 20
    21 22 23 24 25

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Информационное окно MsgBox добавлено, чтобы вы могли увидеть работу процедуры поэтапно, если решите проверить ее в своей книге Excel.

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

    При получении диапазона с помощью метода Application.InputBox и присвоении его переменной диапазона, бывает полезно узнать его размерность. Это можно сделать следующим образом:

    Запустите процедуру, выберите на рабочем листе Excel любой диапазон и нажмите кнопку «OK». Информационное сообщение выведет количество строк и столбцов в диапазоне, присвоенном переменной myRange.

    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    113 комментариев для “VBA Excel. Переменная диапазона ячеек (As Range)”

    1. А как эту ячейку скопировать в буфер обмена?

      машина ругается на такую команду

      1. Евгений

        А так работает?

    2. извиняюсь за задержку
      Да, так работает.
      Но это классика.

      PS
      стал проверять/экспериментировать с именами …
      как самостоятельная программа

      эта команда работает
      А как часть большой программы — ругается .
      буду проверять/искать где накосячил

    3. Добрый день!
      EXCEL 2007

      Есть процедура
      Sub Vibor(YAC As Range) — получаем адрес ячейки

      Есть макрос в котором надо передать адрес ячейки, например «B8» или «D8».
      Пробовал в макросе указать Vibor «B8», Call Vibor(B8) и т.д.
      Постоянно вылетают ошибки.

      Как при вызове процедуры передать адрес ячейки?

      Всё перелопатил, ответа не нашёл.
      Заранее спасибо.

      1. Евгений

        Добрый день, Юрий!
        Для ячейки «B8»:

        или

        1. Спасибо!
          К сожалению смогу попробовать позже. По результатам отпишусь.
          Ещё раз, спасибо!

        2. Проверил.
          У меня: Call Vibor(Range(«F8»), 2) — всё работает!
          Спасибо!!!

    4. Дмитрий

      Добрый день! Написал код для сравнения ячеек двух несмежных диапазонов. Если дата в ячейке второго диапазона меньше или равна дате первого диапазона, то ячейка окрашивается в красный цвет и появляется сообщение о некорректности даты. Применил цикл, но макрос срабатывает только на указанных в коде ячейках. Видимо некорректно указал реквизиты ячеек.

      1. Евгений

        Здравствуйте, Дмитрий!
        Еще раз отправьте код, и напишите, какие диапазоны вы сравниваете.

    5. Дмитрий

      Код опять передался некорректно — выпала часть и операторы пропали. Видимо не получится. Извините за беспокойство

      1. Евгений

        Дмитрий, для сравнения диапазонов «H1:H39» и «J1:J39» могу предложить следующий код:

    6. Дмитрий

      Евгений, добрый день! Что-то не так — ячейка не окрашивается в красный цвет. Или я что-то не так делаю. Я привязываю код к событию «Change». Как я понимаю, макрос должен работать для любых пар ячеек в диапазонах J и H?

      1. Евгений

        Дмитрий, у меня работает:

        Событие Worksheet.Change срабатывает, когда на рабочем листе изменяются значения ячеек. Попробуйте ввести новое значение в ячейку «A1» или любую другую ячейку и нажать клавишу «Enter».

    7. Дмитрий

      Вы правы — все работает! Еще раз снимаю шляпу!

    8. Добрый день, Евгений!
      Если можно прокомментируйте, пожалуйста, эти строки

      1. Евгений

        Добрый день!
        Судя по последней строке, это сравнение значений ячеек двух одинаковых диапазонов. Скорее всего, первая строка должна быть:

        Третья строка — присвоение массивам a и b значений диапазонов x и y.
        Четвертая и пятая строка — двойной цикл для прохода по всем элементам массивов a и b.
        Шестая строка — сравнение элементов двух массивов. WordPress удалил из нее знак сравнения, но, скорее всего, это было так:

    9. А можно написать отдельно

    10. Добрый день. На протяжении всего макроса успешно работала строка кода :
      .Cells(i, 9) = Application.WorksheetFunction.Sum(.Range(Cells(3, 2), Cells(y, 2))) (вариант 1).

      Но позже при написании при использовании именно этой конструкции Range(Cells(x, y), Cells(x, y)) появилась ошибка: error 1004 Application-defined or object defined error.

      При замене диапазона следующим образом: WorksheetFunction.Sum(.Range("B3:B" & y)) (вариант 2), ошибка исчезла.
      Почему перестал работать 1 вариант? Ошибок при написании кода не вижу. Какое есть решение, чтобы дальше продолжать использовать в коде вариант1? Спасибо

      1. Евгений

        Добрый день, Юлия!
        Все должно работать, проверьте на этом коде:

    11. Добрый день.
      Подскажите, пожалуйста, есть столбец с данными, числовые данные (нулевые также присутствуют) задача в текущем столбце до последнего значения сделать вычисление, а именно разделить на 100.
      Эксель ругается, что в некоторых ячейках нет данных.
      Скрипт след:

      Как можно проще решить проблему?
      Заранее благодарен!

      1. Возможно, вся структура неверная, я не очень силен в VBA, только осваиваю…
        Вопрос только в том: как изменить (с помощью вычислений) весь массив столбца (до последнего значения), а именно разделить на 100. В пред. сообщении я поставил I20:I1000 — только с целью увеличить диапазон…
        и условие задал чтобы считал только ячейки где есть данные…Данные начинаются с 20 строки)

        1. Евгений

          Добрый день, Володя!
          Самый простой код для любого диапазона:

          Ваш код будет выглядеть следующим образом:

    12. Спасибо большое!
      Единственный вопрос, а если есть необходимость завершить деление на последней строке с данными? и также в случае добавлении другого столбца с тем же решением (деление на 100).
      Спасибо!

      1. Евгений

        Для моего примера с несколькими столбцами (чтобы не делились данные в последней строке на 100):

    13. Добрый день.
      Не смогу наверное без Вашей помощи, вопрос в следующем: необходимо выполнить заполнение столбца «Р» на основании данных столбца «О», а именно если в столбце «О» указано слово «Кофе», тогда в столбце «Р» должно быть «да». Также я задал условие для расчета только до последней строки (lngkonec_res) и начало расчета (lngnachalo). Он ругается на : b = shRes.Range("O" & i).Value
      Скрипт:

      Буду очень благодарен!

      1. Евгений

        Здравствуйте, Володя!
        Удалите лишнюю строку:

        Возможно, эта строка записана неправильно:

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

    14. Константин

      Добрый вечер, Евгений!
      Помогите разобраться, пожалуйста!
      У меня в книге 61 лист, на каждом листе одна и таже таблица. Данные в книгу поступают из других файлов Excel (методом =’D:\…..).
      В 61 листе расположил кнопку элемента управления, которая открывает созданную форму.
      Код для кнопки в userform записан 60 раз в соответствии с листами

      Если есть вариант по короче, буду рад.

      Далее основная цель, найденные данные по фамилии должны отобразится на листе61.

      В userform кнопка добавить, содержит:

      Другими словами, надо найти в этих листах фамилию с имеющимися данными на листе61.
      Может что-то упростите если посчитаете нужным.
      Спасибо!

      1. Евгений

        Здравствуйте, Константин!
        Для первой кнопки протестируйте следующий код:

        1. Константин

          Работает, но есть маленький нюанс.
          Пробывал четыре раза найти и добавить (на лист который не учитывается):
          1-й данные все отобразились
          2-й данные все отобразились
          3-й данные все отобразились, но отобразились вместо 2-го
          А так вообщем с двумя листами работает.

    15. Константин

      Евгений!
      Подскажите пожалуйста, как написать код в userform, найти по двум параметрам (фамилия, имя).
      Столкнулся с тем, что фамилии есть одинаковые имена разные.
      Спасибо!

      1. Евгений

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

    16. Константин

      B1 — фамилия — Textbox28
      С1 — имя — Textbox29
      D1 — отчество — Textbox30

      1. Евгений

        Если как вы обозначили, то так:

        Для вашего кода (в моем варианте):

        1. Константин

          Попробовал оба варианта ТИШИНА, работает с этой записью

          получается только с фамилией (В1)

          1. Евгений

            Строка

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

            1. Константин

              Доброе утро, Евгений!
              Не получается. Ввожу данные для поиска, кликаю кнопку, она не реагирует.
              На листах таблица B1 — фамилия, C1 — имя, D1 — отчество и т.д.
              В UserForme 29 TextBox.

              TextBox1 соответствует заполнению ФАМИЛИЕЙ
              TextBox2 соответствует заполнению ИМЕНЕМ
              TextBox3 соответствует заполнению ОТЧЕСТВОМ
              ……
              TextBox28 соответствует заполнению для ПОИСКА по ФАМИЛИИ
              TextBox29 соответствует заполнению для ПОИСКА по ИМЕНИ
              Возможно ПОИСК по 60-ти листам сразу по двум данным невозможен.

              По фамилии находит

              По фамилии, имени, отчеству КНОПКА даже не нажимается ТИШИНА

              1. Евгений

                Добрый вечер, Константин!
                Скопируйте из любой таблицы фамилию, имя и отчество в блокнот. Откройте поисковую форму, вставьте скопированные слова в TextBox28, TextBox29, TextBox30 и запустите поиск. Если получится, значит проблема в латинских символах, пробелах или знаках переноса строки.

    17. Константин

      Добрый вечер, Евгений!

      Ваш код по поиску, работает, но только со строкой

      Может быть я что-то не так сказал, извините.
      Пожелания к коду были: пишем в textbox28 фамилию, textbox29 имя, жму кнопку CommandButton1_Click (найти), появляется фамилия имя и все остальные привязанные данные.
      Т.е. данные надо найти по двум textbox одновременно, пока код работает только с одним.

      1. Евгений

        Константин, это поиск по фамилии, имени и отчеству (он не работает, если вы не вписываете отчество в TextBox30):

        По фамилии и имени будет так:

        Только по фамилии:

        Свойство .Value объекта Range и свойство .Text объекта TextBox можно не указывать, так как они являются свойствами этих объектов по умолчанию.

    18. Константин

      Добрый день, Евгений!
      Сегодня проверил ниже написанный код, ВСЕ работает.

      Я как и чувствовал, что что-то упустил, не объяснил.
      В данном коде (строке) получается, что ТОЛЬКО по фамилии или ТОЛЬКО по фамилии и имени.
      А универсальное решение подобрать можно? Чтобы поиск можно было осуществлять в основном по ФАМИЛИИ, либо по ФАМИЛИИ и ИМЕНИ.
      Однофамильцы попадаются, но редко, а учесть это в коде надо обязательно.
      Спасибо огромное!

    19. Константин

      Добрый вечер, Евгений!
      Сегодня столкнулся снова с поиском. Получилось так, что надо было найти двух разных людей, а у них фамилия и имя одинаковы, отчества разные.
      Как же все таки добавить (изменить) код, чтобы в основном искать по фамилии, если фамилии одинаковые добавлять для поиска имя, ну и если фамилия и имя совпадает, то для поиска добавлять отчество.

      Спасибо!

      1. Евгений

        Решение есть:
        1. Все ФИО копируете на отдельный лист.
        2. TextBox28, TextBox29 и TextBox30 меняете на элементы управления ComboBox.
        3. Вы выбираете фамилию. Если нет повторов, имя и отчество подставляются автоматически. Если есть повторы, поля пустые и требуется выбор.
        4. Если не знаете, как написать код для заполнения ComboBox-ов, дам ссылку, когда будет готова статья по этой теме.

        1. Константин

          Доброй ночи, Евгений!
          Я решил добавить в userform дополнительно кнопку CommandButton.

          Получилось вот так:

          Спасибо!

    20. Константин

      Добрый день, Евгений!
      А может быть Userform дополнить флажками?
      Стоит галочка у TextBox28 (фамилия), значит поиск нажатием на кнопку CommandButton1_Click, только по ФАМИЛИИ.
      Стоит галочка у TextBox28 (фамилия) и TextBox29 (имя), значит поиск нажатием на кнопку CommandButton1_Click, только по ФАМИЛИИ и ИМЕНИ.
      Стоит галочка у TextBox28 (фамилия) и TextBox29 (имя), TextBox30 (отчество) значит поиск нажатием на кнопку CommandButton1_Click, только по ФАМИЛИИ, ИМЕНИ и ОТЧЕСТВУ.

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

      Спасибо!

    Обсуждение закрыто.