Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 10.01.2012, 21:07   #1
Enigmatic
Пользователь
 
Регистрация: 26.11.2009
Сообщений: 22
По умолчанию Автоматическое подставление данных

Добрый день!
Прошу помощи для создания макроса при решении следующей задачи:
необходимо, чтобы при поставлении шестизначного почтового индекса на листе 1 в столбце "индекс" после нажатия Enter (или другого сочетания клавиш) автоматически в соответствующие ячейки подставлялись данные с листа 2 - область, район (если имеется), нас. пункт (если имеется). То есть своего рода происходил поиск по индексу. База на Листе 2 будет содержать в себе 1100 индексов.
Вложения
Тип файла: rar primer.rar (7.0 Кб, 35 просмотров)
Enigmatic вне форума Ответить с цитированием
Старый 10.01.2012, 21:56   #2
motorway
Участник клуба
 
Регистрация: 28.06.2009
Сообщений: 1,950
По умолчанию

Поместите в B2 формулу
Код:
=ИНДЕКС(Лист2!B:B;ПОИСКПОЗ($A$2;Лист2!$A:$A;0)) и протяните. В общем случае могут возникнуть нули, если пустая ячейка. Их можно дополнительно обработать
Например, так:
Код:
=ЕСЛИ(ЕТЕКСТ(ИНДЕКС(Лист2!B:B;ПОИСКПОЗ($A$2;Лист2!$A:$A;0)));ИНДЕКС(Лист2!B:B;ПОИСКПОЗ($A$2;Лист2!$A:$A;0));"")
Если помог, буду благодарен за символичную сумму на кошелёк

Последний раз редактировалось motorway; 10.01.2012 в 22:00.
motorway вне форума Ответить с цитированием
Старый 11.01.2012, 02:19   #3
Enigmatic
Пользователь
 
Регистрация: 26.11.2009
Сообщений: 22
По умолчанию

Цитата:
Сообщение от motorway Посмотреть сообщение
Поместите в B2 формулу
Код:
=ИНДЕКС(Лист2!B:B;ПОИСКПОЗ($A$2;Лист2!$A:$A;0)) и протяните. В общем случае могут возникнуть нули, если пустая ячейка. Их можно дополнительно обработать
motorway, спасибо! Это работает. Нули отключил средствами самого Excel.
НО. Как сделать так, чтобы было можно дописывать нужные индексы вниз, а не только в А2? Изменять параметр в формулах для всех строк нереально (строк будет больше 2000). То есть нужно сделать так, чтобы индекс мог определяться из соседней ячейки любой строки
Вложения
Тип файла: rar primer.rar (9.0 Кб, 13 просмотров)
Enigmatic вне форума Ответить с цитированием
Старый 11.01.2012, 02:32   #4
IgorGO
Новичок
СтарожилДжуниор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Сообщений: 9,487
По умолчанию

потяните вниз вот это:
Код:
=ИНДЕКС(Лист2!$B:$B;ПОИСКПОЗ(A2;Лист2!$A:$A;0))
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO вне форума Ответить с цитированием
Старый 11.01.2012, 13:18   #5
Enigmatic
Пользователь
 
Регистрация: 26.11.2009
Сообщений: 22
По умолчанию

Цитата:
Сообщение от IgorGO Посмотреть сообщение
потяните вниз вот это:
Код:
=ИНДЕКС(Лист2!$B:$B;ПОИСКПОЗ(A2;Лист2!$A:$A;0))
IgorGO, не то. Искомое значение-то все равно остается А2.
К примеру, если мне надо будет вписать индекс в А5. Не исправлять же каждый раз при протягивании формулы ПОИСКПОЗ(A2 на А3, на А4, на А5 и т.д.)
Enigmatic вне форума Ответить с цитированием
Старый 11.01.2012, 13:27   #6
IgorGO
Новичок
СтарожилДжуниор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Сообщений: 9,487
По умолчанию

Рождество время всяких чудес, возможно поэтому...
вы уверены?
1. допустим в С2 вписали это
Код:
=ИНДЕКС(Лист2!$B:$B;ПОИСКПОЗ(A2;Лист2!$A:$A;0))
2. наступили мышкой на С2
3. в правом нижнем уголке появилась черная метка квадратной формы - тянете за нее вниз
4. вы хотите сказать, что в С3 не получилось
Код:
=ИНДЕКС(Лист2!$B:$B;ПОИСКПОЗ(A3;Лист2!$A:$A;0))
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO вне форума Ответить с цитированием
Старый 11.01.2012, 13:28   #7
Watcher_1
Форумчанин
 
