Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Макрос сводных таблиц без привязки имени листа - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос сводных таблиц без привязки имени листа (Макросы/Sub)
Макрос сводных таблиц без привязки имени листа
Santtic Дата: Четверг, 20.02.2020, 15:52 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 190
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
Добрый день. Хочу упростить работу своим коллегам сдачи рапортов.
Специфика такова, никто никогда не знает сколько будет рейсов за одну работу.Для этого я прописал макрос, который делает слепок из фирмы, вставляет его с именем листа "ReportBR" это стартовый, по мере увеличения количества, имя меняется с приставкой 1-2-3...и т.д. Это мне усложнило жизнь,так как я могу собирать и в данный момент этим пользуюсь данные собираю только формулами такого типа
Код
=ЕСЛИ(ReportBR!$E$11>0;ReportBR!$E$11;"")
это с 1го листа
Код
=ЕСЛИ('ReportBR (2)'!$E$11>0;'ReportBR (2)'!$E$11;"")
это со второго листа, ничего заурядного. Но для этого мне надо иметь 10 листов ( максимум что может быть это 10 рейсов). так как при формулах я не могу удалять/добавлять лист, формула бъется при отсутствии листа, пишет #ссылка#и соответственно не работает.
У моего коллеги такой вопрос решен следующим образом: у него макрос при добавлении листа дописывает формулы в новую строку "Лист 1" начиная с 5й строки т.е. 6-7-8....и тд.
Как вы мне посоветуете сделать подобный свод данных из листа ReportBR...ReportBR1...ReportBR2....и тд. из зеленых ячеек в лист1 соответственно что бы макрос каким то образом прописывал формулу "Если".
Воспользовался авто записью макроса при создании формулы:
[vba]
Код
Sub Formula_Property()

'
' Ìàêðîñ1 Ìàêðîñ
'

'
ActiveCell.FormulaR1C1 = _
"=IF(R[-2]C=RC[2],ReportBR1!R[-3]C[4]&"" ""&ReportBR1!R[10]C[13])"
Range("A5").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-2]C=RC[2],ReportBR1!R[-3]C[4]&"" ""&ReportBR1!R[10]C[13],"""")"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[6]C[2]>0,ReportBR1!R[6]C[2],"""")"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[5]C[10]>0,ReportBR1!R[5]C[10],"""")"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[9]C>0,ReportBR1!R[9]C,"""")"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[2]C[-1]>0,ReportBR1!R[2]C[-1],"""")"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[3]C[-2]>0,ReportBR1!R[3]C[-2],"""")"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[4]C[-3]>0,ReportBR1!R[4]C[-3],"""")"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[2]C[5]>0,ReportBR1!R[2]C[5],"""")"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[3]C[4]>0,ReportBR1!R[3]C[4],"""")"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[4]C[3]>0,ReportBR1!R[4]C[3],"""")"
Range("M5").Select
ActiveCell.FormulaR1C1 = _
"=IF(ReportBR1!R[21]C[-2]>0,ReportBR1!R[21]C[-2],"""")"
Range("R5").Select
ActiveCell.FormulaR1C1 = _
"=IF(ReportBR1!R[30]C[-7]>0,ReportBR1!R[30]C[-7],"""")"
Range("L6").Select
End Sub
[/vba]
Мне таким же образом для всех ячеек прописать,не проблема, но может есть проще пусть) многовато писать так. Это всего по листа, а у меня еще таких ячеек 80 кажется.
Попросил бы пожалуйста для начала пример или почитать, хочу сам разобраться прежде чем отвлекать вас.
Всем спасибо.
К сообщению приложен файл: TestN.xlsx (143.8 Kb)


Сообщение отредактировал Santtic - Четверг, 20.02.2020, 16:40
 
