Сводная таблица макросом

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сводная таблица макросом (Макросы/Sub)

mikaelw

Дата: Среда, 17.09.2014, 23:33 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 179

Репутация: 1 ±

Замечаний: 20% ±

Excel 2010, 2013, 2016

Извините за вопрос и думаю его уже не однократно задавали, я просто не пойму как правильно сформулировать запрос.

Задача: написать макрос создания СТ.

Не чего проще как использовать для этого "записывальщик" я не придумал.

В результате получаю код, в котором записан сразу диапазон ячеек для СТ.

Как диапазон стандартный заменить на меняющийся в зависимости от размера таблицы.

Мои "топтания" в примере

Извините за простоту вопроса!

К сообщению приложен файл: 170920142332.xlsm (29.0 Kb)

Ответить

DJ_Marker_MC

Дата: Четверг, 18.09.2014, 01:37 | Сообщение № 2

Группа: Друзья

Ранг: Ветеран

Сообщений: 991

Репутация: 213 ±

Замечаний: 0% ±

Excel 2019

Доброй ночи.

так нужно?

[vba]

Код

Sub iTable()
Dim lLastRow As Long
Dim lLastCol As Long
Dim t As Range

     lLastRow = Cells(Rows.Count, "A").End(xlUp).Row
     lLastCol = Cells.SpecialCells(xlLastCell).Column
     Set t = Range(Cells(1, 1), Cells(lLastRow, lLastCol))

     Sheets.Add.Name = "Report"

     ActiveWorkbook.PivotCaches.Create(xlDatabase, t).CreatePivotTable "Report!R1C1"
End Sub

[/vba]

К сообщению приложен файл: 6952470.xlsm (23.9 Kb)

Сообщение отредактировал DJ_Marker_MC - Четверг, 18.09.2014, 01:48

Ответить

mikaelw

Дата: Четверг, 18.09.2014, 08:10 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 179

Репутация: 1 ±

Замечаний: 20% ±

Excel 2010, 2013, 2016

Цитата DJ_Marker_MC, 18.09.2014 в 01:37, в сообщении № 2

Доброй ночи.

так нужно?

Именно в точку!
Спасибо!

P.S. Зачем нужна команда set ?

Ответить

_Boroda_

Дата: Четверг, 18.09.2014, 09:35 | Сообщение № 4

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

Репутация: 6481 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Цитата DJ_Marker_MC, 18.09.2014 в 01:37, в сообщении № 2

lLastRow = Cells(Rows.Count, "A").End(xlUp).Row
lLastCol = Cells.SpecialCells(xlLastCell).Column

