Сортировка списка при добавление новых значений

[dzv]

Здравствуйте, уважаемые Участники клуба!

Прошу помочь исправить ошибку в приведенном ниже макросе, он состоит из двух:
1. Добавление в список нового значения.
2. Сортировка списка.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$AC$7" Then
     If IsEmpty(Target) Then Exit Sub
       If WorksheetFunction.CountIf(Sheets("Формули").Range("Замовник"), Target) = 0 Then
          lReply = MsgBox("Додати Замовника: * " & _
                        Target & " * у випадний список?", vbYesNo + vbQuestion)
          If lReply = vbYes Then
              Sheets("Формули").Range("Замовник").Cells(Sheets("Формули").Range("Замовник").Rows.Count + 1, 1) = Target
          End If
       End If
     End If
ActiveWorkbook.Worksheets("Формули").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Формули").Sort.SortFields.Add Key:=Range("Замовник"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Формули").Sort
.SetRange Range("Замовник")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Макрос работает до "ActiveWorkbook.Worksheets("Формули" ).Sort.SortFields.Clear", тоесть добавляет значения в список. Но после этого момента и дальше, работать отказывается, тобишь сортировать.

Жду Ваши предложения и благодарю за помощь и время!

[Maxx]

Может так:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$AC$7" Then
     If IsEmpty(Target) Then Exit Sub
       If WorksheetFunction.CountIf(Sheets("Формули").Range("Замовник"), Target) = 0 Then
          lReply = MsgBox("Додати Замовника: * " & _
                        Target & " * у випадний список?", vbYesNo + vbQuestion)
          If lReply = vbYes Then
              Sheets("Формули").Range("Замовник").Cells(Sheets("Формули").Range("Замовник").Rows.Count + 1, 1) = Target
          End If
       End If
     End If
Selection.AutoFilter
ActiveWorkbook.Worksheets("Формули").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Формули").AutoFilter.Sort.SortFields.Add Key:=Range("Замовник"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Формули").AutoFilter.Sort
.SetRange Range("Замовник")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
End Sub

только перед выполнением фильтры должны быть сняты.

И не пойму зачем вот это .SetRange Range("Замовник"). Уберите.

[dzv]

Maxx, спасибо, но не помогло.

Давайте я уточню.

Макрос находится в "Лист1", список "Замовник" в "Лист2" .
"Лист2" это "Формули".

Да, фильтр у меня не стоит, грубо говоря, в столбце А идет нумерация с А1 по А10: 1,2,3,..,10.

[The_Prist]

Может так?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lReply As Long
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address <> "$AC$7" Then Exit Sub
    If IsEmpty(Target) Then Exit Sub
    With Sheets("Формули").Range("Замовник")
        If WorksheetFunction.CountIf(.Cells, Target) = 0 Then
            If MsgBox("Додати Замовника: * " & Target & " * у випадний список?", _
                      vbYesNo + vbQuestion) = vbYes Then _
               .Cells(.Row + .Rows.Count, 1) = Target
        End If
        .Sort .Cells(1), xlAscending
    End With
End Sub

[dzv]

The_Prist, супер, спасибо Вам!

Последнее, а если я вот так подправлю, это правильно будет (работает, но хотелось бы услышать Ваше мнение):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lReply As Long
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address <> "$AD$7" Then Exit Sub
    If IsEmpty(Target) Then Exit Sub
        With Sheets("Формули").Range("Замовник")
        If WorksheetFunction.CountIf(.Cells, Target) = 0 Then
            If MsgBox("Додати Замовника: * " & Target & " * у список?", _
                      vbYesNo + vbQuestion) = vbYes Then _
               .Cells(.Row + .Rows.Count, 1) = Target
        End If
        End With
    With Sheets("Формули").Range("Замовник")
        .Sort .Cells(1), xlAscending
    End With
End Sub

[The_Prist]

А зачем так? Ведь Вы просто дублируете конструкцию With ... End With. Это лишние строки кода. Я и сделал так именно потому, что работаете в коде Вы только с одним диапазоном. Принципиально Вы ничего не поменяли, просто задублировали пару строк, что привело к раздуванию кода без улучшения функциональности. Короче, лишнее это.

[dzv]

Сообщение от The_Prist

Короче, лишнее это.

Подождите немного

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

Возможно, что-то лишнее, я не спорю, потому что с Бейсиком не дружу, только учусь.

[The_Prist]

Сообщение от dzv

В вашем варианте, список сортируется при в воде нового значения, но при этом, само новое значение добавляется снизу

Возможно VBA не сразу обновляет данные о изменившейся размерности диапазона в конструкции With ... End With и поэтому Вы перепрописывая её как бы и переназначаете и обновляете данные о размерности диапазона.

[dzv]

Сообщение от The_Prist

Возможно VBA не сразу обновляет данные о изменившейся размерности диапазона в конструкции With ... End With и поэтому Вы перепрописывая её как бы и переназначаете и обновляете данные о размерности диапазона.

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

Забыл, у меня MO 2003.

[The_Prist]

Сообщение от dzv

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

Я это понял и написал причину. Я код не тестировал на данных. Я просто подправил Ваш.