Аватар для Watcher_1
 
Регистрация: 22.06.2011
Сообщений: 325
По умолчанию

Ловите с помощью макроса
Забивайте индекс и любуйтесь на результат...
Вложения
Тип файла: rar primer.rar (8.8 Кб, 22 просмотров)
Заказать макрос можно на сайте http://excel4you.ru/
Watcher_1 вне форума Ответить с цитированием
Старый 11.01.2012, 14:59   #8
Hugo121
Старожил
 
Регистрация: 11.05.2010
Сообщений: 5,166
По умолчанию

В стандартный модуль (создать):

Код:
Public oDict As Object
В модуль книги:

Код:
Private Sub Workbook_Open()
    Dim a(), i&
    a = Sheets(2).UsedRange.Value
    Set oDict = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(a)
        If Len(a(i, 1)) Then
            ReDim b(2)
            b(0) = a(i, 2)
            b(1) = a(i, 3)
            b(2) = a(i, 4)
            oDict.Item(a(i, 1)) = b
        End If
    Next
End Sub
В модуль листа:

Код:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column = 1 Then
        If oDict.exists(Target.Value) Then
            Application.EnableEvents = False
            Target.Offset(, 1).Resize(, 3) = oDict.Item(Target.Value)
            Application.EnableEvents = True
        End If
    End If
End Sub
Работает мнгновенно.
Если список индексов может в процессе работы меняться - тогда или открыть книгу заново, или написать код для создания словаря индексов заново после изменения списка.

Причём индекс может быть любой длины, может и вообще не быть индексом. Хот просто словом, которому нужно справа поставить соответствие.
webmoney: E265281470651 Z422237915069 R418926282008

Последний раз редактировалось Hugo121; 11.01.2012 в 15:03.
Hugo121 вне форума Ответить с цитированием
Старый 13.01.2012, 19:48   #9
Enigmatic
Пользователь
 
Регистрация: 26.11.2009
Сообщений: 22
По умолчанию

Цитата:
Сообщение от IgorGO Посмотреть сообщение
Рождество время всяких чудес, возможно поэтому...
вы уверены?
1. допустим в С2 вписали это
Код:
=ИНДЕКС(Лист2!$B:$B;ПОИСКПОЗ(A2;Лист2!$A:$A;0))
2. наступили мышкой на С2
3. в правом нижнем уголке появилась черная метка квадратной формы - тянете за нее вниз
4. вы хотите сказать, что в С3 не получилось
Код:
=ИНДЕКС(Лист2!$B:$B;ПОИСКПОЗ(A3;Лист2!$A:$A;0))
Все протянулось, все работает. Когда не вписан индекс возникает ошибка недопустимого значения (убрал с помощью (ЕСЛИОШИБКА). Но из этих ячеек макрос берет данные. В общем получается, что копируются сами формулы.

Цитата:
Сообщение от Watcher_1 Посмотреть сообщение
Ловите с помощью макроса
Забивайте индекс и любуйтесь на результат...
Вариант рабочий, но не смог адаптировать его под свой конкретный документ экселя из данного примера.

Цитата:
Сообщение от Hugo121 Посмотреть сообщение
В стандартный модуль (создать):
............
Если список индексов может в процессе работы меняться - тогда или открыть книгу заново, или написать код для создания словаря индексов заново после изменения списка.

Причём индекс может быть любой длины, может и вообще не быть индексом. Хот просто словом, которому нужно справа поставить соответствие.
После часового тырканья воткнул код в свой эксель, все отлично работает, спасибо!
Enigmatic вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

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


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
подставление данных относительно сегодняшней даты johny_03 Microsoft Office Excel 12 21.12.2011 15:38
Автоматическое изменение данных в ячейке cheshka Microsoft Office Excel 2 19.12.2011 13:51
Автоматическое обновление данных внутри TEdit wadzik C++ Builder 1 08.07.2011 22:32
Автоматическое обновление блока данных Jakethefish PHP 4 05.03.2011 22:37
Автоматическое заполнение данных из другого листа usgaz@list.ru Microsoft Office Excel 8 17.10.2008 10:41