Excel присваивает значение ячейке vba excel

Обновлено: 03.07.2024

Возвращает или задает значение Variant, представляющее значение указанного диапазона.

Синтаксис

выражение.Value (RangeValueDataType)

выражение Переменная, представляющая объект Range.

Параметры

Имя Обязательный/Необязательный Тип данных Описание
RangeValueDataType Необязательно Вариант Тип данных значения диапазона. Может быть константой XlRangeValueDataType.

Примечания

При задании диапазона ячеек с содержимым файла электронной таблицы XML используются только значения первого листа книги. Вы не можете установить или получить несмежный диапазон ячеек в формате электронной таблицы XML.

Член Range по умолчанию перенаправляет вызовы без параметров в Value. Таким образом, someRange = someOtherRange эквивалентно someRange.Value = someOtherRange.Value .

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

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

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

Пример

В этом примере для ячейки A1 на листе Sheet1 активной книги задается значение 3,14159.

В этом примере зацикливаются ячейки A1:D10 на листе Sheet1 активной книги. Если значение одной из ячеек меньше 0,001, код заменяет это значение на 0 (ноль).

В этом примере циклически перебираются значения в диапазоне A1:CC5000 на листе Sheet1. Если одно из значений меньше 0,001, код заменяет значение на 0 (ноль). Наконец, он копирует значения в исходный диапазон.

Поддержка и обратная связь

Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.

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

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

Кто-нибудь знает лучший способ сделать это?


7 ответов 7

Короткий ответ:

Не используйте vba, используйте формулу. В частности, комбинация ЕСЛИ и ПОИСК.

Но это проверка кода, так что давайте все равно сделаем это.

Регулярное выражение работает медленно. Кажется, вы используете его только из-за нечувствительности к регистру. Учитывая это, вы можете напрямую сравнивать значения ячеек, используя StrComp с параметром vbTextCompare. (полезная статья о StrComp)

i и j обычно используются для счетчиков цикла, но row и col в этом случае имеют больше смысла.

Вот как это может выглядеть:


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

\$\begingroup\$ Этот код не учитывает сравнение без учета регистра, но вы правы. Регулярное выражение является излишним, и в этом случае предпочтение отдается сравнению строк. (Кстати, добро пожаловать в Code Review!) \$\endgroup\$

Могу ли я предложить сократить время выполнения/усилия на 50 %?

Разве никто не заметил, что в ОП говорится о «проверке ОДНОГО столбца, записи в СЛЕДУЮЩИЙ смежный столбец», правда? Зачем тогда зацикливать столбцы? Второй проход будет проверять либо пустую ячейку, либо ячейку со словом «Эксплуатация».


Тогда просто заполните его. Это нечувствительно к регистру.

Это автозаполнение можно выполнить двумя способами: интерактивно на листе или программно:

Интерактивно: в Excel есть функция автозаполнения. Выбрав B1 и вставив эту формулу, просто дважды щелкните маркер заполнения, который представляет собой крошечный квадрат в правом нижнем углу ячейки, когда он выбран. Excel интеллектуально скопирует формулу до конца непрерывного диапазона, содержащего данные. Это означает, что если в A1-A256 есть данные без пробелов, они будут автоматически заполнены до B256. В качестве альтернативы, если есть пробелы, прокрутите вниз и выберите B256 (или любой другой конец).Затем Ctrl + Shift + стрелка вверх, чтобы выбрать диапазон, ведущий к B1, и Ctrl+D, чтобы скопировать его вниз (думаю, d = то же самое)

Использование VBA. если вы должны сделать это программно: с формулой, содержащей ячейку в качестве вашего выбора:

Есть и другие варианты автозаполнения, позволяющие выполнять несколько крутых трюков. Может копировать буквальное значение вместо формулы или также заполнять серию на основе шаблона. Вы также можете установить пользовательские шаблоны для распознавания, например направления бизнеса, которые вы часто повторяете в вещах, или города, в которых у вас есть магазины, и т. д.

JA Gomez из Power Spreadsheets

Дж.А. Гомес

