Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Максим78
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
получение и вставка первой видимой ячейки после фильтрации таблицы в Excel в код макроса, передача в переменную итога работы другого макроса
 
#
30.04.2025 09:29:59
Спасибо за советы. Забавно, но именно статью про отладку кода которую вы предложили я вчера читал.  Через F8 как раз и вижу строку с ошибкой, но не понимаю в чём она. Да и в целом код макроса
Код
Sub FirstVisibleCell()   With Worksheets("расчёт").AutoFilter.Range
       ActiveCell.Value2 = Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
 
End Sub
хоть и пытался не понял. Моих знаний пока не хватает )
Поэтому и попытался скрестить куски кода с нужной мне функциональностью. Т.к. оба макроса из моего первого сообщения по отдельности
работают отлично. А вообще вот эту конструкцию
Код
With Worksheets("расчёт").AutoFilter.Range
ActiveCell.Value2 = Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
End With
в принципе можно использовать в переменной? Через F8 строки
Код
Sub ФильтрыСводных() 
Dim nameВТМ As String
    With Worksheets("расчёт").AutoFilter.Range
    ActiveCell.Value2 = Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
Dim НаименованиеАналога As String
    With Worksheets("расчёт").AutoFilter.Range
    ActiveCell.Value2 = Range("B" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
проходятся без сообщения об ошибке...
 
Перейти    
получение и вставка первой видимой ячейки после фильтрации таблицы в Excel в код макроса, передача в переменную итога работы другого макроса
 
#
30.04.2025 08:43:07
Sanja, доброе утро!
К сожалению реальный файл приложить не могу, т.к. на листе BI сводные построены на основе корпоративного OLAP куба.

Сделал похожий файл, общий принцип тот же.
Лист "расчёт" с простой таблицей откуда надо взять отфильтрованные значения.
Лист "BI" - на нём сводная в фильтры которой надо вставить отфильтрованные значения макросом. Правда в нём нет сводной на основе куба, поэтому макрос "ФильтрыСводных" переписал под обычную сводную. Если бы сводная была одна или фильтруемых значений мало я бы не заморачивался, но и сводных несколько и значений бывает больше не один десяток.
Код
Sub ФильтрыСводных()

Dim nameВТМ As String
    With Worksheets("расчёт").AutoFilter.Range
    ActiveCell.Value2 = Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
Dim НаименованиеАналога As String
    With Worksheets("расчёт").AutoFilter.Range
    ActiveCell.Value2 = Range("B" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
    
    ActiveSheet.PivotTables("Сводная таблица3").PivotFields("ВТМ").CurrentPage = nameВТМ
    ActiveSheet.PivotTables("Сводная таблица3").PivotFields("Наименование аналога").CurrentPage = НаименованиеАналога

End Sub

Теперь ругается на эту строку  
Код
ActiveSheet.PivotTables("Сводная таблица3").PivotFields("ВТМ").CurrentPage = nameВТМ
 
Перейти    
получение и вставка первой видимой ячейки после фильтрации таблицы в Excel в код макроса, передача в переменную итога работы другого макроса
 
#
29.04.2025 16:35:20
отредактировал
 
Перейти    
получение и вставка первой видимой ячейки после фильтрации таблицы в Excel в код макроса, передача в переменную итога работы другого макроса
 
#
29.04.2025 12:17:44
Добрый день!
На листе "расчёт" есть таблица в которой я произвожу расчёт. Периодически есть необходимость фильтровать таблицу по названиям поставщиков и продуктов.
Эти названия поставщиков продуктов я часто выбираю на другом листе "BI" в сводных для просмотра ряда характеристик. Что бы не копировать каждый раз названия поставщиков и продуктов записал макрос

Код
Sub ФильтрыСводных()

Dim nameВТМ As String
nameВТМ = Sheets("расчёт").Range("I7")
Dim nameSKU As String
nameSKU = Sheets("расчёт").Range("C7")
    
    Sheets("BI").Select
    ActiveSheet.PivotTables("Сводная таблица8").PivotFields( _
        "[prod].[SKU - ВТМ].[SKU - ВТМ]").VisibleItemsList = Array("[prod].[SKU - ВТМ].&[" & nameВТМ & "]")
    ActiveSheet.PivotTables("Сводная таблица8").PivotFields( _
        "[prod].[SKU - НАИМЕНОВАНИЕ АНАЛОГ].[SKU - НАИМЕНОВАНИЕ АНАЛОГ]").VisibleItemsList = Array("[prod].[SKU - НАИМЕНОВАНИЕ АНАЛОГ].&[" & nameSKU & "]")
    ActiveSheet.PivotTables("Сводная таблица3").PivotFields( _
         "[product].[SKU - ВТМ].[SKU - ВТМ]").VisibleItemsList = Array("[product].[SKU - ВТМ].&[" & nameВТМ & "]")
   
End Sub


Он работает.
Однако с листа "расчёт" макрос получает только первые названия поставщика и товара после заголовка таблицы из ячеек "I7" и "C7".
Нашёл макрос которой находит именно первое видимое значение в отфильтрованной ячейке.

Код
Sub FirstVisibleCell()
   With Worksheets("расчёт").AutoFilter.Range
       ActiveCell.Value2 = Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With

End Sub


Хочу результат его работы передать в переменную первого макроса, но пока не получается. Пробовал так
Код
Sub Фильтры()
Dim nameВТМ As String
    With Worksheets("расчёт").AutoFilter.Range
       ActiveCell.Value2 = Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
однако ругается = Run-time error '1004: Unable to get the SpecialCells property of the Range class

Подскажите пжл что делаю не так?
Изменено: Максим78 - 29.04.2025 13:12:51 (отформатировал код)
 
Перейти    
Подставить значение в скрипт Power Query
 
#
12.11.2024 13:42:34
Всем доброго дня!
По мотивам параметризации путей запроса (почитал ряд тем на этом форуме), но в итоге у меня получается пока не всё, просьба помочь.
По работе надо копировать рабочий файл в разные папки и обрабатывать PQ те файлы которые в папке содержатся.
После копирования переименовываю рабочий файл добавляя в название имя обрабатываемого контрагента.
Захотел сделать относительные ссылки в рабочем файле, чтобы не менять руками источник каждый раз.

Сделал в рабочем файле умную таблицу с 2 столбцами и 1 строкой.
в 1 строке 1 столбца получаю путь к текущей папке через формулу: =ЛЕВСИМВ(ЯЧЕЙКА("filename"); НАЙТИ("[";ЯЧЕЙКА("filename"))-1)
и полученный путь удалось включить в шаг источник: = Folder.Files(Excel.CurrentWorkbook(){[Name="Параметры2"]}[Content]{0}[АдресПапки])
работает )

далее фильтром в PQ мне надо отфильтровать файл с именем нужного контрагента (м.б. разные), для этого
в 1 строке 2 столбца получаю формулой часть имени рабочего файла содержащего имя контрагента
вот так ==ЛЕВСИМВ(ПРАВСИМВ(ЯЧЕЙКА("filename");НАЙТИ("[Прогноз ";ЯЧЕЙКА("filename"))-9);НАЙТИ(" ";ПРАВСИМВ(ЯЧЕЙКА("filename");НАЙТИ("[Прогноз ";ЯЧЕЙКА("filename"))-9)))
работает)

Пробую подставить значение из второго столбца умной таблицы в запрос (чтобы отфильтровать нужное имя котрагента:
= Table.SelectRows(Источник, each Text.Contains([Name], Excel.CurrentWorkbook(){[Name="Параметры2"]}[Content]{0}[ИмяКонт-та))
не работает ((

Подскажите пожалуйста что не так делаю.
Изменено: Максим78 - 12.11.2024 13:43:08
 
Перейти    
подставить значение переменной макроса в фильтр сводной
 
#
08.11.2024 14:16:58
Понял, таблицу приложить не смогу к сожалению.
В любом случае ещё раз огромное спасибо.
 
Перейти    
подставить значение переменной макроса в фильтр сводной
 
#
08.11.2024 10:43:25
Дмитрий(The_Prist) Щербаков, большое спасибо!
Теперь работает. Дополнил ещё одним фильтром в той же сводной и ещё одним фильтром в другой сводной. Получилось так (ниже)
Причём в последней сводной
"[prod].[SKU - ВТМ].[SKU - ВТМ]"). не срабатывало, через макрорекордер понял, что надо так
"[product].[SKU - ВТМ].[SKU - ВТМ]").

заодно узнал, что сводные построены на подключении к разным моделям данных и вот эта часть "[prod]" это название таблицы.
Если не сложно подскажите почему надо [SKU - ВТМ].[SKU - ВТМ] указывать дважды
вот здесь
PivotFields( _
       "[prod].[SKU - ВТМ].[SKU - ВТМ]").
а здесь один раз
VisibleItemsList = Array("[prod].[SKU - ВТМ].




Sub Макрос3()
Dim nameВТМ As String
nameВТМ = Sheets("расчёт").Range("I7")
Dim nameSKU As String
nameSKU = Sheets("расчёт").Range("C25")
   
   Sheets("BI").Select
   ActiveSheet.PivotTables("Сводная таблица8").PivotFields( _
      "[prod].[SKU - ВТМ].[SKU - ВТМ]").VisibleItemsList = Array("[prod].[SKU - ВТМ].&[" & nameВТМ & "]")
   ActiveSheet.PivotTables("Сводная таблица8").PivotFields( _
       "[prod].[SKU - НАИМЕНОВАНИЕ АНАЛОГ].[SKU - НАИМЕНОВАНИЕ АНАЛОГ]").VisibleItemsList = Array("[prod].[SKU - НАИМЕНОВАНИЕ АНАЛОГ].&[" & nameSKU & "]")
    ActiveSheet.PivotTables("Сводная таблица3").PivotFields( _
        "[product].[SKU - ВТМ].[SKU - ВТМ]").VisibleItemsList = Array("[product].[SKU - ВТМ].&[" & nameВТМ & "]")
End Sub
 
Перейти    
подставить значение переменной макроса в фильтр сводной
 
#
07.11.2024 17:15:37
Цитата
написал:
не мог макрорекордер записать так:Array("[prod].[SKU - ВТМ].[SKU - ВТМ].&[" & name1 & "]")покажите исходную строку. И что записано в I7.
Да, это я переделал почитав информацию с ряда форумов, исходный код приложил выше
 
Перейти    
подставить значение переменной макроса в фильтр сводной
 
#
07.11.2024 17:04:31
Записанный макрорекордером макрос такой

Sub Макрос1()
'
' Макрос1 Макрос
'

'
   Sheets("расчёт").Select
   Range("I7").Select
   Selection.Copy
   Sheets("BI").Select
   ActiveSheet.PivotTables("Сводная таблица8").PivotFields( _
       "[prod].[SKU - ВТМ].[SKU - ВТМ]").VisibleItemsList = Array( _
       "[prod].[SKU - ВТМ].&[ЛОРЕНЦ СНЭК-УОРЛД РАША]")
   Range("N17").Select
End Sub


Я попробовал его переделать. Добавил переменную, с значением из ячейки I7 что бы если оно изменилось не указывать его.
И попробовал переменную вставить вот сюда
  "[prod].[SKU - ВТМ].&[ЛОРЕНЦ СНЭК-УОРЛД РАША]")
   Range("N17").Select

но что-то указываю нет так
Изменено: Максим78 - 07.11.2024 17:13:43
 
Перейти    
подставить значение переменной макроса в фильтр сводной
 
#
07.11.2024 16:21:15
Добрый день!
Есть сводная таблица построенная на вроде на основе OLAP-куба на листе "BI". Хочу сделать так, чтобы макросом в фильтр сводной таблицы передавалось значение с листа "расчёт" из ячейки "I7". В этой ячейке меняются текстовые значения. Через макрорекордер записал макрос и потом попробовал его переделать.

Получилось так
Sub Макрос3()
Dim name1 As String
name1 = Sheets("расчёт").Range("I7")
   
   Sheets("BI").Select
   ActiveSheet.PivotTables("Сводная таблица8").PivotFields( _
       "[prod].[SKU - ВТМ].[SKU - ВТМ]").VisibleItemsList = Array("[prod].[SKU - ВТМ].[SKU - ВТМ].&[" & name1 & "]")
End Sub

при выполнении выдаёт ошибку:
Run-time error '1004:
Не удается найти элемент в кубе OLAP.

в Debug подсвечивает эту часть
  ActiveSheet.PivotTables("Сводная таблица8").PivotFields( _
       "[prod].[SKU - ВТМ].[SKU - ВТМ]").VisibleItemsList = Array("[prod].[SKU - ВТМ].[SKU - ВТМ].&[" & name1 & "]")

Подскажите пжл, что не так указываю?
Изменено: Максим78 - 07.11.2024 16:23:08
 
Перейти    
power query ошибка без ошибки, запрос в power query выдаёт ошибки хотя по шагам всё проходит нормально
 
#
02.04.2024 13:50:25
Цитата
написал:
Можно использовать связку  List.Accumulate + Table.AddColumn
спасибо, тоже попробую, только про let-in теперь надо больше узнать, чтобы разобраться )))
 
Перейти    
power query ошибка без ошибки, запрос в power query выдаёт ошибки хотя по шагам всё проходит нормально
 
#
02.04.2024 12:46:40
Спасибо, понял.
Это реально упростит запрос.  
 
Перейти    
power query ошибка без ошибки, запрос в power query выдаёт ошибки хотя по шагам всё проходит нормально
 
#
02.04.2024 12:30:38
Если я правильно понял идею, то можно
первым шагом создать 1 столбец с длинным именем состоящим из нужных мне имён через разделитель
вторым шагом разделить по этому разделителю  
 
Перейти    
power query ошибка без ошибки, запрос в power query выдаёт ошибки хотя по шагам всё проходит нормально
 
#
02.04.2024 12:21:07
Цитата
написал:
добавить столбец с запятыми, а затем разделить его по разделителю )
Не совсем понял.
Уточню, надо добавлять около 10 пустых столбцов с заранее известными мне именами.
Поэтому использовал Table.AddColumn и добавил в каждом новом шаге новый столбец с новым именем,
а функции которая добавляла бы несколько столбцов сразу (типа перечислением: столб1, имя; столб2, имя;  столб3, имя;) не нашел (((
Думал может это как-то в Table.AddColumn можно это сделать, но говорят низя (
 
Перейти    
power query ошибка без ошибки, запрос в power query выдаёт ошибки хотя по шагам всё проходит нормально
 
#
02.04.2024 11:35:06
Понял, всем ещё раз спасибо.
Пока проще через много "тыц" )))
 
Перейти    
power query ошибка без ошибки, запрос в power query выдаёт ошибки хотя по шагам всё проходит нормально
 
#
02.04.2024 11:14:52
Большое спасибо, всё как всегда не сложно если знать где искать, а я до этого голову сломал.

Если не сложно, подскажите возможно ли создать несколько столбцов с разными именами в одном шаге, в функции Table.AddColumn. Как то так
#"Вставленный столбец1" = Table.AddColumn(#"Переупорядоченные столбцы2", "новый1", "новый2", "новый3", each null, type number),
путём их перечисления.
Я просто создал столько шагов, сколько надо новых столбцов. так как ниже. Читал про разные способы, но про те что читал они пока сложны для меня.

Сейчас так
 #"Переупорядоченные столбцы2" = Table.ReorderColumns(#"Объединенные столбцы",{"Сцепить", "Наименование Контрагента", "ИНН плательщика", "№ договора", "Приход"}),    
#"Вставленный столбец1" = Table.AddColumn(#"Переупорядоченные столбцы2", "ФМ остатки в машине мб дубли", each null, type number),
   #"Вставленный столбец2" = Table.AddColumn(#"Вставленный столбец1", "АСБНУ (остатки на дату выгрузки) мб дубли", each null, type number),
   #"Вставленный столбец3" = Table.AddColumn(#"Вставленный столбец2", "АСКУ (сумма услуг оказанных в текущем периоде)", each null, type number),
   #"Вставленный столбец4" = Table.AddColumn(#"Вставленный столбец3", "Итог (АСБНУ-АСКУ)", each null, type number),
   #"Вставленный столбец5" = Table.AddColumn(#"Вставленный столбец4", "Доп услуги", each null, type number),
   #"Вставленный столбец6" = Table.AddColumn(#"Вставленный столбец5", "Итоговый остаток не введённый в ФМ", each null, type number),
   #"Вставленный столбец7" = Table.AddColumn(#"Вставленный столбец6", "Анализ", each null, type number),
   #"Вставленный столбец8" = Table.AddColumn(#"Вставленный столбец7", "Постоплата", each null, type number),
   Пользовательская1 = Table.AddColumn(#"Вставленный столбец8", "Минус", each null, type number)
 
Перейти    
power query ошибка без ошибки, запрос в power query выдаёт ошибки хотя по шагам всё проходит нормально
 
#
02.04.2024 10:45:17
Добрый день!
Начинаю работать с power query, написал запрос к таблице в книге. Внутри запроса если идти по шагам, то нет ошибок. В выгружаемой запросом таблице тоже нет ошибок (насколько понимаю). Ошибки, а их столько, сколько выгружается строк видно только в разделе где отражаются запросы.

Не понимаю из-за чего возникает ошибка и в чём она состоит?
заранее всем спасибо
Изменено: Максим78 - 02.04.2024 10:46:04
 
Перейти    
Страницы: 1
Наверх