Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: Dmitriy_10 от 11.09.2017, 22:30

Название: Поиск меньшего. Два критерия поиска. Копирование найденного диапазона.
Отправлено: Dmitriy_10 от 11.09.2017, 22:30
Здравствуйте,

нужен совет/помощь в написании кода.

В чем проблема:
1) если я ищу с помощью "Find" и беру значение из определенных ячеек, как мне искать значение меньшее этого (того, что в ячейке).

  Windows("Ex.xlsm").Activate
    x = Application.Cells(20, 1).Value
    y = Application.Cells(20, 2).Value
    Windows("3.xls").Activate
      Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
       xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
       False, SearchFormat:=False).Select

Как мне искать "<x" ? Если я так и пропишу, то выдаст ошибку..
2) Мне нужно найти значение меньшее "Х" при этом и на "уровень" ниже чем "у". (
Cells.Find(What:=<x And <y, After  ?????
3) Как взять "х" и "у" из строки ниже? т.е
  x = Application.Cells(21, 1).Value
           y = Application.Cells(21, 2).Value


Задать какую-то переменную вместо строки? скажем а = а+1?

Зачем вообще все это:

- есть 3 эксель документа "Ех", "1" и "3". В первый док мы всё тянем из двух других..
Из первого документа  в "Ех" кнопки "Step+"  и  "Step-" тянут диапазон от "А до D" и копируют его в документ "Ех".

-В этом диапазоне есть "С" номмер. В примере в G5 он C160194. Зная его в док "3" кнопка "CNummer" фильтром находит все "С" номера и копирует в табличку "Ех".

-"С" номера имеют "ряд/row" и "уровень/level"  например "C160194" на уровне "3" и ряд "918". После того как нашли "С" номмер на уровне 3, нужно спуститься на ряд 2 и найти первое значение меньше чем "918".
И до 1го уровня и для каждого из найденных "С" номеров.. их может быть от 1 и до...

В примере красным выделил найденные "С" номера. Ниже они же синим и для одного из них я нашел все до 1го уровня.
для других поставил "х"..

Можно все это делать разными способами... мне без разницы каким.. фильтр, проставлять что-то в столбце "А" и затем фильтровать и копировать все найденные строки, или красить в какой-то цвет..

Мне кажется что проще было бы находить "С" номер  помощью "Find" и сразу же для него идти по рядам и уровням до уровня 1.. будет 2 цикла, верно? или как? Что скажете?
Название: Re: Поиск меньшего. Два критерия поиска. Копир
Отправлено: kuklp от 12.09.2017, 08:37
Dmitriy_10, Вы так можете долго прождать помощи. Зачем Вы выложили свои рабочие файлы? Кому интересно рыться в десятке модулей, вникая - что надо сделать? Выложите ОДИН файл-пример с парой-тройкой листов. В них только то, что касается вопроса. И пожалуйста, без вырвиглазных раскрасок.
Название: Re: Поиск меньшего. Два критерия поиска. Копирование найденного диапазона.
Отправлено: Dmitriy_10 от 12.09.2017, 17:04
Прошу прощения.

думал кому-то может быть полезен код..
Название: Re: Поиск меньшего. Два критерия поиска. Копир
Отправлено: kuklp от 13.09.2017, 10:23
Dmitriy_10, я из описания мало что понял. Не рассказывайте нам историю от сотворения мира. Только то, что касается вопроса. Поехали - мы ищем на листе 3:
1) категорически не советую называть макросы\модули зарезервированными словами(Find).
2) Cells.Find(What:=x, ..LookAt:= xlPart,.. ищет на всем листе по части ячейки - это бессмысленно. Первое что нашел Ваш код: F25918. Как эта строка может быть меньше 918? Поэтому уточните, в каком столбце мы ищем это 918. И дальше с каким столбцом сравниваем и что ищем. Метод find тут  подойдет только если столбец поиска всегда будет отсортирован.
Название: Re: Поиск меньшего. Два критерия поиска. Копир
Отправлено: kuklp от 13.09.2017, 10:42
Смотрите, я предположил, что искать надо в столбце В и он отсортирован по возрастанию:
Sub FindRow()
    Dim x, y
    Dim c As Range, lr As Long
    x = Sheets("Ex").Range("A3").Value
'    y = Sheets("Ex").Range("B3").Value Зачем это?
    With Sheets("3")
        lr = .Range("B" & .Rows.Count).End(xlUp).Row
        Set c = .Range("B2:b" & lr).Find(x, , , xlPart, SearchFormat:=False)
        If Not c Is Nothing Then _
           c.Offset(-1).Resize(, 4).Copy Sheets("Ex").Range("A20")
    End With
End Sub


Название: Re: Поиск меньшего. Два критерия поиска. Копирование найденного диапазона.
Отправлено: Dmitriy_10 от 13.09.2017, 11:14
"Y" - это Level.  Нашли "C" номер (Например C160194) который имеет ряд 918 (А3) и Level 3 (B3).. в этом случае нужно спуститься на Level 2 (всегда на уровень ниже) и найти первое "ближайшее/меньшее" число от А3.. т.е.  как в примере это будет 905 на уровне 2. (Диапазон А4:D4) в примере и теперь тоже самое для уже нового найденого диапазона (Диапазон А4:D4) идем на уровень ниже - будет 1 и ищем первое ближайшее/меньшее к 905 - будет 3.. (Диапазон А5:D5 в примере).
Поэтому и нужен "Y". Это второй критерий поиска.

После все это нужно проведать для следующего найденного "С" номера.. (Диапазон А6:D6 в примере) и так для всех "С номеров.. спускаемся на Уровень 1.. (уровней всего 7. можем найти на нём 40 "С" номеров и от каждого нужно будет пройти с уровня 7 до уровня 1.
Название: Re: Поиск меньшего. Два критерия поиска. Копир
Отправлено: kuklp от 13.09.2017, 14:23
Ничего не понял из этой тарабарщины. Китайский язык.. 78 просмотров на текущий момент и никто Вас не понимает. ""Y" - это Level. Нашли "C" номер (Например C160194) который имеет ряд 918 (А3) и Level 3 (B3)..." Это Вы с кем разговаривали? ;D Это в Вашей голове какие-то левелы-ряды-с\значения. В эксе есть листы, строки-столбцы, ячейки, значения. Заголовки у них могут быть какие угодно.
Цитата: kuklp от 13.09.2017, 10:23уточните, в каком столбце мы ищем это 918. И дальше с каким столбцом сравниваем и что ищем
Ждите, может кто-то осилит вникнуть в Ваши ролевые игры. Или попробуйте самостоятельно:
1) считать диапазон с листа 3 в массив в памяти;
2) в построчном цикле проверяйте соответствие значений Вашим условиям;
3) найденное обрабатывайте как Вам надо.
P.S. еще - мы ищем на листе 3, копируем в Ех - зачем в файле лист 1?
Название: Re: Поиск меньшего. Два критерия поиска. Копирование найденного диапазона.
Отправлено: Dmitriy_10 от 13.09.2017, 14:38
Из файла 1 берем следующий диапазон с "С" номером..

