Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Главное меню

Корректировка кода VBA (очистка ячеек по условию)

Автор Michael Holbrook, 20.05.2014, 12:49

« назад - далее »

GWolf

Доброго дня, Michael Holbrook!
Цитата: Michael Holbrook от 27.05.2014, 11:36
... VBA выделяет эту часть кода:

If [C5] <> test Then
Удивляться нечему! Дело в том что с одной стороны условия у Вас ячейка, по абсолютному ее адресу: [C5], и поскольку ячейка является контейнером, по сути своей, то и содержать может все что угодно и Вы ее не приводите к нужному виду, ну например так: Thisworkbooks.Cells(5,3).text. Почему .text? - Да потому что, другая сторона условия - переменная типа String, т.е. Строковая!
Вот за что я не люблю адресацию вида [CR]. Но, если уж Вам так нужно, то попробуйте сначала присвоить некоей переменной содержимое [C5], а уж затем сравнивайте эту переменную с test.

ЦитироватьСпасибо также за ваш вариант с удалением ячеек. Но мне он не подходит, потому что файл "Инвентаризационная" может за день довольно значительно поменять свое наполнение и расположение этой номенклатуры может постоянно прыгать по ячейкам. Именно поэтому поставили задачу автоматической очистки ячеек.
А вот тут я бы не торопился! Расскажите по-подробнее про: "может за день довольно значительно поменять свое наполнение и расположение этой номенклатуры может постоянно прыгать по ячейкам". Ведь, насколько я понимаю, Вы поменяв значение получаете мгновенное изменение, которое и влияет на решение: удалять или нет?! Ну та эта последовательность ЛИНЕЙНА и, происходит столько раз, сколько Вы будете менять значения. При чем тут "прыжки"? Или я чего то не понял?
Путей к вершине - множество. Этот один из многих!

Michael Holbrook

#16
GWolf, "Инвентаризационная" - экспортированный файл номенклатуры с 1С. Складовщик постоянно заносит новые данные или же удаляет. Плюс номенклатура у него сортируется по алфавиту. Следствием этих изменений может быть постоянное изменение адреса конкретной номенклатуры.

Во втором файле я забил формулы поиска по ВПР с первого файла номенклатурного кода и на основе его изымаются названия, кол-во и единицы измерения номенклатуры.
Данные вставляются во второй файл и в нем уже вручную в колонках D и E мы выбираем в какой группе должна быть конкретная номенклатура и к какому производителю относится. В колонке J выбирается расположение на складе. Спискам производителей и групп у меня присвоены свои коды, по которым они сортируются. (коды находятся в скрытых колонках F и G)

Сортируются по этому коду:

Private Sub Worksheet_Change(ByVal target As Range)
Dim rng As Range: Set rng = [C5:C3000]
    If Not Intersect(target, Range("C5:C3000")) Is Nothing Then Range("D5:E5,J5").ClearContents
    With ActiveWorkbook.Worksheets("Лист2").AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("F5:F" & Range("D" & Rows.Count).End(xlUp).Row), Order:=xlAscending
        .SortFields.Add Key:=Range("G5:G" & Range("E" & Rows.Count).End(xlUp).Row), Order:=xlAscending
        .Apply
        End With
End Sub


Проделав это все я понял, что при изменении базы первого главного файла данные заменяются во втором вполне неплохо. Но вот данные в колонках D, E, J остаются конечно же теми же, что и были, ибо они выбираются вручную из списков.

Вот я и хотел сделать так. Если меняется код конкретной номенклатуры - происходит очистка списков в колонках D, E и J в ее строке. После чего срабатывает сортировка.



Michael Holbrook

#17
Также не могу после кода очистки выполнить сортировку, которая ранее выглядела так и работала (вообще обидно, код ниже меня во всем удовлетворял бы и задача была бы полностью решена, если бы он реагировал на изменение значения в ячейке по формуле, а не на ручной ввод в ячейку):

Private Sub Worksheet_Change(ByVal target As Range)
Dim rng As Range: Set rng = [C5:C3000]
    If Not Intersect(target, Range("C5:C3000")) Is Nothing Then Range("D5:E5,J5").ClearContents
    With ActiveWorkbook.Worksheets("Лист2").AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("F5:F" & Range("D" & Rows.Count).End(xlUp).Row), Order:=xlAscending
        .SortFields.Add Key:=Range("G5:G" & Range("E" & Rows.Count).End(xlUp).Row), Order:=xlAscending
        .Apply
        End With
End Sub




Т.е. фактически после очистки, которая происходит, если меняется значение C5:

Private Sub Worksheet_Calculate()
Static OldVal1 As Variant, OldVal2 As Variant
If Range("C5").Value <> OldVal1 Then
    OldVal1 = Range("C5").Value
    Range("D5") = Range("A1")
    Range("E5") = Range("B1")
    Range("J5") = Range("C1")
End If
End Sub


Эта сортировка не выполняется:

Private Sub Worksheet_Change(ByVal target As Range)
Dim rng As Range: Set rng = [C5:C3000]
    If Not Intersect(target, Range("C5:C3000")) Is Nothing Then Range("D5:E5,J5").ClearContents
    With ActiveWorkbook.Worksheets("Лист2").AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("F5:F" & Range("D" & Rows.Count).End(xlUp).Row), Order:=xlAscending
        .SortFields.Add Key:=Range("G5:G" & Range("E" & Rows.Count).End(xlUp).Row), Order:=xlAscending
        .Apply
        End With
End Sub


Выделяется почему-то как ошибка эта строка и выскакивает Subscript out of range

With ActiveWorkbook.Worksheets("Лист2").AutoFilter.Sort

Michael Holbrook

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

Option Explicit

Private Sub Worksheet_Calculate()
  Dim r As Long
  Application.EnableEvents = False
  For r = 7 To 100
    If Cells(r, 3).Value <> ThreeC(r, 1) Then
      ThreeC(r, 1) = Cells(r, 3).Value: Cells(r, 12) = 0
      Cells(r, 5).Resize(1, 2).ClearContents: Cells(r, 10).ClearContents
    End If
  Next
  Application.EnableEvents = True
End Sub

GWolf

Цитата: Michael Holbrook от 13.06.2014, 13:08
... произошел пересчет и изменение значения ячейки.
какой ячейки?
Путей к вершине - множество. Этот один из многих!