Excel VBA Tutorial о том, как установить или получить значение ячейки или диапазона ячеек с помощью макросов

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

Это руководство по VBA сопровождается книгами Excel, содержащими макросы и данные, которые я использую в приведенных ниже примерах. Вы можете получить немедленный доступ к этим примерам книг, подписавшись на информационный бюллетень Power Spreadsheets.

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

Оглавление

Похожие руководства по VBA и макросам

Следующие учебные пособия по VBA и макросам могут помочь вам лучше понять и реализовать приведенное ниже содержание:

    Общие конструкции и структуры VBA:

    Начните работать с макросами здесь.

    Узнайте, как копировать и вставлять значения здесь.

Дополнительные учебные пособия по VBA и макросам можно найти в архивах.

Код VBA для установки значения ячейки

Процесс установки значения ячейки

Чтобы установить значение ячейки с помощью VBA, выполните следующие действия:

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

Объяснение оператора VBA

Чтобы вернуть такой объект Range, используйте такие конструкции, как свойства Worksheet.Range, Worksheet.Cells, Application.ActiveCell, Application.Selection, Range.Range, Range.Cells или Range.Offset.

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

    Конструкция VBA: свойство Range.Value или Range.Value2.

Разница между Range.Value и Range.Value2 заключается в типах данных, с которыми они работают. Range.Value2 не использует ни валюту, ни дату. Это различие особенно важно для целей получения значения ячейки. Более подробное обсуждение этой темы см. в соответствующем разделе.

    Конструкция VBA: оператор присваивания.

    Конструкция VBA: новое значение свойства Range.Value или Range.Value2.

Примеры макросов для установки значения ячейки

В следующем примере макроса для значения ячейки (myCellSetValue) задается строка «установить значение ячейки с помощью Range.Value» со свойством Range.Value.

В следующем примере макроса для значения ячейки (myCellSetValue2) задается строка «установить значение ячейки с помощью Range.Value2» со свойством Range.Value2.

Эффекты выполнения примера макроса для установки значения ячейки

Следующий GIF-файл иллюстрирует результаты выполнения первого примера макроса, который работает со свойством Range.Value. В качестве значения ячейки A7 устанавливается строка «установить значение ячейки с помощью Range.Value».

Следующий GIF-файл иллюстрирует результаты выполнения второго примера макроса, который работает со свойством Range.Value2. В качестве значения ячейки A11 устанавливается строка «установить значение ячейки с помощью Range.Value2».

Код VBA для установки значения диапазона ячеек

Процесс установки значения диапазона ячеек

Чтобы установить значение диапазона ячеек с помощью VBA, выполните следующие действия:

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

Объяснение оператора VBA

Чтобы вернуть такой объект Range, используйте такие конструкции, как свойства Worksheet.Range, Worksheet.Cells, Application.Selection, Range.Range, Range.Cells, Range.Offset или Range.Resize.

Если вы явно объявляете объектную переменную для представления CellRange, используйте объектный тип данных Range.

    Конструкция VBA: свойство Range.Value или Range.Value2.

Разница между Range.Value и Range.Value2 заключается в типах данных, с которыми они работают. Range.Value2 не использует ни валюту, ни дату. Это различие особенно важно для целей получения значения диапазона ячеек. Более подробное обсуждение этой темы см. в соответствующем разделе.

    Конструкция VBA: оператор присваивания.

    Конструкция VBA: новое значение Range.Value или Range.Свойство Value2.

Примеры макросов для установки значения диапазона ячеек

В следующем примере макроса значение диапазона ячеек (myCellRangeSetValue) задается строкой «установить значение диапазона ячеек с помощью Range.Value» со свойством Range.Value.

В следующем примере макроса значение диапазона ячеек (myCellRangeSetValue2) задается строкой «установить значение диапазона ячеек с помощью Range.Value2» со свойством Range.Value2.

Эффекты выполнения примера макроса для установки значения диапазона ячеек

