Excel макрос на одну ячейку вниз
Люди добрые, подскажите, как перейти на ячейку вправо(или влево)
Делаю через
Стр = ActiveCell.Row
Кол = ActiveCell.Column (в Watch эта переменная - число, поэтому
потом после ее подстановки в Range(Кол+1, Стр).Select комп ругается на синтаксис.
Переход по такому же принципу по строкам проходит на ура, а вот со столбцами проблема.
Извиняйте, если туповат.
Люди добрые, подскажите, как перейти на ячейку вправо(или влево)
Делаю через
Стр = ActiveCell.Row
Кол = ActiveCell.Column (в Watch эта переменная - число, поэтому
потом после ее подстановки в Range(Кол+1, Стр).Select комп ругается на синтаксис.
Переход по такому же принципу по строкам проходит на ура, а вот со столбцами проблема.
Извиняйте, если туповат. Ded George
activecell.offset(1,0) - смещение вниз на одну строку
activecell.offset(0,1) - смещение вправо на один столбец
activecell.offset(-1,0) - смещение вверх на одну строку
activecell.offset(0,-1) - смещение влево на один столбец
activecell.offset(1,0) - смещение вниз на одну строку
activecell.offset(0,1) - смещение вправо на один столбец
activecell.offset(-1,0) - смещение вверх на одну строку
activecell.offset(0,-1) - смещение влево на один столбец
activecell.offset(1,0) - смещение вниз на одну строку
activecell.offset(0,1) - смещение вправо на один столбец
activecell.offset(-1,0) - смещение вверх на одну строку
activecell.offset(0,-1) - смещение влево на один столбец
Спасибо всем, кто откликнулся.
Особый респект RAN.
Cells(Стр, Кол+1).Select - (вместо Range. ) - ЭТО ТО, ЧТО НАДО!
Кстати, какая разница между Cells и Range?
Спасибо всем, кто откликнулся.
Особый респект RAN.
Cells(Стр, Кол+1).Select - (вместо Range. ) - ЭТО ТО, ЧТО НАДО!
Кстати, какая разница между Cells и Range? Ded George
А хелп слабО почитать?
Range - это свойство (property) некоего объекта, возвращающее некий диапазон (возможно, мультинабор) ячеек. Параметром является ИМЯ диапазона в нотации макроязыка (как в формулах листа). Или два параметра - задающие углы диапазона. В нотации макроязыка. Что аналогично использованию параметра-объекта в коде VBA.
Cells - это свойство только листа или диапазона (а Application.Cells=ActiveSheet.Cells), позволяющее обратиться к диапазону ячеек именно этого объекта (листа или диапазона) по индексу строки/столбца. Cells позволяет обратиться к ячейке по индексу, НО - только потому, что таково дефолтное свойство диапазона: .Cells(x,y)==.Range.Item(x,y). С другой стороны, можно вызвать .Range(.Cells(. )[,.Cells(. )]) - опять же, только потому, что Cells() возвращает именно объект-диапазон.
Ну и не забывайте, что использование Range и Cells без квалификатора принадлежности объекту - требует очень сильного самоконтроля и отличного знания объектной модели (про что обычно забывают многие "пейсатели" руководств) С другой стороны, точное понимание взаимодействия свойств объектной модели - это один из признаков хорошего программера на любом объектном языке.
А хелп слабО почитать?
Range - это свойство (property) некоего объекта, возвращающее некий диапазон (возможно, мультинабор) ячеек. Параметром является ИМЯ диапазона в нотации макроязыка (как в формулах листа). Или два параметра - задающие углы диапазона. В нотации макроязыка. Что аналогично использованию параметра-объекта в коде VBA.
Cells - это свойство только листа или диапазона (а Application.Cells=ActiveSheet.Cells), позволяющее обратиться к диапазону ячеек именно этого объекта (листа или диапазона) по индексу строки/столбца. Cells позволяет обратиться к ячейке по индексу, НО - только потому, что таково дефолтное свойство диапазона: .Cells(x,y)==.Range.Item(x,y). С другой стороны, можно вызвать .Range(.Cells(. )[,.Cells(. )]) - опять же, только потому, что Cells() возвращает именно объект-диапазон.
Ну и не забывайте, что использование Range и Cells без квалификатора принадлежности объекту - требует очень сильного самоконтроля и отличного знания объектной модели (про что обычно забывают многие "пейсатели" руководств) С другой стороны, точное понимание взаимодействия свойств объектной модели - это один из признаков хорошего программера на любом объектном языке. AndreTM
А хелп слабО почитать?
Range - это свойство (property) некоего объекта, возвращающее некий диапазон (возможно, мультинабор) ячеек. Параметром является ИМЯ диапазона в нотации макроязыка (как в формулах листа). Или два параметра - задающие углы диапазона. В нотации макроязыка. Что аналогично использованию параметра-объекта в коде VBA.
Cells - это свойство только листа или диапазона (а Application.Cells=ActiveSheet.Cells), позволяющее обратиться к диапазону ячеек именно этого объекта (листа или диапазона) по индексу строки/столбца. Cells позволяет обратиться к ячейке по индексу, НО - только потому, что таково дефолтное свойство диапазона: .Cells(x,y)==.Range.Item(x,y). С другой стороны, можно вызвать .Range(.Cells(. )[,.Cells(. )]) - опять же, только потому, что Cells() возвращает именно объект-диапазон.
Ну и не забывайте, что использование Range и Cells без квалификатора принадлежности объекту - требует очень сильного самоконтроля и отличного знания объектной модели (про что обычно забывают многие "пейсатели" руководств) С другой стороны, точное понимание взаимодействия свойств объектной модели - это один из признаков хорошего программера на любом объектном языке. Автор - AndreTM
Дата добавления - 13.11.2014 в 20:14
Добрый день! Помогите пожалуйста.
Нужно назначить макрос на клавишу который бы копировал диапазон ячеек (A1:P1) и вставлял в ячейку например - А25 если она пустая, если нет то опускался ниже (А26) и вставлял сюда.
Объединение ячеек со смещением вниз
Всем привет, подскажите пожалуйста как в VBA сделать объединение ячеек со смещение на одну позицию.
Копирование диапазона ячеек с гиперссылками
Добрый вечер! Подскажите, пожалуйста, как скопировать из одного документа excel в другой диапазон.
Копирование диапазона ячеек по условию
Здравствуйте! Не могу понять почему не работает, что я не так тут делаю: Sub.
Ещё один подвариант, учитывающий, что ячейка [A25] может быть пуста :
Range("A1:P1").Copy Cells(Application.Max(25, Cells(Rows.Count, 1).End(xlUp)(2).Row), 1)
Спасибо огромное! То что надо.
Еще маленький вопрос: Как эти скопированные данные указать, что копировать только значения, а то копирует и значение и формат ячеек?
Можно записать с помощью инструмента записи макросов действия "Специальная вставка -> Значения" и получить готовый код для копирования только значений:
Далее вы можете удалить лишнее, добавить код от pashulka и. программа готова!
Решение
Вариант без копирования :
Еще один вопрос может поможете еще.
Как скопировать диапазон А4:AG22 и вставить только значения в ячейку с группировкой строк (А5:A22) - в А25 если она пустая, если нет то опускался ниже (А26) и вставлял сюда.
Спасибо работает но не так как хотелось бы. Сейчас получается копирует в 25 ячейку и вниз 18 строк и группирует , следущее копирование уже в 26 соответсвенно верхние 18 пропадают получается вложеный-вложеный и так далее список, а хотелось бы что бы 25 строка и к ней группировка 18 строк, потом 44 и к ней группировка 18 строк. Извините наверное плохо объяснил.
Добавлено через 51 минуту
Так вот смещает на нужные строки но группирует все 19, а нужно только 18 без первой, то есть первая всегда видна
Решение
Если проблема кроется только в этом, то, как вариант :
Спасибо.Так как нужно.
Добавлено через 2 часа 22 минуты
Может подскажите, в одной книге работает с 25 строки, а в другой упорно с 43.
Добрый день. Делаю очень большой древовидный список в Excel. Часто приходится перетаскивать ячейку на 1 вправо. A1 -> A2, B3->B4 и т.д. Можно ли как-то делать это действие горячими клавишами? Наверное требуется макрос. Если кто подскажет такой - буду очень благодарен. Спасибо.
Как записать переход по стрелке вниз на одну ячейку, либо вправо - влево - вверх
Добрый день. Необходимо в макросе прописать переход по стрелке вниз на одну ячейку, либо вправо -.
Excel : Выделенную ячейку скопировать параллельно по строке через две соседние ячейки вправо
12. Excel : Выделенную ячейку скопировать параллельно по строке через две соседние ячейки вправо.
Перенос даты из одной ячейки Excel в другую
Имеется книга эксель и в ней находится два столбца. в одном столбце идут ячейки с датами и формат.
Ошибся. A1 -> B2, B3 -> C3 и т.д.
Сделал примерную книгу. Если бы можно было одной клавишей (или простой комбинацией клавиш) перемещать содержимое ячейки в ячейку соседнего столбца справа - это в разы бы ускорило работу.
lehachgtop, вот по такому принципу можно перемещать данные из активной ячейки в ячейку, находящуюся справа:
А как вызывать этот макрос - вам решать. Можно сочетание клавиш сделать. Ещё у листа есть события, например, при двойном клике по ячейке можно вызывать макрос.
А можно с помощью макроса сделать выделение ячеек (фон)? Например я выделил 6 ячеек, жму клавиши, и макрос выделяет заданным цветом фон.
Осталось Вам только продумать, как программа, по каким критериям, будет определять ячейки под заливку.
Хорошо, давайте по другому. Чем Вы будете руководствоваться при выделении вручную? Наличием текста?, символов?, цифр?, комбинациями? Вот это и научите железяку. Она послушная. Например, если у Вас в ячейке встречаеться число 2 - закрасить! немедленно!
А вобще есть еще много способов сделать это-же.
Добавлено через 16 минут
А вобще Вы можете воспользоваться условным форматированием, и, возможно, Вам VBA никчему.
Чем Вы будете руководствоваться при выделении вручную? Наличием текста?, символов?, цифр?, комбинациями? Вот это и научите железяку.
Мне придется выделять исключительно вручную. Надо выделять похожие по смыслу, но разные по написанию слова: велик, байк и велосипед. Как-то автоматизировать это невозможно.
Поэтому я по-прежнему хочу выделять нажатием клавиши (комбинацией клавиш). Наверное для этого нужен макрос. Прошу помочь.
Мне кажется, что можно. Велик, байк и велосипед - а что у них общего? Все это средства передвижения, которые можно обьеденить в группу, например RemedForMovement. Создайте массив - и вперед. Главное - экспериментируйте, иначе Вам и святой Франциск не поможет. Но если Вам нравится в ручную - тоже не плохо. Ручная работа ценится во всем мире.
Добавлено через 2 часа 12 минут
Передышка голове (моей).
Создаем кнопку на активном листе:
Из этого макроса запускаем заливку (а стоило это трудов? Не проще было выделить и выбрать цвет на панели?)
Но раз Вам так этого хотелось - пожалуйста!
До сих пор иногда с улыбкой вспоминаю один из своих первых выездных корпоративных тренингов лет 10 назад.
Представьте: огромный как футбольное поле опенспейс-офис российского представительства международной FMCG-компании. Шикарный дизайн, дорогая оргтехника, дресс-код, экспаты курлыкают по углам - вот это вот все :) В одной из переговорок начинаю двухдневный тренинг продвинутого уровня по текущей тогда версии Excel 2003 для 15 ключевых сотрудников экономического департамента вместе с их руководителем. Знакомимся, расспрашиваю их о бизнес-задачах, проблемах, прошу показать несколько типовых рабочих файлов. Показывают километровой длины выгрузки из SAP, простыни отчетов, которые они по этому всему делают и т.д. Ну, дело знакомое - мысленно прикидываю темы и тайминг, подстраиваюсь под аудиторию. Краем глаза замечаю, как один из участников, демонстрируя кусочек своего отчета, терпеливо тянет ячейку с формулой вниз за черный крестик в правом нижнем углу на несколько тысяч строк, потом проскакивает с лету конец таблицы, тянет обратно и т.д. Не выдержав, прерываю его кёрлинг мышью по экрану и показываю двойной щелчок по черному крестику, объясняя про автозаполнение вниз до упора.
Вдруг понимаю, что в аудитории подозрительно тихо и все как-то странно на меня смотрят. Незаметно окидываю себя взглядом где могу - все ОК, руки-ноги на месте, ширинка застегнута. Мысленно отматываю назад свои последние слова в поисках какой-нибудь жуткой оговорки - не было ничего криминального, вроде бы. После этого главный в группе молча встает, жмет мне руку и с каменным лицом говорит: "Спасибо, Николай. На этом тренинг можно закончить."
Ну, короче говоря, выяснилось, что никто из них не имел понятия про двойной щелчок по черному крестику и автозаполнение. Как-то исторически так сложилось, что некому им было показать такую простую но нужную штуку. Тянули всем отделом формулы вручную на тысячи строк, бедолаги. И тут я. Сцена маслом. Руководитель отдела потом очень просил название их компании никому не озвучивать :)
Несколько раз потом были похожие ситуации, но только с отдельными слушателями - большинство сейчас эту функцию, конечно, знает.
Вопрос в другом. После первой радости от освоения столь прекрасной функции большинство пользователей доходит до понимания того, что у автоматического копирования формул двойным щелчком по черному кресту (маркеру автозаполнения) есть при всех положительных моментах и отрицательные:
- Копирование не всегда происходит до конца таблицы. Если таблица не монолитная, т.е. в соседних столбцах есть пустые ячейки, то не факт, что автозаполнение сработает до конца таблицы. Скорее всего процесс остановится на ближайшей пустой ячейке, не дойдя до конца. Если ниже по столбцу есть занятые чем-то ячейки, то автозаполнение остановится на них совершенно точно.
- При копировании портится дизайн ячеек, т.к. по-умолчанию копируется не только формула, но еще и формат. Для исправления надо щелкать по кнопке параметров копирования и выбирать Только значения (Fill without format) .
- Не существует быстрого способа также удобно протянуть формулу не вниз, а вправо, кроме как тянуть вручную. Двойной щелчок по черному крестику - это только вниз.
Нажмите сочетание клавиш левый Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) . Вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст этих макросов:
- умеют заполнять не только вниз (SmartFillDown), но и вправо (SmartFillRight)
- не портят формат ниже- или справа стоящих ячеек - копируется только формула (значение)
- игнорируют пустые соседние ячейки и копирование происходит именно до конца таблицы, а не до ближайшего разрыва в данных или первой занятой ячейки.
Для пущего удобства можно назначить этим макросам сочетания клавиш, используя кнопку Макросы - Параметры (Macros - Options) там же на вкладке Разработчик (Developer) . Теперь достаточно будет ввести нужную формулу или значение в первую ячейку столбца и нажать заданное сочетание клавиш, чтобы макрос автоматически заполнил весь столбец (или строку):
P.S. Частично проблема с копированием формул до конца таблицы была решена в Excel 2007 с появлением "умных таблиц". Правда, не всегда и не везде они уместны. И вправо Excel самостоятельно копировать так и не научился.
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
- в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Читайте также: