Однажды сталкивался с подобной задачей но там было фиксированное к-во листов (2). Суть была вот в чем: есть два прайса ЛЮКС и ТМ на отдельных листах; в них пользователь в некоторых позициях проставляет необходимое количество товара далее когда все выбрано переходит на лист отчет там жмет кнопку сформировать заявку и получает новую табличку в которой будут только те позиции в которых проставлено количество (>0) это достигается тем что на листах ЛЮКС и ТМ создаются дополнительные столбцы с нумерацией по принципу если количество>0, то предыдущая ячейка в столбце+1, иначе предыдущая ячейка. Это нужно для нормальной работы ИНДЕКС(ПОИСКПОЗ()) вооооттт такое предисловие пример этот в файлике
Ну а теперь к самой проблеме. Сейчас столкнулся с необходимостью формирования подобных таблиц (для каждого из энного количества параметров) собирая данные из нескольких десятков однотипных листов. То есть каждый лист представляет собой отчет за месяц (01.2019; 02.2019 и т.д.) в котором есть таблица в ней столбец категория и значения в нем могут встречаться несколько раз И вот мне надо каждую строку в которой по столбцу категория = нужной категории (на всех листах) перенести по порядку в эту новую таблицу сложность в том что метод применяемый мной для решения в примере выше по тексту не подходит по двум причинам: 1. получатся огромнейшие формулы 2. каждый раз добавляя новый лист из которого нужно собрать данные будет сложно корректировать формулу.
Вот надеюсь более менее внятно выразился) Может кто сталкивался с подобной проблемой или подскажете в каком направлении "копать" заранее благодарен.
Однажды сталкивался с подобной задачей но там было фиксированное к-во листов (2). Суть была вот в чем: есть два прайса ЛЮКС и ТМ на отдельных листах; в них пользователь в некоторых позициях проставляет необходимое количество товара далее когда все выбрано переходит на лист отчет там жмет кнопку сформировать заявку и получает новую табличку в которой будут только те позиции в которых проставлено количество (>0) это достигается тем что на листах ЛЮКС и ТМ создаются дополнительные столбцы с нумерацией по принципу если количество>0, то предыдущая ячейка в столбце+1, иначе предыдущая ячейка. Это нужно для нормальной работы ИНДЕКС(ПОИСКПОЗ()) вооооттт такое предисловие пример этот в файлике
Ну а теперь к самой проблеме. Сейчас столкнулся с необходимостью формирования подобных таблиц (для каждого из энного количества параметров) собирая данные из нескольких десятков однотипных листов. То есть каждый лист представляет собой отчет за месяц (01.2019; 02.2019 и т.д.) в котором есть таблица в ней столбец категория и значения в нем могут встречаться несколько раз И вот мне надо каждую строку в которой по столбцу категория = нужной категории (на всех листах) перенести по порядку в эту новую таблицу сложность в том что метод применяемый мной для решения в примере выше по тексту не подходит по двум причинам: 1. получатся огромнейшие формулы 2. каждый раз добавляя новый лист из которого нужно собрать данные будет сложно корректировать формулу.
Вот надеюсь более менее внятно выразился) Может кто сталкивался с подобной проблемой или подскажете в каком направлении "копать" заранее благодарен.MrFrai1992
Pelena, Спасибо! Попробую сам вникнуть в код подробнее (незнакомые команды для меня), но вроде то что мне и было нужно и вдвое проще чем то что сам придумал)
Pelena, Спасибо! Попробую сам вникнуть в код подробнее (незнакомые команды для меня), но вроде то что мне и было нужно и вдвое проще чем то что сам придумал)MrFrai1992
Pelena, еще раз спасибо за помощь, но возник вопрос по теме с которым не получается справится своими силами вот макрос на основе вашего [vba]
Код
Public Sub seach 'макрос-поисковик 'sm, pom, sg, pog, s, po - переменные задающие границы поиска по датам Dim sm, pom, sg, pog, s, po As Integer 'cat - переменные отвечающие за выбор категории (искомые значения) 'Dim cat1, cat2, cat3 As String Dim sh As Worksheet, oTbl As ListObject, x, Poz& Dim PctDone As Double 'не отображать на экране промежуточные действия Application.ScreenUpdating = False
'определение области построения и ее очистка Set oTbl = ActiveSheet.ListObjects("Таблица1") With oTbl With .DataBodyRange .ClearContents If .Rows.Count > 1 Then .Rows(2).Resize(.Rows.Count - 1).Delete End With
'определение переменных Poz = 1 'для выбора категорий поиска cat1 = Range("R1") cat2 = Range("R2") cat3 = Range("R3") 'для определения диапазона просматриваемых листов в книге sm = Range("B2") pom = Range("B3") sg = Range("C2") pog = Range("C3") s = Worksheets(sm & "." & sg).Index po = Worksheets(pom & "." & pog).Index
'заполнение таблицы искомыми значениями For Each sh In Worksheets If sh.Index >= s And sh.Index <= po Then x = sh.Range("A3", sh.Cells(sh.Rows.Count, 10).End(xlUp)).Value For i = 1 To UBound(x) If x(i, 2) = cat1 or x(i, 2) = cat2 or x(i, 2) = cat3 Then If Poz > 1 Then .ListRows.Add With .DataBodyRange .Cells(.Rows.Count, 1) = Poz .Cells(.Rows.Count, 2) = x(i, 2) .Cells(.Rows.Count, 3) = x(i, 3) .Cells(.Rows.Count, 4) = x(i, 4) .Cells(.Rows.Count, 5) = x(i, 5) .Cells(.Rows.Count, 6) = x(i, 6) .Cells(.Rows.Count, 7) = x(i, 7) .Cells(.Rows.Count, 8) = x(i, 8) .Cells(.Rows.Count, 9) = x(i, 9) .Cells(.Rows.Count, 10) = x(i, 10) End With Poz = Poz + 1 End If PctDone = i / UBound(x) Call UpdateProgress(PctDone) Next i End If Next sh End With Application.ScreenUpdating = True MsgBox ("Данные обновленны!") End Sub
[/vba] суть проблемы вот в чем нужно чтобы макрос искал по нескольким категориям (сейчас они присвоены переменным cat1, cat2, cat3). Тут на первый взгляд все казалось хорошо и просто НО: 1. если одной из категорий не выбрано значение то макрос работает не корректно (появляются пустые и не полностью заполненные строки которые вообще не нужно выводить) 2. категорий может быть выбранно от 1 до 40 а то и больше и прописывать это вручную как я пробовал выше наверняка неверный путь... пробовал сделать cat as new colection и сравнивать x(i, 2) = cat но так не получается - ошибка Надеюсь на ваши подсказки
ЗЫ: не сразу получилось код нормально вставить :blink:
Pelena, еще раз спасибо за помощь, но возник вопрос по теме с которым не получается справится своими силами вот макрос на основе вашего [vba]
Код
Public Sub seach 'макрос-поисковик 'sm, pom, sg, pog, s, po - переменные задающие границы поиска по датам Dim sm, pom, sg, pog, s, po As Integer 'cat - переменные отвечающие за выбор категории (искомые значения) 'Dim cat1, cat2, cat3 As String Dim sh As Worksheet, oTbl As ListObject, x, Poz& Dim PctDone As Double 'не отображать на экране промежуточные действия Application.ScreenUpdating = False
'определение области построения и ее очистка Set oTbl = ActiveSheet.ListObjects("Таблица1") With oTbl With .DataBodyRange .ClearContents If .Rows.Count > 1 Then .Rows(2).Resize(.Rows.Count - 1).Delete End With
'определение переменных Poz = 1 'для выбора категорий поиска cat1 = Range("R1") cat2 = Range("R2") cat3 = Range("R3") 'для определения диапазона просматриваемых листов в книге sm = Range("B2") pom = Range("B3") sg = Range("C2") pog = Range("C3") s = Worksheets(sm & "." & sg).Index po = Worksheets(pom & "." & pog).Index
'заполнение таблицы искомыми значениями For Each sh In Worksheets If sh.Index >= s And sh.Index <= po Then x = sh.Range("A3", sh.Cells(sh.Rows.Count, 10).End(xlUp)).Value For i = 1 To UBound(x) If x(i, 2) = cat1 or x(i, 2) = cat2 or x(i, 2) = cat3 Then If Poz > 1 Then .ListRows.Add With .DataBodyRange .Cells(.Rows.Count, 1) = Poz .Cells(.Rows.Count, 2) = x(i, 2) .Cells(.Rows.Count, 3) = x(i, 3) .Cells(.Rows.Count, 4) = x(i, 4) .Cells(.Rows.Count, 5) = x(i, 5) .Cells(.Rows.Count, 6) = x(i, 6) .Cells(.Rows.Count, 7) = x(i, 7) .Cells(.Rows.Count, 8) = x(i, 8) .Cells(.Rows.Count, 9) = x(i, 9) .Cells(.Rows.Count, 10) = x(i, 10) End With Poz = Poz + 1 End If PctDone = i / UBound(x) Call UpdateProgress(PctDone) Next i End If Next sh End With Application.ScreenUpdating = True MsgBox ("Данные обновленны!") End Sub
[/vba] суть проблемы вот в чем нужно чтобы макрос искал по нескольким категориям (сейчас они присвоены переменным cat1, cat2, cat3). Тут на первый взгляд все казалось хорошо и просто НО: 1. если одной из категорий не выбрано значение то макрос работает не корректно (появляются пустые и не полностью заполненные строки которые вообще не нужно выводить) 2. категорий может быть выбранно от 1 до 40 а то и больше и прописывать это вручную как я пробовал выше наверняка неверный путь... пробовал сделать cat as new colection и сравнивать x(i, 2) = cat но так не получается - ошибка Надеюсь на ваши подсказки
ЗЫ: не сразу получилось код нормально вставить :blink:MrFrai1992
Сообщение отредактировал MrFrai1992 - Понедельник, 08.07.2019, 15:13
MrFrai1992, судя по описанию, можно попробовать фильтр, обычный или расширенный. То есть фильтровать на листе по нескольким критериям и копировать в заявку. Посмотрите варианты в приложенном файле здесь
MrFrai1992, судя по описанию, можно попробовать фильтр, обычный или расширенный. То есть фильтровать на листе по нескольким критериям и копировать в заявку. Посмотрите варианты в приложенном файле здесьPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Что то похожее и мне надо но не понимаю как под себя это переделать у меня в пользовательской форме через элемент листбох выбирается одна несколько или все категории какого либо раздела и уже по выбранным категориям нужно осуществить поиск по энному количеству листов (20+) и одновременно формировать таблицу с итогами поиска Проблемы у меня возникли именно на этапе формирования критерия поиска. Не знаю как (и можно ли вообще) вот здесь
If x(i, 2) = cat1 or x(i, 2) = cat2 or x(i, 2) = cat3 Then
прописать так чтобы не каждая выбранная строка = cat(i), а формировался какой-то массив выбранных значений и при обработке поиска по листам каждая следующая позиция проверялась на соответствие любому из элементов этого массива
Что то похожее и мне надо но не понимаю как под себя это переделать у меня в пользовательской форме через элемент листбох выбирается одна несколько или все категории какого либо раздела и уже по выбранным категориям нужно осуществить поиск по энному количеству листов (20+) и одновременно формировать таблицу с итогами поиска Проблемы у меня возникли именно на этапе формирования критерия поиска. Не знаю как (и можно ли вообще) вот здесь
If x(i, 2) = cat1 or x(i, 2) = cat2 or x(i, 2) = cat3 Then
прописать так чтобы не каждая выбранная строка = cat(i), а формировался какой-то массив выбранных значений и при обработке поиска по листам каждая следующая позиция проверялась на соответствие любому из элементов этого массиваMrFrai1992