Следующий GIF-файл иллюстрирует результаты выполнения первого примера макроса, который работает со свойством Range.Value. В качестве значения ячеек с A15 по C19 устанавливается строка «установить значение диапазона ячеек с помощью Range.Value».

Следующий GIF-файл иллюстрирует результаты выполнения второго примера макроса, который работает со свойством Range.Value2. В качестве значения ячеек с A23 по C27 устанавливается строка «установить значение диапазона ячеек с помощью Range.Value2».

Код VBA для получения значения ячейки

Процесс получения значения ячейки

Чтобы получить значение ячейки с помощью VBA, выполните следующие действия:

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

Объяснение оператора VBA

Если вы явно объявляете myVariable, используйте тип данных, способный хранить все потенциальные значения, которые может содержать Cell.

    Конструкция VBA: оператор присваивания.

Чтобы вернуть такой объект Range, используйте такие конструкции, как свойства Worksheet.Range, Worksheet.Cells, Application.ActiveCell, Application.Selection, Range.Range, Range.Cells или Range.Offset.

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

    Конструкция VBA: свойство Range.Value или Range.Value2.

Разница между Range.Value и Range.Value2 заключается в типах данных, с которыми они работают. Range.Value2 не использует ни валюту, ни дату. Таким образом, если числовой формат Cell — Date или Currency, Range.Value преобразует значение Cell в тип данных Date или Currency, в зависимости от ситуации. Range.Value2 не выполняет это преобразование, поэтому Range.Value2 обычно возвращает такое значение, как тип данных Double.

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

Следующий пример макроса:

    Получает значение ячейки (A7) с помощью свойства Range.Value.

Следующий пример макроса:

    Получает значение ячейки (A7) с помощью свойства Range.Value2.

Эффекты выполнения примера макроса для получения значения ячейки

Следующий GIF-файл иллюстрирует результаты выполнения первого примера макроса, который работает со свойством Range.Value. В окне сообщения отображается значение ячейки A7.

Обратите внимание, что ячейка A7 отформатирована как денежная единица. Свойство Range.Value преобразует значение ячейки в тип данных Currency, в результате чего получается число с фиксированной запятой с 4 десятичными разрядами. Таким образом, в окне сообщения отображается значение только с 4 знаками после запятой вместо 10 знаков после запятой, которые есть в исходном значении в ячейке A7.

Следующий GIF-файл иллюстрирует результаты выполнения второго примера макроса, который работает со свойством Range.Value2. В окне сообщения отображается значение ячейки A7.

Свойство Range.Value2 не работает с типом данных Currency. Таким образом, в окне сообщения отображаются все (10) десятичных разрядов исходного значения в ячейке A7.

Код VBA для получения значения диапазона ячеек

Процесс получения значения диапазона ячеек

Чтобы получить значение диапазона ячеек с помощью VBA, выполните следующие действия:

    Объявить массив данных типа Variant (myArray).

Объяснение оператора VBA

  1. Элемент: Dim myArray() как вариант.

    Конструкция VBA: оператор присваивания.

Чтобы вернуть такой объект Range, используйте такие конструкции, как свойства Worksheet.Range, Worksheet.Cells, Application.Selection, Range.Range, Range.Cells, Range.Offset или Range.Resize.

Если вы явно объявляете объектную переменную для представления CellRange, используйте объектный тип данных Range.

    Конструкция VBA: свойство Range.Value или Range.Value2.

Разница между Range.Value и Range.Value2 заключается в типах данных, с которыми они работают. Range.Value2 не использует ни валюту, ни дату. Поэтому, если числовой формат CellRange — Date или Currency, Range.Value преобразует значения в CellRange в тип данных Date или Currency, в зависимости от ситуации. Range.Value2 не выполняет это преобразование, поэтому Range.Value2 обычно возвращает такие значения, как тип данных Double.

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

Следующий пример макроса:

    Получает значения диапазона ячеек (от A11 до C15) с помощью свойства Range.Value.

Следующий пример макроса:

    Получает значения диапазона ячеек (от A11 до C15) с помощью свойства Range.Value2.