Ответить
СообщениеДобрый день. Хочу упростить работу своим коллегам сдачи рапортов.
Специфика такова, никто никогда не знает сколько будет рейсов за одну работу.Для этого я прописал макрос, который делает слепок из фирмы, вставляет его с именем листа "ReportBR" это стартовый, по мере увеличения количества, имя меняется с приставкой 1-2-3...и т.д. Это мне усложнило жизнь,так как я могу собирать и в данный момент этим пользуюсь данные собираю только формулами такого типа
Код
=ЕСЛИ(ReportBR!$E$11>0;ReportBR!$E$11;"")
это с 1го листа
Код
=ЕСЛИ('ReportBR (2)'!$E$11>0;'ReportBR (2)'!$E$11;"")
это со второго листа, ничего заурядного. Но для этого мне надо иметь 10 листов ( максимум что может быть это 10 рейсов). так как при формулах я не могу удалять/добавлять лист, формула бъется при отсутствии листа, пишет #ссылка#и соответственно не работает.
У моего коллеги такой вопрос решен следующим образом: у него макрос при добавлении листа дописывает формулы в новую строку "Лист 1" начиная с 5й строки т.е. 6-7-8....и тд.
Как вы мне посоветуете сделать подобный свод данных из листа ReportBR...ReportBR1...ReportBR2....и тд. из зеленых ячеек в лист1 соответственно что бы макрос каким то образом прописывал формулу "Если".
Воспользовался авто записью макроса при создании формулы:
[vba]
Код
Sub Formula_Property()

'
' Ìàêðîñ1 Ìàêðîñ
'

'
ActiveCell.FormulaR1C1 = _
"=IF(R[-2]C=RC[2],ReportBR1!R[-3]C[4]&"" ""&ReportBR1!R[10]C[13])"
Range("A5").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[-2]C=RC[2],ReportBR1!R[-3]C[4]&"" ""&ReportBR1!R[10]C[13],"""")"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[6]C[2]>0,ReportBR1!R[6]C[2],"""")"
Range("D5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[5]C[10]>0,ReportBR1!R[5]C[10],"""")"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[9]C>0,ReportBR1!R[9]C,"""")"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[2]C[-1]>0,ReportBR1!R[2]C[-1],"""")"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[3]C[-2]>0,ReportBR1!R[3]C[-2],"""")"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[4]C[-3]>0,ReportBR1!R[4]C[-3],"""")"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[2]C[5]>0,ReportBR1!R[2]C[5],"""")"
Range("J5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[3]C[4]>0,ReportBR1!R[3]C[4],"""")"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=IF(ReportBR1!R[4]C[3]>0,ReportBR1!R[4]C[3],"""")"
Range("M5").Select
ActiveCell.FormulaR1C1 = _
"=IF(ReportBR1!R[21]C[-2]>0,ReportBR1!R[21]C[-2],"""")"
Range("R5").Select
ActiveCell.FormulaR1C1 = _
"=IF(ReportBR1!R[30]C[-7]>0,ReportBR1!R[30]C[-7],"""")"
Range("L6").Select
End Sub
[/vba]
Мне таким же образом для всех ячеек прописать,не проблема, но может есть проще пусть) многовато писать так. Это всего по листа, а у меня еще таких ячеек 80 кажется.
Попросил бы пожалуйста для начала пример или почитать, хочу сам разобраться прежде чем отвлекать вас.
Всем спасибо.

Автор - Santtic
Дата добавления - 20.02.2020 в 15:52
Santtic Дата: Пятница, 21.02.2020, 15:06 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 190
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
Всем добрый день. Все что сегодня сделал, так это слил формулы в макросе, что бы они выполнялись одна за одной в свои ячейки. Формулы прописаны под один определенный лист. пробовал сделать под не определенное количество листов, ничего не вышло.
Суть задачи осталась такой:
Как переправит макрос, что бы он искал данные из всех листов которые будут в рапорте ( их может быть и 3 и 2 и 10) начала названия листа будет одинаковое но с нумерацией (2),(3).... и вставлял значения в "Лист1 (2)" начиная с 5й строки. Данные должны собираться рапорт 1 - 5 строка, рапорт 2 - 6 строк ...и т.д.
То что я прописал, как бы и прописывает, но только к первому листу,второй лист не видит и еще добавляет в столбец А какие то значения. Откуда он из берет ума не приложу ( выделено красным).
Пока решилось таким способом определения последней строки вместо [vba]
Код
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Rows(LastRow + 1).Select
[/vba]
прописал [vba]
Код
ange("A5").SpecialCells(xlCellTypeLastCell).Select
[/vba]
Лишнего не прописивает, но не ищет далее по листам.
Всем спасибо.
К сообщению приложен файл: MWD_Raport_END_.xlsm (186.6 Kb)


Сообщение отредактировал Santtic - Пятница, 21.02.2020, 15:51
 
Ответить
СообщениеВсем добрый день. Все что сегодня сделал, так это слил формулы в макросе, что бы они выполнялись одна за одной в свои ячейки. Формулы прописаны под один определенный лист. пробовал сделать под не определенное количество листов, ничего не вышло.
Суть задачи осталась такой:
Как переправит макрос, что бы он искал данные из всех листов которые будут в рапорте ( их может быть и 3 и 2 и 10) начала названия листа будет одинаковое но с нумерацией (2),(3).... и вставлял значения в "Лист1 (2)" начиная с 5й строки. Данные должны собираться рапорт 1 - 5 строка, рапорт 2 - 6 строк ...и т.д.
То что я прописал, как бы и прописывает, но только к первому листу,второй лист не видит и еще добавляет в столбец А какие то значения. Откуда он из берет ума не приложу ( выделено красным).
Пока решилось таким способом определения последней строки вместо [vba]
Код
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Rows(LastRow + 1).Select
[/vba]
прописал [vba]
Код
ange("A5").SpecialCells(xlCellTypeLastCell).Select
[/vba]
Лишнего не прописивает, но не ищет далее по листам.
Всем спасибо.

Автор - Santtic
Дата добавления - 21.02.2020 в 15:06
Santtic Дата: Воскресенье, 23.02.2020, 21:10 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 190
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
Коллеги, может подскажите где почитать или вариант. Те формулы что прописал, они работают, но что бы макрос искал по листам (не известно какое количество их будет) прописывал новую строку, с данными с нового листа.
Просто вариант, это создать 10 макросов с именным диапазоном. Назначить кнопочки и нажимать. Но это не по инженерному))
То что я находил, под мои формулы в макросе естественно не подходит. Везде они прописаны как то по другому.
Всем спасибо.
 
