Excel vba заменить содержимое конечных ячеек

Обновлено: 03.07.2024

Полагаю не совру когда скажу, что все кто программирует в VBA очень часто в своих кодах общаются к ячейкам листов. Ведь это чуть ли не основное предназначение VBA в Excel. В принципе ничего сложного в этом нет. Например, чтобы записать в ячейку A1 слово Привет необходимо выполнить код:

Тоже самое можно сделать сразу для нескольких ячеек:

Если необходимо обратиться к именованному диапазону:

Диапазон1 - это имя диапазона/ячейки, к которому надо обратиться в коде. Указывается в кавычках, как и адреса ячеек.
Но в VBA есть и альтернативный метод записи значений в ячейке - через объект Cells:

Cells(1, 1).Value = "Привет"

Синтаксис объекта Range:
Range(Cell1, Cell2)

Cell1 - первая ячейка диапазона. Может быть ссылкой на ячейку или диапазон ячеек, текстовым представлением адреса или имени диапазона/ячейки. Допускается указание несвязанных диапазонов(A1,B10), пересечений(A1 B10).
Cell2 - последняя ячейка диапазона. Необязательна к указанию. Допускается указание ссылки на ячейку, столбец или строку.

Синтаксис объекта Cells:
Cells(Rowindex, Columnindex)

Исходя из этого несложно предположить, что к диапазону можно обратиться, используя Cells и Range:

'выделяем диапазон "A1:B10" на активном листе Range(Cells(1,1), Cells(10,2)).Select

и для чего? Ведь можно гораздо короче:

Иногда обращение посредством Cells куда удобнее. Например для цикла по столбцам(да еще и с шагом 3) совершенно неудобно было бы использовать буквенное обозначение столбцов.
Объект Cells так же можно использовать для указания ячеек внутри непосредственно указанного диапазона. Например, Вам необходимо выделить ячейку в 3 строке и 2 столбце диапазона "D5:F56" . Можно пройтись по листу и посмотреть, отсчитать нужное количество строк и столбцов и понять, что это будет "E7" . А можно сделать проще:

Согласитесь, это гораздо удобнее, чем отсчитывать каждый раз. Особенно, если придется оперировать смещением не на 2-3 ячейки, а на 20 и более. Конечно, можно было бы применить Offset. Но данное свойство именно смещает диапазон на указанное количество строк и столбцов и придется уменьшать на 1 смещение каждого параметра для получения нужной ячейки. Да и смещает на указанное количество строк и столбцов весь диапазон, а не одну ячейку. Это, конечно, тоже не проблема - можно вдобавок к этому использовать метод Resize - но запись получится несколько длиннее и менее наглядной:

Range("D5:F56").Offset(2, 1).Resize(1, 1).Select

И неплохо бы теперь понять, как значение диапазона присвоить переменной. Для начала переменная должна быть объявлена с типом Range. А т.к. Range относится к глобальному типу Object, то присвоение значения такой переменной должно быть обязательно с применением оператора Set:

Dim rR as Range Set rR = Range("D5")

если оператор Set не применять, то в лучшем случае получите ошибку, а в худшем(он возможен, если переменной rR не назначать тип) переменной будет назначено значение Null или значение ячейки по умолчанию. Почему это хуже? Потому что в таком случае код продолжит выполняться, но логика кода будет неверной, т.к. эта самая переменная будет содержать значение неверного типа и применение её в коде в дальнейшем все равно приведет к ошибке. Только ошибку эту отловить будет уже сложнее.
Использовать же такую переменную в дальнейшем можно так же, как и прямое обращение к диапазону:

Вроде бы на этом можно было завершить, но. Это как раз только начало. То, что я написал выше знает практически каждый, кто пишет в VBA. Основной же целью этой статьи было пояснить некоторые нюансы обращения к диапазонам. Итак, поехали.

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

'так выглядит запись слова Test в ячейку А1 Range("A1").Select Selection.Value = "Test"

Но как правило выделение - действие лишнее. Можно записать значение и без него:

'запишем слово Test в ячейку A1 на активном листе Range("A1").Value = "Test"