Эффекты выполнения примера макроса для получения значения диапазона ячеек

Следующий GIF-файл иллюстрирует результаты выполнения первого примера макроса, который работает со свойством Range.Value. В окнах сообщений отображаются значения ячеек от A11 до C15.

Обратите внимание, что диапазон ячеек отформатирован как валюта. Свойство Range.Value преобразует значения диапазона ячеек в тип данных Currency, что приводит к числам с фиксированной запятой с 4 десятичными разрядами. Поэтому в окнах сообщений отображаются значения с максимум 4 десятичными знаками вместо 10 десятичных знаков, которые есть у исходных значений в диапазоне ячеек.

Следующий GIF-файл иллюстрирует результаты выполнения второго примера макроса, который работает со свойством Range.Value2. В окнах сообщений отображаются значения ячеек от A11 до C15.

Свойство Range.Value2 не работает с типом данных Currency. Поэтому в окнах сообщений отображаются все (10) знаков после запятой, которые есть у исходных значений в диапазоне ячеек.

Ссылки на конструкции VBA, используемые в этом руководстве по VBA

Используйте следующие ссылки, чтобы посетить соответствующую веб-страницу в Microsoft Developer Network:

    Определите ячейку или диапазон ячеек, значение которых вы хотите установить или получить:

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

Получение значений ячеек

Чтобы получить значение ячейки в VBA, нам нужно сослаться на нее с помощью объекта Range, а затем вызвать свойство .Value.

Для нашего примера мы будем использовать следующую электронную таблицу. Это простая таблица с некоторыми именами.

Значения ячеек — пример электронной таблицы

Чтобы получить значение из ячейки A2, вы можете использовать этот фрагмент кода:

Это возьмет ячейку A2 и поместит ее в переменную val . Затем мы распечатываем значение в Immediate Window (которое значение в нашем примере равно Joseph ).

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

Что произойдет, если вы используете .Value для набора ячеек?

Давайте изменим наш предыдущий фрагмент кода на следующий:

Если вы запустите этот код, вы получите сообщение об ошибке о несоответствии типов.

Что здесь происходит?

Проблема в том, что когда вы работаете с набором ячеек, .Value может возвращать только одно значение. Поэтому, когда мы просим VBA вернуть значение .Value для нашей переменной (которая относится к нескольким ячейкам), свойство .Value не знает, на какую ячейку мы ссылаемся.

Как получить одну ячейку из набора ячеек?

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

Функция Range.Cells

Функция Cells() позволяет взять диапазон ячеек и вернуть одну ячейку из набора. Вот определенная функция:

Параметр Тип Определение
номер_строки Integer Номер строки из диапазона, на который вы хотите сослаться.
column_number Integer Номер столбца из диапазона, на который вы хотите сослаться.

Взгляните на следующий код:

Здесь мы взяли диапазон A2:A5 и сослались на столбец 1 строки 1. Поскольку переменная диапазона cellRange ссылается на A2:A5 , первой строкой является строка 2, а первым столбцом – A .

БУДЬТЕ ОСТОРОЖНЫ!

При использовании функции Cells() помните, что строка 1 и столбец 1 представляют крайнюю левую ячейку в диапазоне, с которым работает функция Cells(). . Если ваш диапазон A1:D5 , то Cells(1, 1) будет ссылаться на A1 , но если ваш диапазон B2:D6 , то Cells(1, 1) будет ссылаться на B2 .

Хорошо, это охватывает получение значений ячеек из объектов диапазона, теперь давайте обсудим настройку значений ячеек с помощью объектов диапазона.

Эта статья поможет вам? Если да, поддержите меня чашечкой кофе ☕️

Установка значений ячеек

Чтобы установить значение ячейки, вы можете использовать то же свойство .Value при ссылке на ячейку. В этом примере мы возьмем значение A2 и изменим его с Joseph на John :

Сначала мы устанавливаем для переменной cellRange значение A2. Затем мы сказали cellRange.Value = "John", что изменило свойство переменной .Value.Помните, однако, что переменная является ссылкой на ячейку A2, поэтому все, что вы делаете с этой переменной, вы также делаете с ячейкой A2 на листе. Наконец, мы выводим значение A2 в Immediate Window, чтобы увидеть, что оно изменилось.

