Профессиональные приемы работы в Microsoft Excel

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

Из правил форума: Тема должна отражать суть вопроса, топики типа "help please" будут удаляться!

Автор Тема: Предыдущий лист в VBA  (Прочитано 6359 раз)

0 Пользователей и 1 Гость просматривают эту тему.

ess711

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 6
Предыдущий лист в VBA
« : 10.01.2009, 21:41:41 »

Добрый день.

Я знаю, что похожая тема уже обсуждалась, но не смог найти примеры в архиве. Задача в следующем: Нужно чтобы данные одной ячейки копировались из данных другой в предыдущем листе. Менять руками долго, т.к. в книге 53 листа. Наименования листов от Я1 (= январь, неделя №1) до Д53 (= декабрь, неделя 53).
Подскажите плиз, как это сделать формулой или VBA.
Записан

IKor

  • Старожил
  • ****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 902
Re: Предыдущий лист в VBA
« Ответ #1 : 10.01.2009, 22:49:53 »

Если я Вас правильно понял, то гораздо проще будет:
1. скопировать целиком лист Я4, ссылающийся на лист Я3,
2. заменить его название на Ф5,
3. выделить все его ячейки
3. в меню ПРАВКА / ЗАМЕНИТЬ (Cntr+H) заменить все вхождения текста Я3! на Я4!

Но если Вы очень хотите сделать единую универсальную формулу для всех листов, то можно воспользоваться сочетанием функций ЯЧЕЙКА("адрес";ссылка_на_ячейку) и ДВССЫЛ(текст;необязательный параметр)
Первая из этих функций вернет Вам текстовую строку с полным адресом ячейки, которую ей передали в качестве второго параметра (если ссылка будет на другой лист, то вернется также и название листа).
Эту строку нужно будет преобразовать в строку с адресом для нового листа и использовать в качестве аргумента функции ДВССЫЛ()

Преобразование строк можно осуществить с помощью текстовых функций:
НАЙТИ("!";ЯЧЕЙКА(...);1) - вернет номер символа, разделяющего имя листа и адрес ячейки на листе
НАЙТИ("]";ЯЧЕЙКА(...);1) - вернет номер символа, разделяющего имя файла и имя листа
ПСТР(текст;НАЙТИ("]";...)+1;НАЙТИ("]";...)-НАЙТИ("!";...)-Константа) - вернет имя листа (Константа зависит от того, есть ли в имени файла или листа пробелы, и если есть, то между "!" и самим именем листа будет апостроф.
С помощью функции ЗАМЕНИТЬ() потребуется подставить новые символы на соответствующие места старой строки.
СОВЕТ_1: откажитесь от "разнобуквенного" обозначения листов и используйте всегда две цифры для обозначения недели, тогда модификация формул станет существенно проще:
функция Т(1+ЗНАЧЕН(ПСТР(текст;НАЙТИ("!";...)-2-Константа;2))) вернет Вам текстовую строку, содержащую номер на единицу больщий, нежели чем номер листа ссылки.
СОВЕТ_2: Сформируйте где-нибудь список названий листов, определяйте название текущего листа, с помощью функции ПОИСКПОЗ(найденое_имя;список_названий_листов) находите его номер в списке, после чего с помощью функции ИНДЕКС(список_названий_листов;ПОИСКПОЗ(...)+1) возвращайте имя следующего листа

Таким образом вместо простой записи:
=Я03!А1
потребуется записать
=ДВССЫЛ(Сложная_формула_преобразования_текста(ЯЧЕЙКА("адрес";Я03!А1 )))

Удачи!
Надеюсь, что помог.
Записан

Шпец Докапыч

  • Ветеран
  • *****
  • Уважение: +189/-4
  • Оффлайн Оффлайн
  • Сообщений: 1 919
  • ОБЛАДАТЕЛЬ УНИКАЛЬНЫХ НАВЫКОВ!!! :)
Re: Предыдущий лист в VBA
« Ответ #2 : 10.01.2009, 23:02:49 »

=ДВССЫЛ(Сложная_формула_преобразования_текста(ЯЧЕЙКА("адрес";Я03!А1 )))