Теперь чуть подробнее разберем, как обратиться к диапазону не выделяя его и при этом сделать все правильно. Диапазон и ячейка - это объекты листа. У каждого объекта есть родитель - грубо говоря это другой объект, который является управляющим для дочернего объекта. Для ячейки родительский объект - Лист, для Листа - Книга, для Книги - Приложение Excel. Если смотреть на иерархию зависимости объектов, то от старшего к младшему получится так:
Applicaton => Workbooks => Sheets => Range
По умолчанию для всех диапазонов и ячеек родительским объектом является текущий(активный) лист. Т.е. если для диапазона(ячейки) не указать явно лист, к которому он относится, в качестве родительского листа для него будет использован текущий - ActiveSheet:

'запишем слово Test в ячейку A1 на активном листе Range("A1").Value = "Test"

Т.е. если в данный момент активен Лист1 - то слово Test будет записано в ячейку А1 Лист1. Если активен Лист3 - в А1 Лист3. Иначе говоря такая запись равносильна записи:

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

'активируем Лист2 Worksheets("Лист2").Select 'записываем слово Test в ячейку A1 Range("A1").Value = "Test"

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

'запишем слово Test в ячейку A1 на Лист2 независимо от того, какой лист активен Worksheets("Лист2").Range("A1").Value = "Test"

Таким же образом происходит считывание данных с ячеек - если не указывать лист, данные ячеек которого необходимо считать - считаны будут данные с ячейки активного листа. Чтобы считать данные с Лист2 независимо от того, какой лист активен применяется такой код:

'считываем значение ячейки A1 с Лист2 независимо от того, какой лист активен MsgBox Worksheets("Лист2").Range("A1").Value

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

'запишем слово Test в ячейку A1 на Лист2 книги Книга2.xlsx независимо от того, какая книга и какой лист активен Workbooks("Книга2.xlsx").Worksheets("Лист2").Range("A1").Value = "Test" 'считываем значение ячейки A1 с Лист2 книги Книга3.xlsx независимо от того, какой лист активен MsgBox Workbooks("Книга3.xlsx").Worksheets("Лист2").Range("A1").Value

Очень часто ошибки обращения к ячейкам листов и книг делают начинающие, особенно в циклах по листам. Вот пример неправильного цикла:

Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets Range("A1").Value = wsSh.Name 'записываем в ячейку А1 имя листа MsgBox Range("A1").Value 'проверяем, то ли имя записалось Next wsSh

MsgBox будет выдавать правильные значения, но сами имена листов будут записываться не на каждый лист, а последовательно в ячейку активного листа. Поэтому на активном листе в ячейке А1 будет имя последнего листа.
А вот так выглядит правильный цикл:
Вариант 1 - активация листа (медленный)

Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets wsSh.Activate 'активируем каждый лист Range("A1").Value = wsSh.Name 'записываем в ячейку А1 имя листа MsgBox Range("A1").Value 'проверяем, то ли имя записалось Next wsSh

Вариант 2 - без активации листа (быстрый и более правильный)

Dim wsSh As Worksheet For Each wsSh In ActiveWorkbook.Worksheets wsSh.Range("A1").Value = wsSh.Name 'записываем в ячейку А1 имя листа MsgBox wsSh.Range("A1").Value 'проверяем, то ли имя записалось Next wsSh

Важно: если код записан в модуле листа(правая кнопка мыши на листе-Исходный текст) и для объекта Range или Cells родитель явно не указан(т.е. нет имени листа и книги) - тогда в качестве родителя будет использован именно тот лист, в котором записан код, независимо от того какой лист активный. Иными словами - если в модуле листа записать обращение вроде Range("A1").Value = "привет" , то слово привет всегда будет записывать в ячейку A1 именно того листа, в котором записан сам код. Это следует учитывать, когда располагаете свои коды внутри модулей листов.

В конструкциях типа Range(Cells(,),Cells(,)) Range является контейнером, в котором указываются ссылки на объекты, из которых и будет создана ссылка на непосредственно конечный объект.
Предположим, что активен "Лист1" , а код запущен с листа "Итог" .
Если запись будет вида