Мы также можем увидеть изменение значения на листе после запуска этого кода:

Значения ячеек - Установить Значение

Как установить значения для нескольких ячеек?

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

Если вы запустите этот код, он установит для всех ячеек A2:A5 значение John :

Значения ячеек - Установить несколько значений - упс

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

Давайте на секунду возьмем реальный пример. Допустим, у нас есть два столбца: Имя и Фамилия. Мы хотим взять столбец «Фамилия» и поместить его значение после значения «Имя»; по сути, объединяя значения для создания одного столбца имени.

Вот наш образец данных:

Значения ячеек - Имя Фамилия

Наша задача состоит в том, чтобы объединить столбцы имени и фамилии и поместить результат в столбец A. Как мы это делаем?

Одно из решений – пройтись по ячейкам с A2 по A5, а затем установить для этой ячейки ее собственное значение, плюс пробел и фамилию ячейки рядом с ней.

Звучит достаточно просто, давайте напишем код:

Давайте пройдемся по коду.

  • Сначала мы создаем переменную с именем named . Затем мы устанавливаем его в диапазоне A2:A5 .
  • Далее мы создаем переменную с именем cell . Это будет временная переменная, которая будет меняться при каждой итерации цикла.

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

*При первом запуске цикла для ячейки установлено значение A2 . Затем A3, затем A4 и, наконец, A5. После этого в переменной name больше не осталось ячеек, поэтому цикл завершается.

  • Я расскажу, как перебирать диапазоны в цикле, в следующем посте, так как этот пост уже достаточно длинный!

Теперь мы готовы объединить имя и фамилию. Мы делаем это с помощью другой функции Range, которая называется Offset(_rows_, _columns_) . Идея этой функции заключается в том, что если вы находитесь в ячейке, такой как A2, и вы говорите cell.Offset(0, 1), то на самом деле мы говорим «переместиться на один столбец вправо». Это помещает нас в ячейку B2. Вот как мы можем получить фамилию в нашем примере.

  • Я более подробно расскажу о том, как использовать функцию Offset(), в следующем посте. Опять же, этот пост длился достаточно долго.

Вот результаты кода после его запуска:

Значения ячеек - Имя Фамилия Результат

Отсюда мы могли бы заменить ячейку A1 просто именем и полностью удалить столбец B.

Получение и установка значений ячеек из именованного диапазона или имени таблицы

И последнее, на что я хотел бы обратить внимание, это то, что при использовании функции Range() вы можете использовать именованный диапазон или имя таблицы вместо диапазона, например A2:A5 . В нашем первом примере наши данные находятся в таблице с именем Table1. Чтобы обратиться к данным таблицы, мы могли бы использовать следующее:

Чтобы сослаться на всю таблицу, мы можем использовать структурированные ссылки следующим образом:

Это вернет значение A1 «Имя», так как таблица начинается в A1.

Кроме того, если вы не знакомы с таблицами Excel, нажмите здесь, чтобы узнать больше.

Что дальше?

Честно говоря, с объектами диапазонов в VBA можно многое обсудить. Я затрону многие другие темы, касающиеся диапазонов в VBA, в следующих сообщениях, таких как:

  • Изменение цветов ячеек
  • Поиск ячеек по их текстовым значениям
  • Фильтрация данных
  • Получение последней строки в диапазоне (вам это нужно чаще, чем вы думаете)

Я вернусь к этому сообщению и добавлю ссылки на эти сообщения по мере их создания.

Если вам понравился этот контент, поделитесь им и подпишитесь!

Вау, вы прочитали всю статью! Вы знаете, люди, которые заходят так далеко, являются настоящими учениками. И очевидно, вы цените обучение. Хотите узнать больше об Excel? Пожалуйста, поддержите меня, купив мне кофе (чтобы написать эти статьи, нужно много кофе!).

Читайте также: