if not intersect target range

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

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

Для начала следует создать обыкновенный выпадающий список.

Для этого необходимо:

• Войти во вкладку «Данные»;
• Выбрать опцию «Проверка данных»;
• Выбрать «Список»;
• Указать диапазон, из которого будет выбираться выпадающий список или создать список прямо в появившемся поле через знак «;».

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

Для записи макроса следует:

• Открыть вкладку «Разработчик» ( Если вкладка отключена, включите ее в разделе Файл=> Параметры=> Настройка Ленты);

• Во вкладке «Разработчик» выбрать кнопку «Просмотр кода»;
• В открывшееся окно записать макрос;

• Закрыть окно с макросом.

Давайте рассмотрим несколько макросов с выпадающими списками.

Первый макрос со смещением списка в сторону (горизонтально).

Текст макроса:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«B2:B10»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(0, 1)) = 0 Then
Target.Offset(0, 1) = Target
Else
Target.End(xlToRight).Offset(0, 1) = Target
End If
Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Необходимо обратить внимание, что в строке :
If Not Intersect(Target, Range(«B1:B10»)) Is Nothing And Target.Cells.Count = 1 Then
Значения («B1:B10»)— это диапазон в пределах которого будет работать выпадающий список.
Аналогичным образом можно создать выпадающий список со смещением вниз и выпадающий список, записывающий в ячейку несколько значений через знак табуляции или пробел.

Макрос выпадающего списка со смещением вниз:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«C2:F2»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(1, 0)) = 0 Then
Target.Offset(1, 0) = Target
Else
Target.End(xlDown).Offset(1, 0) = Target
End If
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Макрос выпадающего списка с внесением нескольких значений в одну ячейку:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«B2:B5»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & «//» & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

В строке If Not Intersect(Target, Range(«B2:B5»)) Is Nothing And Target.Cells.Count = 1 Then
указывается диапазон действия макроса.
В строке
Target = Target & «//» & newVal
указывается разделитель «//». Его можно заменить на любой знак препинания, текст или поставить пробел.

Похожее:

1. Макрос определяющий пустая ли ячейка или заполненная в VBA ExcelМакрос проверки заполнения ячеек. Периодически при создании.
2. Функция VAL в VBA Excel или как преобразовать TextBox в число (цифру).Использования функции преобразования текста в число в.
3. Макрос для быстрой замены формул на значения (числа) в выделенных ячейках документа Excel.Когда удобно менять формулы на значения нажатием.

Макрос выпадающего списка с несколькими значениями в Excel: 2 комментария

Добрый день! Макрос выпадающего списка с внесением нескольких значений в одну ячейку почему то не работает. Нижеприведенные строки почему то становятся красным. Я так понимаю В2:В5 это диапазон который можно изменять на другую область например на F2:F200 допучстим? Или я не прав? Подскажите пожалуйста.

I am relatively new to VBA and I need help with this please.

I have a private sub within a sheet and I want it to autofill formulas adjacent to a dynamic named range, if the size of the range changes.

(edit) I am pasting data from another worksheet into this one columns A-M. My dynamic range is defined as =OFFSET($A$1,1,0,COUNTA($A:$A)-1,13). The first If statement should exit the sub if there is no data in column M and I had the destination calculating the last row of column M because I want to fill the formulas in N:O so that they cover the same number of rows as column M.

This is my code and it works if the size of the range gets smaller (i.e. if I delete rows from the bottom), but not if it gets bigger and I can’t work out why!

I put the last bit into a separate macro to test if it works on its own and for some reason, when I run it, the autofill goes all the way up to row 1 and overwrites the formulas, which is weird because I use that code a lot and it’s never done that before. What have I done.

Also, if there is a better way to do the autofill I’d appreciate if someone could let me know what it is because I just cobbled that together from bits I found on forums 🙂

Возвращает объект Range , представляющий прямоугольное пересечение двух или более диапазонов. Returns a Range object that represents the rectangular intersection of two or more ranges. Если указаны один или несколько диапазонов из другого листа, возвращается ошибка. If one or more ranges from a different worksheet are specified, an error is returned.

Синтаксис Syntax

выражение: переменная, представляющая объект Application. expression A variable that represents an Application object.

Параметры Parameters

Возвращаемое значение Return value

Пример Example

В примере ниже показано, как выбрать пересечение двух именованных диапазонов, Rg1 и RG2, на листе Sheet1. The following example selects the intersection of two named ranges, rg1 and rg2, on Sheet1. Если диапазоны не пересекаются, в примере отображается сообщение. If the ranges don’t intersect, the example displays a message.

В следующем примере сравнивается свойство листа. Range , метод Application. Union и метод Intersect . The following example compares the Worksheet.Range property, the Application.Union method, and the Intersect method.

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

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.