Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, rw As Long, j As Long Dim Arr() As String Dim col As Collection
If Target.Cells.Count = 1 Then If Target.Column = 3 And Target.Value = "Склад" Then With ActiveSheet With .ListObjects("Таблица2").Sort.SortFields .Clear .Add Key:=Range("Таблица2[[#All],[Наименование]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal End With With .Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End With i = Target.Row Cells(i, 5).Validation.Delete Err.Clear On Error Resume Next rw = Columns(8).Find(what:=Cells(i, 2).Value).Row If Err.Number = 0 Then If Cells(rw, 8).Value = Cells(rw + 1, 8).Value Then Set col = New Collection Do col.Add Cells(rw, 10).Value rw = rw + 1 Loop While Cells(rw, 8).Value = Cells(rw - 1, 8).Value ReDim Arr(1 To col.Count) j = col.Count For j = 1 To col.Count Arr(j) = col(j) Next j Cells(i, 5).Validation.Add Type:=xlValidateList, Formula1:=Join(Arr, ",") Cells(i, 5).Select Else: Cells(i, 5).Value = Cells(rw, 10).Value End If End If Else If Target.Column = 3 And Target.Value = "Заказ" Then Cells(Target.Row, 5).Value = "-------------" End If End If
End Sub
[/vba]
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, rw As Long, j As Long Dim Arr() As String Dim col As Collection
If Target.Cells.Count = 1 Then If Target.Column = 3 And Target.Value = "Склад" Then With ActiveSheet With .ListObjects("Таблица2").Sort.SortFields .Clear .Add Key:=Range("Таблица2[[#All],[Наименование]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal End With With .Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End With i = Target.Row Cells(i, 5).Validation.Delete Err.Clear On Error Resume Next rw = Columns(8).Find(what:=Cells(i, 2).Value).Row If Err.Number = 0 Then If Cells(rw, 8).Value = Cells(rw + 1, 8).Value Then Set col = New Collection Do col.Add Cells(rw, 10).Value rw = rw + 1 Loop While Cells(rw, 8).Value = Cells(rw - 1, 8).Value ReDim Arr(1 To col.Count) j = col.Count For j = 1 To col.Count Arr(j) = col(j) Next j Cells(i, 5).Validation.Add Type:=xlValidateList, Formula1:=Join(Arr, ",") Cells(i, 5).Select Else: Cells(i, 5).Value = Cells(rw, 10).Value End If End If Else If Target.Column = 3 And Target.Value = "Заказ" Then Cells(Target.Row, 5).Value = "-------------" End If End If
StoTisteg, Простите, я не знал, как нужно идентифицировать ошибку. Если я выбираю "Заказ", то ячейка не чистится полностью. Там просто появляется "--------------", а "выбиратор" все еще сидит внутри. Это касается только тех ячеек где несколько поставщиков, например, "Вата (нест. 100 г.)"
StoTisteg, Простите, я не знал, как нужно идентифицировать ошибку. Если я выбираю "Заказ", то ячейка не чистится полностью. Там просто появляется "--------------", а "выбиратор" все еще сидит внутри. Это касается только тех ячеек где несколько поставщиков, например, "Вата (нест. 100 г.)"AVI
Сообщение отредактировал AVI - Среда, 13.06.2018, 11:30
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, rw As Long, j As Long Dim Arr() As String Dim col As Collection
If Target.Cells.Count = 1 Then If Target.Column = 3 And Target.Value = "Склад" Then With ActiveSheet With .ListObjects("Таблица2").Sort.SortFields .Clear .Add Key:=Range("Таблица2[[#All],[Наименование]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal End With With .Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End With i = Target.Row Cells(i, 5).Validation.Delete Err.Clear On Error Resume Next rw = Columns(8).Find(what:=Cells(i, 2).Value).Row If Err.Number = 0 Then If Cells(rw, 8).Value = Cells(rw + 1, 8).Value Then Set col = New Collection Do col.Add Cells(rw, 10).Value rw = rw + 1 Loop While Cells(rw, 8).Value = Cells(rw - 1, 8).Value ReDim Arr(1 To col.Count) j = col.Count For j = 1 To col.Count Arr(j) = col(j) Next j Cells(i, 5).Validation.Add Type:=xlValidateList, Formula1:=Join(Arr, ",") Cells(i, 5).Select Else: Cells(i, 5).Value = Cells(rw, 10).Value End If End If Else If Target.Column = 3 And Target.Value = "Заказ" Then With Cells(Target.Row, 5) .Value = "-------------" .Validation.Delete End With End If End If End If
End Sub
[/vba]
Да, забыл, прошу пардону. [vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, rw As Long, j As Long Dim Arr() As String Dim col As Collection
If Target.Cells.Count = 1 Then If Target.Column = 3 And Target.Value = "Склад" Then With ActiveSheet With .ListObjects("Таблица2").Sort.SortFields .Clear .Add Key:=Range("Таблица2[[#All],[Наименование]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal End With With .Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End With i = Target.Row Cells(i, 5).Validation.Delete Err.Clear On Error Resume Next rw = Columns(8).Find(what:=Cells(i, 2).Value).Row If Err.Number = 0 Then If Cells(rw, 8).Value = Cells(rw + 1, 8).Value Then Set col = New Collection Do col.Add Cells(rw, 10).Value rw = rw + 1 Loop While Cells(rw, 8).Value = Cells(rw - 1, 8).Value ReDim Arr(1 To col.Count) j = col.Count For j = 1 To col.Count Arr(j) = col(j) Next j Cells(i, 5).Validation.Add Type:=xlValidateList, Formula1:=Join(Arr, ",") Cells(i, 5).Select Else: Cells(i, 5).Value = Cells(rw, 10).Value End If End If Else If Target.Column = 3 And Target.Value = "Заказ" Then With Cells(Target.Row, 5) .Value = "-------------" .Validation.Delete End With End If End If End If
_Boroda_, Выбрал Ваш вариант, так как хоть как-то в нем могу разобраться при переносе в большой рабочий файл. Но получилось только в первой строке, яе понимаю как заставить массив работать для последующих строк. Выбор строки для работы массивной формулы происходит макросом? Подскажите, пожалуйста, как заставить работать массивную формулу при выборе данных в остальных строках
_Boroda_, Выбрал Ваш вариант, так как хоть как-то в нем могу разобраться при переносе в большой рабочий файл. Но получилось только в первой строке, яе понимаю как заставить массив работать для последующих строк. Выбор строки для работы массивной формулы происходит макросом? Подскажите, пожалуйста, как заставить работать массивную формулу при выборе данных в остальных строкахAVI
_Boroda_, Наткнулся на такую штуку. Макрос срабатывает, когда я выбираю ячейку, но если случайно выбрать уже заполненную, что оттуда все стирается. Можно предусмотреть, что бы, например, выбор поставщика (а равно как и срабатывание макроса) происходило только после того как я изменю ячейку «операции»? То есть макрос срабатывал при двух условиях: сначала должен быть Worksheet_Change поле «операции» , а потом Worksheet_SelectionChange соответствующей ячейки. Я понимаю какие команды нужно использовать, но не знаю как из связать…
_Boroda_, Наткнулся на такую штуку. Макрос срабатывает, когда я выбираю ячейку, но если случайно выбрать уже заполненную, что оттуда все стирается. Можно предусмотреть, что бы, например, выбор поставщика (а равно как и срабатывание макроса) происходило только после того как я изменю ячейку «операции»? То есть макрос срабатывал при двух условиях: сначала должен быть Worksheet_Change поле «операции» , а потом Worksheet_SelectionChange соответствующей ячейки. Я понимаю какие команды нужно использовать, но не знаю как из связать…AVI
сначала должен быть Worksheet_Change поле «операции» , а потом Worksheet_SelectionChange соответствующей ячейки
Ага, а если сначала изменили операцию, потом ткнулись еще в пару ячеек, а потом только в выбор поставщика? Как тогда? Не, можно конечно глобальную переменную сделать или на листе писать, но это не очень хорошо
сначала должен быть Worksheet_Change поле «операции» , а потом Worksheet_SelectionChange соответствующей ячейки
Ага, а если сначала изменили операцию, потом ткнулись еще в пару ячеек, а потом только в выбор поставщика? Как тогда? Не, можно конечно глобальную переменную сделать или на листе писать, но это не очень хорошо_Boroda_
Простите! Очень странно. На другом компьютере исчезало. Дома открываю все на месте...
И надеюсь, последний вопрос.
В столбец "P" выгружаются договоры с нулевым остатком. А можно сделать так что бы массив игнорировал такие, то есть отображал только те, где остаток больше нуля?
Простите! Очень странно. На другом компьютере исчезало. Дома открываю все на месте...
И надеюсь, последний вопрос.
В столбец "P" выгружаются договоры с нулевым остатком. А можно сделать так что бы массив игнорировал такие, то есть отображал только те, где остаток больше нуля?AVI
но если случайно выбрать уже заполненную, что оттуда все стирается
Попробовал с двух компьютеров. Все одинаково. Если в открывшемся файле просто стрелкой поднять выделение на заполненную ячейку, то данные оттуда стираются.
но если случайно выбрать уже заполненную, что оттуда все стирается
Попробовал с двух компьютеров. Все одинаково. Если в открывшемся файле просто стрелкой поднять выделение на заполненную ячейку, то данные оттуда стираются.AVI
=ЕСЛИ(Опер<>"Заказано у поставщика";;ЕСЛИОШИБКА(ИНДЕКС(Договоры_tb[Поставщик];НАИМЕНЬШИЙ(ЕСЛИ((Договоры_tb[Наименование]=Наим)*Договоры_tb[Остаток];СТРОКА(Договоры_tb[Наименование])-СТРОКА(Договоры_tb[[#Заголовки];[Наименование]]));СТРОКА()));))
Мне нужно добавить еще условие Помимо
Код
Опер<>"Заказано у поставщика"
должно быть еще
Код
Опер<>"Получено от поставщика"
Но если в обычное "Если" можно добавить "Или", то в массивную оно не получается…
_Boroda_, Да, что-то я совсем затупил...
Опять вопрос по формуле Массивная
Код
=ЕСЛИ(Опер<>"Заказано у поставщика";;ЕСЛИОШИБКА(ИНДЕКС(Договоры_tb[Поставщик];НАИМЕНЬШИЙ(ЕСЛИ((Договоры_tb[Наименование]=Наим)*Договоры_tb[Остаток];СТРОКА(Договоры_tb[Наименование])-СТРОКА(Договоры_tb[[#Заголовки];[Наименование]]));СТРОКА()));))
Мне нужно добавить еще условие Помимо
Код
Опер<>"Заказано у поставщика"
должно быть еще
Код
Опер<>"Получено от поставщика"
Но если в обычное "Если" можно добавить "Или", то в массивную оно не получается…AVI
Сообщение отредактировал AVI - Среда, 11.07.2018, 06:03
В данном случае (когда неравенство) нужно не ИЛИ, а И [vba]
Код
=ЕСЛИ((Опер<>"Заказано у поставщика")*(Опер<>"Получено от поставщика");;ЕСЛИОШИБКА(ИНДЕКС(Договоры_tb[Поставщик];НАИМЕНЬШИЙ(ЕСЛИ((Договоры_tb[Наименование]=Наим)*Договоры_tb[Остаток];СТРОКА(Договоры_tb[Наименование])-СТРОКА(Договоры_tb[[#Заголовки];[Наименование]]));СТРОКА()));
[/vba]))
В данном случае (когда неравенство) нужно не ИЛИ, а И [vba]
Код
=ЕСЛИ((Опер<>"Заказано у поставщика")*(Опер<>"Получено от поставщика");;ЕСЛИОШИБКА(ИНДЕКС(Договоры_tb[Поставщик];НАИМЕНЬШИЙ(ЕСЛИ((Договоры_tb[Наименование]=Наим)*Договоры_tb[Остаток];СТРОКА(Договоры_tb[Наименование])-СТРОКА(Договоры_tb[[#Заголовки];[Наименование]]));СТРОКА()));
_Boroda_, Блин, я почему-то подумал, что если эти условия перемножить, то формула будет искать нахождение и первого и второго условия одновременно и даже не стал пробовать, а надо было бы...
_Boroda_, Блин, я почему-то подумал, что если эти условия перемножить, то формула будет искать нахождение и первого и второго условия одновременно и даже не стал пробовать, а надо было бы...AVI