Ищем по двум столбикам сразу.. по А и Б.   
Х = значению в стобце А,
Y = значению в стобце В.

В Таблице 3 в Столбце  В ищем первое значение меньшее "x" одновременно при "y = y-1" в столбце С. (или у тоже меньше у предыдущего).

Спасибо за терпение!
Название: Re: Поиск меньшего. Два критерия поиска. Копир
Отправлено: kuklp от 13.09.2017, 21:11
Не знаю, правильно ли понял:
Sub FindRow()
    Dim x, y, a, i&, f As Boolean, b
    Dim c As Range, lr As Long
    x = Sheets("Ex").Range("A3").Value
    y = Sheets("Ex").Range("B3").Value
    With Sheets("3")
        a = .Range("B2:e" & .Range("B" & .Rows.Count).End(xlUp).Row).Value
    End With
    For i = 2 To UBound(a)
        If a(i, 1) = x Then
            If a(i, 2) = y Then
                f = True
                Exit For
            End If
        End If
    Next
    If f Then
        For i = i To 2 Step -1
            If a(i, 2) = y - 1 Then
                If a(i, 1) < x Then    ' при отсортированной по первому столбцу таблице эта строка не нужна
                    b = Application.Index(a, i, 0)
                    Sheets("Ex").[a20:d20] = b
                    Exit Sub
                End If
            End If
        Next
    End If
End Sub

А второй макрос лучше так:
Sub CNummer()
    Dim x As Variant
    x = Sheets("Ex").Range("G5").Value
    With Sheets("3").Range("$B$2:$E$7593")
        .AutoFilter 4, x
        .Offset(1).SpecialCells(12).Copy Sheets("Ex").Range("A20")
        .AutoFilter
    End With