Я было тоже хотел сначала сообразить через ДВССЫЛ(ЯЧЕЙКА()), но не придумал как сослаться на предыдущий лист. К тому же, почему-то решил, что у ess711 именно ОДНА ячейка со ссылкой. Короче, это предыстория.

Поэтому решил пойти другим путём (через ПФ). Вот инструкция:
1) Бросаем в обычный модуль код пользовательской функции:
Function СОСЕДЛИСТ(Ссылка As Range, Смещение As Long)
   Application.Volatile
   СОСЕДЛИСТ = Sheets(Ссылка.Parent.Index + Смещение).Range(Ссылка.Address).Value
End Function

2) Удерживая Shift, щёлкаем по ярлычкам крайних листов "Я1" и "Д53". В заголовке окна должно дописаться "…[Группа]"

3) Пишем формулу, например, в "A1":
  • =соседлист(A5;-1)
Записан
Знания недостаточно, необходимо применение. Желания недостаточно, необходимо действие. (с) Брюс Ли

ess711

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 6
Re: Предыдущий лист в VBA
« Ответ #3 : 11.01.2009, 06:12:46 »

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

А можно через код сделать чтобы,

например ячейка A1 листа Я1 книги 1 ссылалась на ячейку B1 листа Я1 в книге 2. Т.е автоматом для всех листов. Обязательное условие чтобы имена листов совпадали, хотя это наверное не важно, так как последовательность листов в книгах одинакова.

Записан

Goorito

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 3
Re: Предыдущий лист в VBA
« Ответ #4 : 08.04.2019, 08:13:16 »

удален
« Последнее редактирование: 15.04.2019, 20:41:00 от Goorito »
Записан

_Boroda_

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +415/-0
  • Оффлайн Оффлайн
  • Сообщений: 2 552
Re: Предыдущий лист в VBA
« Ответ #5 : 09.04.2019, 09:19:07 »

Приложите файл к сообщению. Об этом в Правилах форума написано
Записан
Скажи мне, кудесник, любимец ба’гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

Goorito

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 3
Re: Предыдущий лист в VBA
« Ответ #6 : 11.04.2019, 06:51:53 »

Приложите файл к сообщению. Об этом в Правилах форума написано

как это сделать, научите.
Не смог найти кнопку для вложений
Записан

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 005
Re: Предыдущий лист в VBA
« Ответ #7 : 11.04.2019, 09:07:22 »

Под полем для ввода сообщения:
Вложения и другие параметры - Выберите файл
Записан

Goorito

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 3
Re: Предыдущий лист в VBA
« Ответ #8 : 12.04.2019, 08:51:20 »

удален, не актуально
« Последнее редактирование: 15.04.2019, 20:41:34 от Goorito »
Записан

boa

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +32/-0
  • Онлайн Онлайн
  • Сообщений: 583
  • Доброта спасет мир...
Re: Предыдущий лист в VBA
« Ответ #9 : 15.04.2019, 10:55:32 »

Здесь ответ на ваш вопрос
в принципе, тоже, что тут написано.
« Последнее редактирование: 15.04.2019, 11:08:22 от boa »
Записан
Ничто не обходится нам так дешево и не ценится так дорого, как вежливость...  Мигель Сервантес де Сааведра
 



Темы без ответов

18.07.2019 16:02 Рассылка почты из Excel при помощи почтовой программы TheBAT! 50
09.07.2019 20:39 Кредит с уменьшением периода выплат 72
28.05.2019 21:09 Сделать несколько скриптов для рабочей таблицы 360
05.03.2019 17:00 Последовательный вывод таблиц Excel в один документ Word без шаблона 676
05.03.2019 09:29 Нежелательные изменение размеров колонтитула при редактировании 479
07.02.2019 01:36 Как удалить дубликаты из выпадающего связанного списка? 619
20.01.2019 12:38 Все варианты частичного суммирования 765
13.01.2019 12:24 Заполнение диапазона числами - в виде кластеров 623
30.09.2018 10:24 Расчет процентов за определенный период (месяц) с учетом изменений и платежей 1047
03.03.2018 00:00 Подсчет отработанного времени, за исключением заранее определенных перерывов 1839





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.192 секунд. Запросов: 119.