Поиск значений по всему документу по первым буквам при вводе

Дата: Воскресенье, 09.02.2014, 18:59 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Доброго времени суток!

На днях задался вопросом замены штатного поиска Excel, с возможностью поиска по всем листам книги и просмотра найденных результатов по мере ввода данных в поисковую строку.
Но по скольку в VBA я не силен, отправился на поиски решения этого вопроса на этом форуме

К моему сожалению в темах форума решения я не нашел (может плохо искал а может действительно подходящего нет). Отчаявшись, я уже и не надеялся найти решение.. Но, путешествуя по разделам форума (в поисках совершенно другой информации), в разделе "Полезные приемы" наткнулся на готовое решение, безвозмездно выложенное одним из авторов

Вот ссылка на материал: "Поиск значений в базе данных по первым буквам при вводе на листе (как в 1С)"
В данном решении поиска выдаются все найденные результаты по мере ввода текста, после того как мы нашли то что нам нужно, нажимаем на результат и переходим в ячейку с искомым значением.
Вопрос: Как расширить диапазон поиска и сделать такой поиск по всем ячейкам, на всех листах книги ?

Креплю файлы примеры, мой, и файл того самого готового решения:

К сообщению приложен файл: my_file.xlsm (41.2 Kb) · 27_.xlsm (95.1 Kb)

Сообщение отредактировал sashagerych - Воскресенье, 09.02.2014, 19:00

СообщениеДоброго времени суток!

Автор - sashagerych
Дата добавления - 09.02.2014 в 18:59

Дата: Понедельник, 10.02.2014, 15:05 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата sashagerych, 09.02.2014 в 18:59, в сообщении № 1

после того как мы нашли то что нам нужно, нажимаем на результат и переходим в ячейку с искомым значением.

Вариант для размышления:

К сообщению приложен файл: SKLAD.xls (39.0 Kb)

Дата: Понедельник, 10.02.2014, 18:18 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Wasilic,
Спасибо, что обратили свое внимание на мой вопрос

Цитата

Вариант для размышления:

Мне понравилось отображение в результате поиска "Имя листа" на котором было найдено искомое значение, отображение ячейки- думаю это лишнее, хотя можно и оставить. Вот только хотелось бы видеть результаты поиска сразу во время набора искомого значения, с отображением предполагаемых найденных результатов как в готовом решении: Поиск значений в базе данных по первым буквам при вводе на листе (как в 1С)

На сколько я понял, диапазон

Код

(D6:D60)

указанный в макросе вашего варианта служит для вывода нескольких искомых результатов, если такие имеются. Но, когда я ввожу предположим первые значения "код товара" из вещевого склада, таблица "Детская одежда" VSD не указывая номер, мне поиск выдает только один результат: имя листа, и ячейка O10, когда в этой таблице имеется три ячейки в которых значение начинается на VSD.
Новые строки добавленные в таблицу и заполненные новой информацией поиск не находит.
И все же хочется не отходить далеко от модели поиска предложенной здесь
1. Нет кнопки "Найти"
2. Вывод найденных результатов происходит во время набора текста в поисковой строке.

Wasilic,
Спасибо, что обратили свое внимание на мой вопрос

Цитата

Вариант для размышления:

Код

(D6:D60)

СообщениеWasilic,
Спасибо, что обратили свое внимание на мой вопрос

Цитата

Вариант для размышления:

Код

(D6:D60)

Автор - sashagerych
Дата добавления - 10.02.2014 в 18:18

Дата: Понедельник, 10.02.2014, 22:30 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата sashagerych, 10.02.2014 в 18:18, в сообщении № 3

Мне понравилось отображение в результате поиска "Имя листа" на котором было найдено искомое значение, отображение ячейки- думаю это лишнее, хотя можно и оставить.

Это не для того что бы "понравилось", а для того, чтобы найти лист и ячейку.
В общем, в связи с тем что -

Цитата sashagerych, 09.02.2014 в 18:59, в сообщении № 1

по скольку в VBA я не силен

размышлений, видимо, не будет.
И все же, еще вариант. Он же, последний.

К сообщению приложен файл: SKLAD_2.xls (74.0 Kb)

Дата: Вторник, 11.02.2014, 19:24 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Здравствуйте Wasilic.
Посмотрел Ваш второй вариант. Работает нормально. Но, вводимые значения находит только с учетом регистра. Это можно исправить?
И еще вопрос: возможно ли и в Вашем втором примере сделать ввод искомых значений в TextBox на рабочем листе, а вывод найденных результатов в ListBox на рабочем листе, или в форме как в оригинальном решении о котором я писал выше?

Цитата

Это не для того что бы "понравилось", а для того, чтобы найти лист и ячейку.

Просто посмотрев ваш пример, я подумал, что не плохо было бы видеть одновременно в результате поиска предполагаемое искомое значение и имя листа на котором оно находится

Цитата

размышлений, видимо, не будет.

К моему сожалению... Ибо если бы я был силен в программировании в VBA я не стал бы надоедать людям на форуме.

Дата: Среда, 12.02.2014, 14:25 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата sashagerych, 11.02.2014 в 19:24, в сообщении № 5

не плохо было бы видеть одновременно в результате поиска предполагаемое искомое значение и имя листа на котором оно находится