Sheets("Итог").Range(Cells(1, 1), Cells(10, 1))

это вызовет ошибку "Run-time error '1004': Application-defined or object-defined error". А ошибка появляется потому, что контейнер и объекты внутри него не могут располагаться на разных листах, равно как и:

Sheets("Итог").Range(Cells(1, 1), Sheets("Итог").Cells(10, 1)) 'запись ниже так же неверна Range(Cells(1, 1), Sheets("Итог").Cells(10, 1))

т.к. ссылки на объекты внутри контейнера относятся к разным листам. Cells(1, 1) - к активному листу, а Sheets( "Итог" ).Cells(10, 1) - к листу Итог.
А вот такие записи будут правильными:

Sheets("Итог").Range(Sheets("Итог").Cells(1, 1), Sheets("Итог").Cells(10, 1)) Range(Sheets("Итог").Cells(1, 1), Sheets("Итог").Cells(10, 1))

Вторая запись не содержит ссылки на родителя для Range, но ошибки это в большинстве случаев не вызовет - т.к. если для контейнера ссылка не указана, а для двух объектов внутри контейнера родитель один - он будет применен и для самого контейнера. Однако лучше делать как в первой строке - т.е. с обязательным указанием родителя для контейнера и для его составляющих. Т.к. при определенных обстоятельствах(например, если в момент обращения к диапазону активной является книга, открытая в режиме защищенного просмотра) обращение к Range без родителя может вызывать ошибку выполнения.
Если запись будет вида Range( "A1" , "A10" ), то указывать ссылку на родителя внутри Range не обязательно - достаточно будет указать эту ссылку перед самим Range - Sheets( "Итог" ).Range( "A1" , "A10" ), т.к. текстовое представление адреса внутри Range не является объектом(у которого может быть какой-то родительский объект), что обязывает создать ссылку именно на родителя контейнера.

Разберем пример, приближенный к жизненной ситуации. Необходимо на лист Итог занести формулу вычитания, начиная с ячейки А2 и до последней заполненной. На момент записи активен Лист1. Очень часто начинающие записывают так:

Sheets("Итог").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) _ .FormulaR1C1 = "=RC2-RC11"