Евгений, а в чем скрытый смысл различной методики подсчета количества строк и столбцов? Если строки считаем через END, то логично было бы и столбцы считать аналогично. Но только, чтобы случайно не затесались столбцы с незаполненными ячейками первой строки, прыгать не справа налево, а слева направо. А xlLastCell - чревато, ну его вообще (по крайней мере, для этого кода.
Предлагаю немного изменить Ваш макрос. Что-то типа
[vba]

Код

Sub iTable()
Dim lLastRow As Long
Dim lLastCol As Long
Dim t As Range
        lLastRow = Cells(Rows.Count, "A").End(xlUp).Row
        lLastCol = Cells(1, 1).End(xlToRight).Column
        Set t = Range(Cells(1, 1), Cells(lLastRow, lLastCol))
        On Error Resume Next
        Sheets.Add.Name = "Report"
        If Err.Number <> 0 Then MsgBox "Лист Report уже существует": shDel: Exit Sub
        ActiveWorkbook.PivotCaches.Create(xlDatabase, t).CreatePivotTable "Report!R1C1"
        If Err.Number <> 0 Then MsgBox "Ошибка создания сводной таблицы": shDel
End Sub

Sub shDel()'Удаление созданного листа
        Application.DisplayAlerts = 0
        ActiveSheet.Delete
        Application.DisplayAlerts = 1
End Sub

[/vba]

К сообщению приложен файл: 6952470_1.xlsm (24.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Rioran

Дата: Четверг, 18.09.2014, 09:46 | Сообщение № 5

Группа: Авторы

Ранг: Ветеран

Сообщений: 903

Репутация: 290 ±

Замечаний: 0% ±

Excel 2013

mikaelw, здравствуйте.

Цитата mikaelw, 18.09.2014 в 08:10, в сообщении № 3

Зачем нужна команда set ?

Ключевое слово Set создаёт связь между переменной и объектом. В коде от DJ_Marker_MC с помощью Set задаётся связь с набором ячеек от А1 до расчётных:

[vba]

Код

Set t = Range(Cells(1, 1), Cells(lLastRow, lLastCol))

[/vba]
Если мы присваиваем ячейке/переменной не объект, а лишь значение (будь то число, дата или текст), то используется просто знак "равно".

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Сообщение отредактировал Rioran - Четверг, 18.09.2014, 09:47

Ответить

The_Prist

Дата: Четверг, 18.09.2014, 10:34 | Сообщение № 6

Группа: Друзья

Ранг: Участник

Сообщений: 85

Репутация: 22 ±

Замечаний: 0% ±

2010

Цитата Rioran, 18.09.2014 в 09:46, в сообщении № 5

то используется просто знак "равно"

Или Let :-)
Просто Let используется в VB по умолчанию и его указание необязательно.

Errare humanum est, stultum est in errore perseverare

Ответить

DJ_Marker_MC

Дата: Четверг, 18.09.2014, 10:41 | Сообщение № 7

Группа: Друзья

Ранг: Ветеран

Сообщений: 991

Репутация: 213 ±

Замечаний: 0% ±

Excel 2019

_Boroda_, я просто не менял поиск последнего столбца и строки как они были записаны у автора.
Лично я чаще использую этот метод поиска:
[vba]

Код

lAntR = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
iAntK = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

[/vba]

ну или такие связки:
[vba]

Код

iRow = Cells(Rows.Count, 1).End(xlUp).Row
iClm = Cells(1, 1).End(xlToRight).Column

iRow = Range("A1").SpecialCells(xlLastCell).Row
iClm = Range("A1").SpecialCells(xlLastCell).Column

[/vba]

[offtop]up:
И все же, какой из вышеописанных методов лучше всего использовать?[/offtop]

Сообщение отредактировал DJ_Marker_MC - Четверг, 18.09.2014, 10:52

Ответить

mikaelw

Дата: Четверг, 18.09.2014, 19:25 | Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 179

Репутация: 1 ±

Замечаний: 20% ±

Excel 2010, 2013, 2016

Цитата _Boroda_, 18.09.2014 в 09:35, в сообщении № 4

Спасибо, но как уже писал Евгений(DJ_Marker_MC) это я переменные так написал в исходнике(от не знания). На будущее Ваш коммент учту(ДЕЛЬНЫЙ)!

Цитата Rioran, 18.09.2014 в 09:46, в сообщении № 5

Ключевое слово Set

СПС. Просто я смысла понять не мог.

Особое спасибо DJ_Marker_MC(Евгению), т.к. решил проблему и наконец-то написал макрос по отчету на который каждый день тратил по 1,5 часа.

Ответить

RAN

Дата: Четверг, 18.09.2014, 21:41 | Сообщение № 9

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

И чего копья ломать?
[vba]

Код

Set r = Range("A1").CurrentRegion

[/vba]
Под сводную самое оно.

Быть или не быть, вот в чем загвоздка!

Ответить

mikaelw

Дата: Четверг, 18.09.2014, 21:45 | Сообщение № 10

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 179

Репутация: 1 ±

Замечаний: 20% ±

Excel 2010, 2013, 2016

Цитата RAN, 18.09.2014 в 21:41, в сообщении № 9

И чего копья ломать?
Set r = Range("A1").CurrentRegion

Под сводную самое оно.

т.е. вообще без переменных lLastRow lLastCol ?

Ответить

RAN

Дата: Четверг, 18.09.2014, 21:46 | Сообщение № 11

Группа: Друзья

Ранг: Экселист

Сообщений: 5660

Репутация: 1163 ±

Замечаний: 0% ±

2010

Именно
[p.s.]А зачем цитата?[/p.s.]

Быть или не быть, вот в чем загвоздка!

Сообщение отредактировал RAN - Четверг, 18.09.2014, 21:48

Ответить

_Boroda_

Дата: Четверг, 18.09.2014, 22:23 | Сообщение № 12

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

Репутация: 6481 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Цитата RAN, 18.09.2014 в 21:41, в сообщении № 9

Set r = Range("A1").CurrentRegion
Под сводную самое оно.

Не всегда. Как только заполнение первой строки по количеству ячеек меньше, чем заполнение нижних - получим ошибку создания (неопределенное название поля). Поэтому для сводной как раз очень удобно столбцы искать от первой ячейки первой строки вправо, а строки - от последней ячейки первого столбца вверх
Пример в файле

К сообщению приложен файл: 885522.xlsx (8.2 Kb) · 4280469.jpg (14.7 Kb)

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сводная таблица макросом (Макросы/Sub)

Страница 1 из 1
1