Ответить
СообщениеКоллеги, может подскажите где почитать или вариант. Те формулы что прописал, они работают, но что бы макрос искал по листам (не известно какое количество их будет) прописывал новую строку, с данными с нового листа.
Просто вариант, это создать 10 макросов с именным диапазоном. Назначить кнопочки и нажимать. Но это не по инженерному))
То что я находил, под мои формулы в макросе естественно не подходит. Везде они прописаны как то по другому.
Всем спасибо.

Автор - Santtic
Дата добавления - 23.02.2020 в 21:10
Pelena Дата: Воскресенье, 23.02.2020, 21:49 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Именно формулы нужны или можно просто значения записать в ячейки?
Если формулы, то надо вместо имени листа написать '" & sh.Name & "', типа
[vba]
Код
   Range("A5").FormulaR1C1 = "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[10]C[13],"""")"
    Range("C5").FormulaR1C1 = "=IF('" & sh.Name & "'!R[6]C[2]>0,'" & sh.Name & "'!R[6]C[2],"""")"
[/vba]


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Именно формулы нужны или можно просто значения записать в ячейки?
Если формулы, то надо вместо имени листа написать '" & sh.Name & "', типа
[vba]
Код
   Range("A5").FormulaR1C1 = "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[10]C[13],"""")"
    Range("C5").FormulaR1C1 = "=IF('" & sh.Name & "'!R[6]C[2]>0,'" & sh.Name & "'!R[6]C[2],"""")"
[/vba]

Автор - Pelena
Дата добавления - 23.02.2020 в 21:49
Santtic Дата: Понедельник, 24.02.2020, 10:46 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 190
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
Именно формулы нужны или можно просто значения записать в ячейки?

Добрый день. Спасибо большое за очередной совет. Сегодня к вечеру попробую, что выйдет. Немного текущей работы добавилось...сервисы...
По поводу значения или формулы. Если вас не затруднит, не могли бы вы написать хотя бы для одной ячейки, мне оба варианта пригодятся, мыслей масса,по своей глупости решил вывести всю службу в одно "программное" решение. Такой себе трудоголик, зарплату не повысят и особо не похвалят. А для себя не плохой плюс в резюме.
Очень вам благодарен)
Попробовал ваш вариант, супер, ищет но по последнему листу.
[vba]
Код