Запись смешанная - и текстовое представление адреса ячейки( "A2:A" ) и ссылка на объект Cells. В данном случае явную ошибку код не вызовет, но и работать будет не всегда так, как хотелось бы. А это самое плохое, что может случиться при разработке.
Sheets("Итог").Range("A2:A" - создается ссылка на столбец " A " листа Итог. Но далее идет вычисление последней строки первого столбца. И вот как раз это вычисление происходит на основе объекта Cells, который не содержит в себе ссылки на родительский объект. А значит он будет вычислять последнюю строку исключительно для текущего листа(если код записан в стандартном модуле, а не модуле листа) - т.е. для Лист1. Правильно было бы записать так:

Sheets("Итог").Range("A2:A" & Sheets("Итог").Cells(Rows.Count, 1).End(xlUp).Row) _ .FormulaR1C1 = "=RC2-RC11"

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

lLastRow = Workbooks("Книга3.xls").Sheets("Лист1").Cells(Rows.Count, 1).End(xlUp).Row

с виду все нормально, но есть нюанс. Rows.Count по умолчанию будет относится к активной книге, если записано в стандартном модуле. Приведенный выше код должен работать с книгой формата 97-2003 и вычислить последнюю заполненную ячейку на листе1. В книгах формата Excel 97-2003(.xls) всего 65536 строк. Если в момент выполнения приведенной строки активна книга формата 2007 и выше(форматы .xlsx, .xlsm, .xlsb и пр) - то Rows.Count вернет 1048576, т.к. именно такое количество строк в листах книг версий Excel, начиная с 2007. И т.к. в книге, в которой мы пытаемся вычислить последнюю строку всего 65536 строк - получим ошибку 1004, т.к. не может быть номера строки 1048576 на листе с количеством строк 65536. Поэтому имеет смысл указывать явно откуда считывать Rows.Count:

lLastRow = Workbooks("Книга3.xls").Sheets("Лист1").Cells(Workbooks("Книга3.xls").Sheets("Лист1").Rows.Count, 1).End(xlUp).Row

или применить конструкцию With

With Workbooks("Книга3.xls").Sheets("Лист1") lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row End With

Также не мешало бы упомянуть возможность выделения несмежного диапазона(часто его называют "рваным"). Это диапазон, который обычно привыкли выделять на листе при помощи зажатой клавиши Ctrl. Что это дает? Это дает возможность выделить одновременно ячейки A1 и B10 и записать значения только в них. Для этого есть несколько способов. Самый очевидный и описанный в справке - метод Union:

Однако существует и другой метод:

В чем отличие(я бы даже сказал преимущество) Union: можно применять в цикле по условию. Например, выделить в диапазоне A1:F50 только те ячейки, значение которых больше 10 и меньше 20:

Sub SelOne() Dim rCell As Range, rSel As Range For Each rCell In Range("A1:F50") If rCell.Value > 10 And rCell.Value < 20 Then If rSel Is Nothing Then Set rSel = rCell Else Set rSel = Union(rSel, rCell) End If End If Next rCell If Not rSel Is Nothing Then rSel.Select End Sub

Конечно, можно и просто в Range через запятую передать все эти ячейки, сформировав предварительно строку. Но в случае со строкой действует ограничение: длина строки не должна превышать 255 символов.

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

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

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

Добрый день всем! В очередной раз за помощью! Простой макрос по кнопке вставляет дату + N дней. Но часто полученная дата попадает на выходной. Что нужно дописать,или изменить, что бы полученная дата "проскакивала" вперед на ближайший рабочий день?

Sub Через_10д()
If Not Intersect(ActiveCell, Range("J14:M350")) Is Nothing Then ActiveCell = Date + 10
End Sub

1. Что бы записать "привет" в ячейку А1 не надо объявлять переменную как диапазон и присваивать ей значение из ячейки D5. Самое начало статьи сразу с ошибки начинаете.
2. Диапазон - это диапазон, я не ячейка с номером типа Cell(х,y). Если уж начали писать про диапазоны - пишите про диапазоны! У меня задача - обратиться к именованному диапазону и вытащить оттуда через ВБА значения в массив для дальнейшей обработки. А вот теперь посмотрите что из написанного может помочь это сделать. Ничего.
Спасибо, бесполезная статья с намеком на гениальность и уводящая постоянно в сторону от озвученной темы

Артем,
1. Спасибо. Вообще в самом начале статьи написан другой код:

Range("A1").Value = "Привет"
Тоже самое можно сделать сразу для нескольких ячеек:
Range("A1:C10").Value = "Привет"

Просто не так давно сменил скрипт подсветки синтаксиса кода и он почему-то отобразил дважды один и тот же код из этой статьи, вместо других. В общем поправил.
2. Прежде чем критиковать, изучите хотя бы мат.часть. Ячейка - это тот же диапазон технически. И обращение Range("A1") равносильно обращению Cells(1,1). И свойства и методы у них идентичны. Только Range может содержать более одной ячейки, Cells нет.
Цель данной статьи - научить правильно обращаться к диапазонам и понимать отличие обращения к диапазону в активном листе или в других листах и книгах. И научить обращаться как через Range, так и через Cells. И никаких намеков на гениальность, просто описание технической части и её нюансов. Впрочем, об этом я тоже упоминаю:

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

И в сторону статья уводит лишь от ВАШЕЙ проблемы, но не от темы. Ваша проблема - это работа не столько с диапазонами, сколько с массивами. Различайте понятия - вся статья про обращение к диапазонам, но описать в одной статье ответы на вопросы по диапазонам каждого проходящего нереально. Ведь само обращение к диапазону может использоваться в любой задаче: отобрать уникальные, загнать в массив, покрасить, подсчитать ячейки с тем-то и тем-то и т.д. и т.п. И все это будет подходить под определение диапазона - ведь мы к нему обращаемся :) Поэтому вменяемые люди пишут комментарии со своим вопросом, а не жалуются, что именно их вопрос здесь не озвучен.
А тем не менее Ваша задача решается элементарно и без всякого выпендрежа вроде "статья бесполезна". Надо было чуть пошерстить хотя бы этот же сайт - я приводил и такие примеры. Хотя бы в этой статье: Как ускорить и оптимизировать код VBA . Там есть советы по оптимизации для чуть более опытных, в том числе использование массивов, вместо прямого обращения к ячейкам.
И решается Ваша задача одной строкой кода:

