Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Главное меню

Предыдущий лист в VBA

Автор ess711, 10.01.2009, 21:41

« назад - далее »

ess711

Добрый день.

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

IKor

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

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

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

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

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

Шпец Докапыч

Цитата: IKor от 10.01.2009, 22: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

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

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

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


Goorito

#4
удален

_Boroda_

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



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

Goorito

Цитата: _Boroda_ от 09.04.2019, 09:19
Приложите файл к сообщению. Об этом в Правилах форума написано

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

vikttur

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

Goorito

#8
удален, не актуально

boa

#9
Здесь ответ на ваш вопрос
в принципе, тоже, что тут написано.
Ничто не обходится нам так дешево и не ценится так дорого, как вежливость...  Мигель Сервантес де Сааведра