Имя листа в колонке D, а она скрыта. Отобразите её.

Цитата sashagerych, 11.02.2014 в 19:24, в сообщении № 5

возможно ли и в Вашем втором примере сделать ввод искомых значений в TextBox на рабочем листе, а вывод найденных результатов в ListBox на рабочем листе, или в форме как в оригинальном решении о котором я писал выше?

Можно но, на каждом отдельном листе. А в моем примере, виден результат сразу на всех листах. Чем он хуже? Переделывать пока нет времени.

Сообщение отредактировал Wasilic - Среда, 12.02.2014, 14:32

ikki

Дата: Среда, 12.02.2014, 17:13 | Сообщение № 7

Группа: Друзья

Ранг: Старожил

Сообщений: 1906

Репутация: 504 ±

Замечаний: 0% ±

Excel 2003, 2010

Цитата sashagerych, 11.02.2014 в 19:24, в сообщении № 5

Цитата размышлений, видимо, не будет.

К моему сожалению... Ибо если бы я был силен в программировании в VBA я не стал бы надоедать людям на форуме.

таки есть ещё вариант - научиться

офф. "генераторов идей" - как собак нерезаных, а исполнителей - "к сожалению..."

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki

Сообщение отредактировал ikki - Среда, 12.02.2014, 17:14

Дата: Четверг, 13.02.2014, 15:10 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата sashagerych, 11.02.2014 в 19:24, в сообщении № 5

вводимые значения находит только с учетом регистра. Это можно исправить?

Можно очень многое и в разных вариантах. Как написал ikki

Цитата ikki, 12.02.2014 в 17:13, в сообщении № 7

таки есть ещё вариант - научиться

[vba]

Код

Private Sub TextBox1_Change()
    Dim j As Long, i As Long, poisk As Range, iAdr$, LT%
    ListBox1.Clear
    LT = Len(TextBox1.Value)
    If LT = 0 Then Exit Sub
    j = 0
    iAdr = ActiveSheet.UsedRange.Address
    iAdr = Mid(iAdr, InStr(iAdr, ":") + 1)
    For Each poisk In ActiveSheet.Range("A10:" & iAdr)
       If UCase(Left(poisk, LT)) = UCase(TextBox1.Value) Then
          ListBox1.AddItem poisk.Address
          ListBox1.List(j, 1) = poisk
          j = j + 1
       End If
     Next
End Sub
Private Sub ListBox1_Click()
      If ListBox1.ListIndex = -1 Then Exit Sub
      Range(ListBox1).Select
End Sub

[/vba]

МОДЕРАТОРАМ. Не крепится файл xls. Пишет - "Недопустимый формат изображения."

Сообщение отредактировал Wasilic - Четверг, 13.02.2014, 15:25

Дата: Четверг, 13.02.2014, 19:14 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Wasilic,

Цитата

Имя листа в колонке D, а она скрыта. Отобразите её.

Извините за мою невнимательность

Потерялся понимаешь в своих размышлениях и даже не обратил внимания что колонки D и E на листе были скрыты

Цитата

Чем он хуже?

Я не писал что он хуже, просто не хотел в рабочий документ добавлять лишний лист для поиска.

Для удобства пользования поиском думал будет луче чтобы человек не прыгал с листа на первый лист с поиском а мог ввести искомое значение на этом же листе.

Буду пробовать Ваш второй вариант приспособить под себя

Цитата

вводимые значения находит только с учетом регистра

Или это не решаемо?

P.S. спасибо большое за помощь и внимание уделенное этой теме.

Сообщение отредактировал sashagerych - Четверг, 13.02.2014, 19:21

Дата: Четверг, 13.02.2014, 19:19 | Сообщение № 10

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата

МОДЕРАТОРАМ. Не крепится файл xls. Пишет - "Недопустимый формат изображения."

У меня были проблемы с добавлением ответа

Немного не успел написать

Сейчас посмотрю код

Дата: Пятница, 14.02.2014, 00:25 | Сообщение № 11

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Посмотрел код и... Точно, нужно учится программировать в VBA

Все работает отлично, если не брать во внимание тот момент что результат поиска выдает только имя ячейки... Что-то я не то намудрил наверное

К сообщению приложен файл: Poisk_1.xls (60.0 Kb)

Сообщение отредактировал sashagerych - Пятница, 14.02.2014, 00:26

Дата: Пятница, 14.02.2014, 10:35 | Сообщение № 12

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата sashagerych, 14.02.2014 в 00:25, в сообщении № 11

результат поиска выдает только имя ячейки

К сообщению приложен файл: 2157067.jpg (56.1 Kb) · Poisk_12.xls (59.0 Kb)

Сообщение отредактировал Wasilic - Пятница, 14.02.2014, 10:39

Дата: Пятница, 14.02.2014, 14:55 | Сообщение № 13

Группа: Пользователи

Ранг: Новичок

Сообщений: 16

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Wasilic, Спасибо Вам за помощь! Сделал как показано на скриншоте- все заработало, поиск работает отлично!

Есть маленькая просьба, если Вам не трудно, объясните как связаны Columnwidths (я так понял это ширина колонок, только не понял ширина каких колонок) и Columncount (количество колонок) с отображением искомых значений в ListBox?