Dim arr 'объявляем переменную, в которую помещать массив arr = Range("A1:F10").Value 'дальше делаем с массивом все, что надо

Есть еще один забавный способ адресоваться в VBA к ячейке.
[a2] = "Привет!" ' Занесет в ячейку А2 текущей книги текущего листа "Привет!"
Работает при способе адресации А1. При адресации RC будет работать такой способ:
[r1c2] = "Привет!"

Подскажите, пожалуйста, можно ли так же обращаться к динамическим диапазонам, нижняя граница которых записана через ИНДЕКС(ПОИСКПОЗ. У меня всякие ошибки вылезают при попытке обращения к такому диапазону.

Матвей, можно. Но не зная как и что Вы там задаете и какие лезут ошибки помочь нечем :( Попробуйте создать тему на форуме с приложенным файлом и подробным описанием проблемы.

Подскажите пожалуйста, у меня есть файл. на листе 1 есть список элементов в столбце B, на листе 2 в столбце B тот же список и в столбце С краткие наименования этих элементов. Мне необходимо создать скрипт, который просматривает столбец B в листе 1 файла Excel, ищет соответствие в столбце B листа 2 и заменяет это значение в листе 1 на соответствующее из столбца С листа 2. Я впервые работаю с VBA. Что мне необходимо сделать?

Спасибо большое за любую помощь!

Добавлено через 3 часа 17 минут
Начала писать скрипт, есть вопросы:

Не могу разобраться как правильно записать "конец строк" и "условие, которое сравнивает Лист1.Cells(i, 1).Value = Лист2.Cells(j, 1).Value" __________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Макрос, который увеличивает значение ячейки А на 1 при изменении ячейки В
Добрый день. Я написал макрос, который увеличивает значение ячейки А на 1 при изменении ячейки.

Как оптимизировать скрипт, который меняет ширину блока
Вот скрипт, который меняет ширину блока. Есть две ссылки, которые выполняют по клику функции.

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

Скрипт меняет значение поля только с задержкой
Есть скрипт который должен после того как динамически сформирует поле input сразу менять его.

(if a = b) - это и есть условие. В Вашем случае, "если ячейка такая-то равна ячейке такой-то . ".
"Конец строк" можно ловить так:
На активном листе ищется последняя заполненная ячейка в столбце "B".

Спасибо за помощь!

Dim j As Integer
Dim i As Integer
For i = 1 To 125

For j = 1 To 2301

If Лист1.Cells(i, 2).Value = Лист2.Cells(j, 3).Value Then

Лист1.Cells(i, 2).Value = Лист2.Cells(j, 6).Value

Попробовала данный код на приложенном файле . Почему-то не заменились ячейки - не понятно почему (

Попробуйте вручную. Например, Лист1, строка 3, столбец 2 = "Предоставление сведений об административных правонарушениях в области дорожного движения". Ищем это значение в Листе 2, столбец 3. На строке 267 Бинго! Смотрим в столбец 6 Листа 2, а он брат близнец 3-му столбцу. Т.е. учитывая тот факт, что значения идентичны, то и замены не видно. Это если смотреть на Ваш код. А если прочесть первый пост, то код должен выглядеть так:

На листе 2 в столбце 3 есть короткие названия (меньше 255) - они заменяются сами на себя, поэтому идентичны.
А есть больше 255 - они заменяются на другие. Поэтому "вручной метод" сработал на идентичный вариант.

Добавлено через 8 минут
Попробуйте даже заменить 6 столбец на другой например на 4 - там цифры. Все равно ничего не происходит

Serge_007 Вот что значит шаблонность мышления
Таким способом обычно заменяют ссылки на другие книги в формулах, в таком случае без ё не обойтись
Для данной задачи необходимости в лишнем действии конечно нет Вот что значит шаблонность мышления
Таким способом обычно заменяют ссылки на другие книги в формулах, в таком случае без ё не обойтись
Для данной задачи необходимости в лишнем действии конечно нет Serge_007

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

Нужно вот это
=ЕСЛИ(Z8<ДВССЫЛ(СЦЕПИТЬ(G3;"P";W8+15));"";ДВССЫЛ(СЦЕПИТЬ(G3;"AV";(W8+15)))))
Заменить на это
=ЕСЛИ(СЦЕПИТЬ(G3;"P";W8+15)>J3;"";ЕСЛИ(Z8<ДВССЫЛ(СЦЕПИТЬ(G3;"P";W8+15));"";ДВССЫЛ(СЦЕПИТЬ(G3;"AV";(W8+15)))))

Число после W8+ всегда увеличивается на 1

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

Число после W8+ всегда увеличивается на 1 Maximan

Число после W8+ всегда увеличивается на 1 Автор - Maximan
Дата добавления - 06.12.2012 в 22:25

Само увеличивается? Автор - AlexM
Дата добавления - 06.12.2012 в 22:30

Ячейки а1 а2 уже с исправлениями, в остальные ниже "а" нужно внести изменения.

К сожалению, всю программу выложить не могу - она на взаимосвязях с другими файлами.

Ячейки а1 а2 уже с исправлениями, в остальные ниже "а" нужно внести изменения.

К сожалению, всю программу выложить не могу - она на взаимосвязях с другими файлами. Maximan

К сожалению, всю программу выложить не могу - она на взаимосвязях с другими файлами. Автор - Maximan
Дата добавления - 06.12.2012 в 22:54

А тут без "ё" не получается
Замена 1
= заменяем на ёЕСЛИ(СЦЕПИТЬ(G3;"P";W8+15)>J3;"";
Замена 2
)))) меняем на )))))
Замена 3
ё меняем на =