Public Sub www()
    Dim sh As Worksheet
    For Each sh In Worksheets
    
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[10]C[13])"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[10]C[13],"""")"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[6]C[2]>0,'" & sh.Name & "'!R[6]C[2],"""")"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[5]C[10]>0,'" & sh.Name & "'!R[5]C[10],"""")"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[9]C>0,'" & sh.Name & "'!R[9]C,"""")"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[2]C[-1]>0,'" & sh.Name & "'!R[2]C[-1],"""")"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[3]C[-2]>0,'" & sh.Name & "'!R[3]C[-2],"""")"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[4]C[-3]>0,'" & sh.Name & "'!R[4]C[-3],"""")"
    Range("I5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[2]C[5]>0,'" & sh.Name & "'!R[2]C[5],"""")"
    Range("J5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[3]C[4]>0,'" & sh.Name & "'!R[3]C[4],"""")"
    Range("K5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[4]C[3]>0,'" & sh.Name & "'!R[4]C[3],"""")"
    Range("M5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R[21]C[-2]>0,'" & sh.Name & "'!R[21]C[-2],"""")"
    Range("R5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R[30]C[-7]>0,'" & sh.Name & "'!R[30]C[-7],"""")"
    Range("S5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3=data!R4C,1,"""")"
    Range("S5").Select
    Selection.AutoFill Destination:=Range("S5:AO5"), Type:=xlFillDefault
    Range("S5:AO5").Select
    Range("AP5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R21C3=data!R4C,1,"""")"
    Range("AP5").Select
    Selection.AutoFill Destination:=Range("AP5:BD5"), Type:=xlFillDefault
    Range("AP5:BD5").Select
    Range("Be5").Select

    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R34C4=data!R4C,1,IF('" & sh.Name & "'!R35C4=data!R4C,1,""""))"
    Range("BE5").Select
    Selection.AutoFill Destination:=Range("BE5:CW5"), Type:=xlFillDefault
    Range("BE5:CW5").Select
    Range("Cx5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R36C4=data!R4C,1,"""")"
    Range("CX5").Select
    Selection.AutoFill Destination:=Range("CX5:ER5"), Type:=xlFillDefault
    Range("CX5:ER5").Select
    Range("Es5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R32C2=data!R4C,1,"""")"
    Range("ES5").Select
    Selection.AutoFill Destination:=Range("ES5:FJ5"), Type:=xlFillDefault
    Range("ES5:FJ5").Select
    Range("Fk5").Select

    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R28C7=data!R4C,1,IF('" & sh.Name & "'!R29C7=data!R4C,1,IF('" & sh.Name & "'!R30C7=data!R4C,1,IF('" & sh.Name & "'!R31C7=data!R4C,1,IF('" & sh.Name & "'!R32C7=data!R4C,1,"""")))))"
    Range("FK5").Select
    Selection.AutoFill Destination:=Range("FK5:IP5"), Type:=xlFillDefault
    Range("FK5:IP5").Select
    Range("Ir5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R20C3=data!R4C,1,"""")"
    Range("IR5").Select
    Selection.AutoFill Destination:=Range("IR5:IW5"), Type:=xlFillDefault
    Range("IR5:IW5").Select
    Range("Ix5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R24C3=data!R4C,1,"""")"
    Range("IX5").Select
    Selection.AutoFill Destination:=Range("IX5:JC5"), Type:=xlFillDefault
    Range("IX5:JC5").Select
    Range("Ji5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R14C14=data!R4C,1,"""")"
    Range("JI5").Select
    Selection.AutoFill Destination:=Range("JI5:JN5"), Type:=xlFillDefault
    Range("JI5:JN5").Select
    Range("JN5").Select

    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[6]C[4])"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[-4]C[13])"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[6]C[2]>0,'" & sh.Name & "'!R[6]C[2],"""")"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R[5]C[10]>0,'" & sh.Name & "'!R[5]C[10]&"" ""&'" & sh.Name & "'!R[6]C[10],"""")"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[9]C>0,'" & sh.Name & "'!R[9]C,"""")"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=tckb('" & sh.Name & "'!R[2]C[-1]>0,'" & sh.Name & "'!R[2]C[-1],"""")"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[2]C[-1]>0,'" & sh.Name & "'!R[2]C[-1],"""")"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[3]C[-2]>0,'" & sh.Name & "'!R[3]C[-2],"""")"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[4]C[-3]>0,'" & sh.Name & "'!R[4]C[-3],"""")"
    Range("I5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[2]C[5]>0,'" & sh.Name & "'!R[2]C[5],"""")"
    Range("J5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[3]C[4]>0,'" & sh.Name & "'!R[3]C[4],"""")"
    Range("K5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[4]C[3]>0,'" & sh.Name & "'!R[4]C[3],"""")"
    Range("M5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[21]C[-2]>0,'" & sh.Name & "'!R[21]C[-2],"""")"
    Range("R5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[30]C[-7]>0,'" & sh.Name & "'!R[30]C[-7],"""")"
    Range("R6").Select
    Range("A5").SpecialCells(xlCellTypeLastCell).Select
    Range("A5").Select
       
     Next sh
        
End Sub

[/vba]
И не добавляет в нижнюю строку последовательно листам.


Сообщение отредактировал Santtic - Понедельник, 24.02.2020, 14:30
 
Ответить
Сообщение
Именно формулы нужны или можно просто значения записать в ячейки?

Добрый день. Спасибо большое за очередной совет. Сегодня к вечеру попробую, что выйдет. Немного текущей работы добавилось...сервисы...
По поводу значения или формулы. Если вас не затруднит, не могли бы вы написать хотя бы для одной ячейки, мне оба варианта пригодятся, мыслей масса,по своей глупости решил вывести всю службу в одно "программное" решение. Такой себе трудоголик, зарплату не повысят и особо не похвалят. А для себя не плохой плюс в резюме.
Очень вам благодарен)
Попробовал ваш вариант, супер, ищет но по последнему листу.
[vba]
Код

Public Sub www()
    Dim sh As Worksheet
    For Each sh In Worksheets
    
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[10]C[13])"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[10]C[13],"""")"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[6]C[2]>0,'" & sh.Name & "'!R[6]C[2],"""")"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[5]C[10]>0,'" & sh.Name & "'!R[5]C[10],"""")"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[9]C>0,'" & sh.Name & "'!R[9]C,"""")"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[2]C[-1]>0,'" & sh.Name & "'!R[2]C[-1],"""")"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[3]C[-2]>0,'" & sh.Name & "'!R[3]C[-2],"""")"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[4]C[-3]>0,'" & sh.Name & "'!R[4]C[-3],"""")"
    Range("I5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[2]C[5]>0,'" & sh.Name & "'!R[2]C[5],"""")"
    Range("J5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[3]C[4]>0,'" & sh.Name & "'!R[3]C[4],"""")"
    Range("K5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[4]C[3]>0,'" & sh.Name & "'!R[4]C[3],"""")"
    Range("M5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R[21]C[-2]>0,'" & sh.Name & "'!R[21]C[-2],"""")"
    Range("R5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R[30]C[-7]>0,'" & sh.Name & "'!R[30]C[-7],"""")"
    Range("S5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3=data!R4C,1,"""")"
    Range("S5").Select
    Selection.AutoFill Destination:=Range("S5:AO5"), Type:=xlFillDefault
    Range("S5:AO5").Select
    Range("AP5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R21C3=data!R4C,1,"""")"
    Range("AP5").Select
    Selection.AutoFill Destination:=Range("AP5:BD5"), Type:=xlFillDefault
    Range("AP5:BD5").Select
    Range("Be5").Select

    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R34C4=data!R4C,1,IF('" & sh.Name & "'!R35C4=data!R4C,1,""""))"
    Range("BE5").Select
    Selection.AutoFill Destination:=Range("BE5:CW5"), Type:=xlFillDefault
    Range("BE5:CW5").Select
    Range("Cx5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R36C4=data!R4C,1,"""")"
    Range("CX5").Select
    Selection.AutoFill Destination:=Range("CX5:ER5"), Type:=xlFillDefault
    Range("CX5:ER5").Select
    Range("Es5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R32C2=data!R4C,1,"""")"
    Range("ES5").Select
    Selection.AutoFill Destination:=Range("ES5:FJ5"), Type:=xlFillDefault
    Range("ES5:FJ5").Select
    Range("Fk5").Select

    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R28C7=data!R4C,1,IF('" & sh.Name & "'!R29C7=data!R4C,1,IF('" & sh.Name & "'!R30C7=data!R4C,1,IF('" & sh.Name & "'!R31C7=data!R4C,1,IF('" & sh.Name & "'!R32C7=data!R4C,1,"""")))))"
    Range("FK5").Select
    Selection.AutoFill Destination:=Range("FK5:IP5"), Type:=xlFillDefault
    Range("FK5:IP5").Select
    Range("Ir5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R20C3=data!R4C,1,"""")"
    Range("IR5").Select
    Selection.AutoFill Destination:=Range("IR5:IW5"), Type:=xlFillDefault
    Range("IR5:IW5").Select
    Range("Ix5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R24C3=data!R4C,1,"""")"
    Range("IX5").Select
    Selection.AutoFill Destination:=Range("IX5:JC5"), Type:=xlFillDefault
    Range("IX5:JC5").Select
    Range("Ji5").Select

    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R14C14=data!R4C,1,"""")"
    Range("JI5").Select
    Selection.AutoFill Destination:=Range("JI5:JN5"), Type:=xlFillDefault
    Range("JI5:JN5").Select
    Range("JN5").Select

    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[6]C[4])"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-2]C=RC[2],'" & sh.Name & "'!R[-3]C[4]&"" ""&'" & sh.Name & "'!R[-4]C[13])"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[6]C[2]>0,'" & sh.Name & "'!R[6]C[2],"""")"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('" & sh.Name & "'!R[5]C[10]>0,'" & sh.Name & "'!R[5]C[10]&"" ""&'" & sh.Name & "'!R[6]C[10],"""")"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[9]C>0,'" & sh.Name & "'!R[9]C,"""")"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=tckb('" & sh.Name & "'!R[2]C[-1]>0,'" & sh.Name & "'!R[2]C[-1],"""")"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[2]C[-1]>0,'" & sh.Name & "'!R[2]C[-1],"""")"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[3]C[-2]>0,'" & sh.Name & "'!R[3]C[-2],"""")"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[4]C[-3]>0,'" & sh.Name & "'!R[4]C[-3],"""")"
    Range("I5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[2]C[5]>0,'" & sh.Name & "'!R[2]C[5],"""")"
    Range("J5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[3]C[4]>0,'" & sh.Name & "'!R[3]C[4],"""")"
    Range("K5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[4]C[3]>0,'" & sh.Name & "'!R[4]C[3],"""")"
    Range("M5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[21]C[-2]>0,'" & sh.Name & "'!R[21]C[-2],"""")"
    Range("R5").Select
    ActiveCell.FormulaR1C1 = "=IF('" & sh.Name & "'!R[30]C[-7]>0,'" & sh.Name & "'!R[30]C[-7],"""")"
    Range("R6").Select
    Range("A5").SpecialCells(xlCellTypeLastCell).Select
    Range("A5").Select
       
     Next sh
        
End Sub

[/vba]
И не добавляет в нижнюю строку последовательно листам.

Автор - Santtic
Дата добавления - 24.02.2020 в 10:46
Pelena Дата: Понедельник, 24.02.2020, 14:50 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Макрос ищет по всем листам, но Вы записываете всё в одну строку, пятую, поэтому остаётся только последний лист.
Вы поясните конкретнее: в ячейку С5 должно пойти значение из ячейки такой-то при условии, что она больше 0. Хотя бы несколько ячеек, потом сделаете по аналогии.
И избавляйтесь от Select-Activecell, это не рационально. Посмотрите, как я записала строчки без Select


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеМакрос ищет по всем листам, но Вы записываете всё в одну строку, пятую, поэтому остаётся только последний лист.
Вы поясните конкретнее: в ячейку С5 должно пойти значение из ячейки такой-то при условии, что она больше 0. Хотя бы несколько ячеек, потом сделаете по аналогии.
И избавляйтесь от Select-Activecell, это не рационально. Посмотрите, как я записала строчки без Select

Автор - Pelena
Дата добавления - 24.02.2020 в 14:50
Santtic Дата: Понедельник, 24.02.2020, 16:12 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 190
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
в ячейку С5 должно пойти значение из ячейки такой-то при условии, что она больше 0.

Вот так вы имели ввиду? Что то я не пойму)
[vba]
Код
Range("C5").FormulaR1C1 = "=IF('" & sh.Name & "'!R[6]C[2]>0,'" & sh.Name & "'!R[6]C[2],"""")"
[/vba]
А по аналогии имелось ввиду вместо А5 указываем А6 и т.д? %)
А как мне сделать что бы макрос писал мне в строку А5 Лист "ReportBR" , в А6 Лист "ReportBR2", в А7 Лист"ReportBR3" и т.д.? Я уже и добавлял ему, что бы прописывал ниже, ничего не меняет)) ясное дело что то я так прописывал))
Не ничего не вышло у меня. Не копирует данные с другого листа в 6 строку...и т.д.
Ткните пожалуйста носом))


Сообщение отредактировал Santtic - Понедельник, 24.02.2020, 21:15
 
Ответить
Сообщение
в ячейку С5 должно пойти значение из ячейки такой-то при условии, что она больше 0.

Вот так вы имели ввиду? Что то я не пойму)
[vba]
Код
Range("C5").FormulaR1C1 = "=IF('" & sh.Name & "'!R[6]C[2]>0,'" & sh.Name & "'!R[6]C[2],"""")"
[/vba]
А по аналогии имелось ввиду вместо А5 указываем А6 и т.д? %)
А как мне сделать что бы макрос писал мне в строку А5 Лист "ReportBR" , в А6 Лист "ReportBR2", в А7 Лист"ReportBR3" и т.д.? Я уже и добавлял ему, что бы прописывал ниже, ничего не меняет)) ясное дело что то я так прописывал))
Не ничего не вышло у меня. Не копирует данные с другого листа в 6 строку...и т.д.
Ткните пожалуйста носом))

Автор - Santtic
Дата добавления - 24.02.2020 в 16:12
Pelena Дата: Понедельник, 24.02.2020, 22:05 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Сделала для столбцов A, C и D. Дальше по аналогии
К сообщению приложен файл: 4176408.xlsm (183.0 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеСделала для столбцов A, C и D. Дальше по аналогии

Автор - Pelena
Дата добавления - 24.02.2020 в 22:05
Santtic Дата: Понедельник, 24.02.2020, 22:15 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 190
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
%) %) %) %) супееер, спасибо огромное, наконец то окончу этот проект. Полтора года выдумывал всякие таблички, формочки, массу переделал всего, подсмотрел на форуме огромнейшее количество решений, подстроил их под себя.
Очень благодарен вам за помощь.
 
Ответить
Сообщение%) %) %) %) супееер, спасибо огромное, наконец то окончу этот проект. Полтора года выдумывал всякие таблички, формочки, массу переделал всего, подсмотрел на форуме огромнейшее количество решений, подстроил их под себя.
Очень благодарен вам за помощь.

Автор - Santtic
Дата добавления - 24.02.2020 в 22:15
Santtic Дата: Среда, 26.02.2020, 17:51 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 190
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
Дальше по аналогии

Добрый день
Подскажите пожалуйста как мне правильно протянуть этуформулу
[vba]
Код
Range("S" & lr).FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3='Лист1 (2)'!R4C,""1"","""")"
    Range("S5").Select
    Selection.AutoFill Destination:=Range("S5:AO5"), Type:=xlFillDefault
    Range("S5:AO5").Select
[/vba]
пробовал убирать как в аналогии номер строк "5" и дописывать & lr, но ничего не дало.
До этого момента работает, Лист 1(2) не убирал имя, так как он не правильно потом прописывает формулу. и не делает сравнения.
[vba]
Код
Range("S" & lr).FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3='Лист1 (2)'!R4C,""1"","""")"
[/vba]
а эту часть макроса дописывает на строку 6 ячейку S и дальше останавливается.
[vba]
Код
Range("S5").Select
    Selection.AutoFill Destination:=Range("S5:AO5"), Type:=xlFillDefault
    Range("S5:AO5")
[/vba]
Убирал привязки к строкам, ничего не вышло.

Кажется вышло.
[vba]
Код
Range("S5:AO" & lr).FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3='Ëèñò1 (2)'!R4C,""1"","""")"
[/vba]


Сообщение отредактировал Santtic - Среда, 26.02.2020, 18:04
 
Ответить
Сообщение
Дальше по аналогии

Добрый день
Подскажите пожалуйста как мне правильно протянуть этуформулу
[vba]
Код
Range("S" & lr).FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3='Лист1 (2)'!R4C,""1"","""")"
    Range("S5").Select
    Selection.AutoFill Destination:=Range("S5:AO5"), Type:=xlFillDefault
    Range("S5:AO5").Select
[/vba]
пробовал убирать как в аналогии номер строк "5" и дописывать & lr, но ничего не дало.
До этого момента работает, Лист 1(2) не убирал имя, так как он не правильно потом прописывает формулу. и не делает сравнения.
[vba]
Код
Range("S" & lr).FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3='Лист1 (2)'!R4C,""1"","""")"
[/vba]
а эту часть макроса дописывает на строку 6 ячейку S и дальше останавливается.
[vba]
Код
Range("S5").Select
    Selection.AutoFill Destination:=Range("S5:AO5"), Type:=xlFillDefault
    Range("S5:AO5")
[/vba]
Убирал привязки к строкам, ничего не вышло.

Кажется вышло.
[vba]
Код
Range("S5:AO" & lr).FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3='Ëèñò1 (2)'!R4C,""1"","""")"
[/vba]

Автор - Santtic
Дата добавления - 26.02.2020 в 17:51
Pelena Дата: Среда, 26.02.2020, 18:02 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
[vba]
Код
Range("S" & lr).Resize(1, 23).FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3=R4C,""1"","""")"
[/vba]


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение[vba]
Код
Range("S" & lr).Resize(1, 23).FormulaR1C1 = "=IF('" & sh.Name & "'!R19C3=R4C,""1"","""")"
[/vba]

Автор - Pelena
Дата добавления - 26.02.2020 в 18:02
Santtic Дата: Среда, 26.02.2020, 18:08 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 190
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
Resize(1, 23)

А это ко всем формулам можно применять в этой строке?
 
Ответить
Сообщение
Resize(1, 23)

А это ко всем формулам можно применять в этой строке?

Автор - Santtic
Дата добавления - 26.02.2020 в 18:08
Pelena Дата: Среда, 26.02.2020, 18:27 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 19162
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Resize растягивает диапазон. В данном случае на 23 столбца с S до AO. То есть формула записывается не в одну ячейку, а сразу в диапазон


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеResize растягивает диапазон. В данном случае на 23 столбца с S до AO. То есть формула записывается не в одну ячейку, а сразу в диапазон

Автор - Pelena
Дата добавления - 26.02.2020 в 18:27
Santtic Дата: Пятница, 13.03.2020, 10:36 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 190
Репутация: 1 ±
Замечаний: 0% ±

Excel 2016
Всем добрый день. Подскажите пожалуйста, если я хочу с четырех листов собрать данные начиная с 17 строки, что мне еще нужно дописать.
Данные должны только дописываться, не перезаписывать, а только дополнять.
На тех листах сбор идет макросом с закрытых книг. А вот как с четырех собрать не пойму. Формулами как ранее мог прописать, но файл будет набираться много лет. Поэтому формулы увы не помогут, только нагружать файл.
Файл обязательно приложить и нужно ли тему новую создавать? Вопрос как бы по сбору данных, поэтому не создавал новую. Если нарушил правила, прошу простить, тему создам если это необходимо.
Спасибо.
[vba]
Код
  Dim sh As Worksheet
    For Each sh In Worksheets
        If sh.Name Like "ПВТР" And sh.Name <> "Стрійське" And sh.Name <> "Красноградське" And sh.Name <> "Шебелинське" Then
            lr = Application.Max(Cells(Rows.Count, 1).End(xlUp).Row + 1, 17)
            
            If IsArray(vData) Then
        [b17].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData
    Else
        [b17] = vData
    End If
            
            Application.ScreenUpdating = True
           
        End If
    Next
End Sub
[/vba]Public Sub www()


Создал тему,так как собрал почти все.


Сообщение отредактировал Santtic - Пятница, 13.03.2020, 16:50
 
Ответить
СообщениеВсем добрый день. Подскажите пожалуйста, если я хочу с четырех листов собрать данные начиная с 17 строки, что мне еще нужно дописать.
Данные должны только дописываться, не перезаписывать, а только дополнять.
На тех листах сбор идет макросом с закрытых книг. А вот как с четырех собрать не пойму. Формулами как ранее мог прописать, но файл будет набираться много лет. Поэтому формулы увы не помогут, только нагружать файл.
Файл обязательно приложить и нужно ли тему новую создавать? Вопрос как бы по сбору данных, поэтому не создавал новую. Если нарушил правила, прошу простить, тему создам если это необходимо.
Спасибо.
[vba]
Код
  Dim sh As Worksheet
    For Each sh In Worksheets
        If sh.Name Like "ПВТР" And sh.Name <> "Стрійське" And sh.Name <> "Красноградське" And sh.Name <> "Шебелинське" Then
            lr = Application.Max(Cells(Rows.Count, 1).End(xlUp).Row + 1, 17)
            
            If IsArray(vData) Then
        [b17].Resize(UBound(vData, 1), UBound(vData, 2)).Value = vData
    Else
        [b17] = vData
    End If
            
            Application.ScreenUpdating = True
           
        End If
    Next
End Sub
[/vba]Public Sub www()


Создал тему,так как собрал почти все.

Автор - Santtic
Дата добавления - 13.03.2020 в 10:36
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос сводных таблиц без привязки имени листа (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!