End Sub

И оба макроса в модуль листа, а общие модули, где они были, удалить. И еще, я уже спрашивал - зачем там лист 1? Вот я его удалил - как это влияет на обсуждаемые вычисления?
Название: Re: Поиск меньшего. Два критерия поиска. Копир
Отправлено: kuklp от 13.09.2017, 22:39
Почему-то тема была заблокирована, сейчас все в порядке.
Название: Re: Поиск меньшего. Два критерия поиска. Копирование найденного диапазона.
Отправлено: Dmitriy_10 от 14.09.2017, 10:05
Огромное спасибо, добрый человек!

Все работает! Сейчас буду пытаться засунуть это все в цикл, чтобы "х" и "у" брались автоматически из А4, В4, А5, В5 и так далее до первой пустой ячейки..

P.S.: из листа 1 берется следующий диапазон с "С" номером от которого и "пляшем"
Название: Re: Поиск меньшего. Два критерия поиска. Копирование найденного диапазона.
Отправлено: Dmitriy_10 от 14.09.2017, 11:24
"И оба макроса в модуль листа, а общие модули, где они были, удалить."
Можете пожалуйста "чайнику" объяснить в чем разница код в модуле листа или в общем?

У меня будет не мало действий и кнопок, поэтому удобней разбить все это на модули

Название: Re: Поиск меньшего. Два критерия поиска. Копир
Отправлено: kuklp от 14.09.2017, 18:52
Все что касается конкретного листа, запускается с этого листа, помещаем в модуль этого листа. Все что относится ко всей книге и\или должно запускаться и работать из любого места, выносим в общий модуль. Свалка общих модулей, это тоже плохо. Можно в нескольких модулях собирать множество макросов, объединенной тематики. Макросы в любом модуле можно быстро найти по названиям, щелкнув комбобокс в правом верхнем углу окна редактирования
Название: Re: Поиск меньшего. Два критерия поиска. Копирование найденного диапазона.
Отправлено: Dmitriy_10 от 15.09.2017, 06:50
Но можно ведь ссылаться на лист/книгу из модулей, верно?

Спасибо!
Название: Re: Поиск меньшего. Два критерия поиска. Копир
Отправлено: kuklp от 15.09.2017, 09:14
Ссылаться можно откуда угодно на что угодно, но если специальный модуль листа пустует, а Вы создаете еще специальные модули для работы с\из этого листа, то со стороны это выглядит мягко говоря не совсем целесообразно. :) Но дело безусловно, Ваше. Поступайте как хотите, с опытом понимание само придет.
P.S. перечитайте мой первый ответ в теме, он и об этом тоже.
Название: Re: Поиск меньшего. Два критерия поиска. Копирование найденного диапазона.
Отправлено: Dmitriy_10 от 15.09.2017, 15:29
Можно еще пару стрынных вопросов?

Ваш код шикарно работает для такого поиска по одному диапазону.. (или по первому "х" из А3 и "у" из В3)
Мне же нужно чтобы программа сама брала следующий "х" и "у". И с этим у меня беда.

Закидываю еще один файл. В нем кнопка "СNummer" ищет "С" номер из листа "3". (До этого было фильтром, но наверное, лучше поиск)

Итак еще раз по пунктам:
1.Из листа "Ех" в ячейке G5 берем "С" Номер (которых много).
2. Поиском находим первый на листе "3" и вставляем его в лист "Ех" в допустим "А30:D30"
3. Вашим кодом ищем первый диапазон и вставляем его в "А31:D31" (сразу после первого найденного "С" номера, но поиск не 1 раз, а до тех пор, пока не скопируем "у" равный единице. (Как в примере на листе "Ех" с 3 по 5 строку.
4.Берем следующий "С" номер (если он есть) и повторяем такой же цикл.

"С" номер находит, но только первый и  если код пишу в модуле, а не на листе..

Спасибо заранее!
Название: Re: Поиск меньшего. Два критерия поиска. Копир
Отправлено: kuklp от 15.09.2017, 23:41
Нажмите кнопки по очереди.
Название: Re: Поиск меньшего. Два критерия поиска. Копирование найденного диапазона.
Отправлено: Dmitriy_10 от 15.10.2017, 08:17
Огромнейшее Вам спасибо!  :)