PS. А про ДВССЫЛ в добавлении СЦЕПИТЬ(G3;"P";W8+15) забыли?

PS. А про ДВССЫЛ в добавлении СЦЕПИТЬ(G3;"P";W8+15) забыли? AlexM

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

PS. А про ДВССЫЛ в добавлении СЦЕПИТЬ(G3;"P";W8+15) забыли? Автор - AlexM
Дата добавления - 06.12.2012 в 22:57

Я наверное слишком глупый, не обессудьте )
Не понял что именно меняем во второй замене?

PS Точно - забыл! Спасибо!

Я наверное слишком глупый, не обессудьте )
Не понял что именно меняем во второй замене?

PS Точно - забыл! Спасибо! Maximan

PS Точно - забыл! Спасибо! Автор - Maximan
Дата добавления - 07.12.2012 в 00:13

Нужно взять карандашик и потыкать в скобочки. Автор - RAN
Дата добавления - 07.12.2012 в 00:18

Еще подозрение на ошибку
1. Проверьте условия. Что-то там неправильно.
2. ДВССЫЛ(СЦЕПИТЬ(G3;"AV";(W8+15))) лишние скобки надо так ДВССЫЛ(СЦЕПИТЬ(G3;"AV";W8+15))

Во второй замене меняем 4 скобки на 5 скобок. Хотя если убирать ошибку 2, то во второй замене меняем (W8 на W8

Во второй замене меняем 4 скобки на 5 скобок. Хотя если убирать ошибку 2, то во второй замене меняем (W8 на W8 AlexM

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Во второй замене меняем 4 скобки на 5 скобок. Хотя если убирать ошибку 2, то во второй замене меняем (W8 на W8 Автор - AlexM
Дата добавления - 07.12.2012 в 00:19

И еще при всех заменах все равно руками придется менять число после W8, а чтобы менялось автоматически при протягивании формулы нужны еще замены.
Т.е. формула переделывается полностью, а если с условиями разобраться, то формулу не узнать.
Может ее заново написать и тянуть? И еще при всех заменах все равно руками придется менять число после W8, а чтобы менялось автоматически при протягивании формулы нужны еще замены.
Т.е. формула переделывается полностью, а если с условиями разобраться, то формулу не узнать.
Может ее заново написать и тянуть? AlexM Изменил условия, если неправильно, то сделайте как в вашем файле.
Сейчас значение условия дает ИСТИНУ если значение ДВССЫЛ(СЦЕПИТЬ($G$3;"P";$W$8+15 между J3 И Z8, т.е больше Z8 и меньше J3

См. файл в отдельном столбце показано как происходит увеличение на единицу и как меняется имя столбца Изменил условия, если неправильно, то сделайте как в вашем файле.
Сейчас значение условия дает ИСТИНУ если значение ДВССЫЛ(СЦЕПИТЬ($G$3;"P";$W$8+15 между J3 И Z8, т.е больше Z8 и меньше J3

См. файл в отдельном столбце показано как происходит увеличение на единицу и как меняется имя столбца AlexM

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

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

Пример 1
Эта функция заменяет значение ячейки Cell1 на значение ячейки Cell2 увеличенное на 100. Сама функция размещается в третьей ячейке, чтобы не возникла циклическая ссылка.

В этом примере мы не присваиваем пользовательской функции значение, поэтому отображается значение по умолчанию – 0. Если объявить эту функцию как строковую: Function Primer1(Cell1 As Range, Cell2 As Range) as String , будет возвращена пустая строка.

Изменение значения ячейки C1 (Cell2) приведет к пересчету значения ячейки B1 (Cell1).

Попробуйте очистить или перезаписать ячейку B1 (Cell1), ничего не получится, так как функция Primer1 вновь перезапишет ее значением C1 (Cell2) + 100.

Метод Application.Volatile

Application.Volatile – это метод, который запускает пересчет функции при изменении значения любой ячейки рабочего листа, а не только той, которая присвоена объявленной в функции переменной. Метод Application.Volatile используется только в функциях.

Рассмотрим пересчет функции на следующем примере:

Пример 2

Function Primer2 ( Cell1 As Range , Cell2 As Range ) As String

Эта функция будет пересчитываться только при изменении значений ячеек B1 и C1, присвоенных переменным Cell1 и Cell2. При изменении значения ячейки C2, значение ячейки B2 не изменится, так как не будет запущен пересчет функции Primer2.

Функция Primer2 начнет вести себя по-другому, если добавить в нее оператор Application.Volatile (переименуем ее в Primer3):

Пример 3

Function Primer3 ( Cell1 As Range , Cell2 As Range ) As String

Теперь при смене значения в ячейке C2, значение ячейки B2 тоже изменится.

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

Безопасное использование функции

Если вам все-таки хочется в важном проекте VBA Excel с помощью одной пользовательской функции изменять значения нескольких ячеек, есть полностью безопасный вариант.

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

В простых случаях для выбора можно использовать функцию Choose или, в более сложных, оператор If…Then…Else и оператор Select Case.

Пример 4
Используем функцию Choose для выбора способа вычисления пользовательской функции в зависимости от значения дополнительного аргумента:

Function Primer4 ( Cell1 As Range , Cell2 As Range , a As Byte ) Primer4 = Choose ( a , Cell1 + Cell2 , Cell1 - Cell2 , Cell1 * Cell2 )

В функцию Primer4 добавлен дополнительный аргумент a, от которого зависит, какое действие будет произведено со значениями ячеек B1 и C1:

На следующем скриншоте представлены результаты вычисления функции в зависимости от значения аргумента a:

В ячейке A1 вычисляется сумма значений ячеек B1 и C1 – аргумент a=1.
В ячейке A2 вычисляется разность значений ячеек B2 и C2 – аргумент a=2.
В ячейке A3 вычисляется произведение значений ячеек B3 и C3 – аргумент a=3.

Пример 5
Используем оператор If…Then…Else в сокращенном виде (If…Then…) для выбора способа вычисления функции в зависимости от значения дополнительного аргумента:

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