Страницы: 1
RSS
VBA. Замена Индекс Поискпоз макросами с использованием формулы, массивов,словаря
 
Добрый день.
Уважаемые Гуру,  научите пжста как получать на листе "Поиск" значение получаемое в столбце "Результат" не формулой, а макросом. (число строк в исходных таблицах не фиксировано, определяем автоматически)
Как понимаю вариантов реализации множество.
Хотелось бы увидеть макросы с использованием формулы, массивов,словаря ....(кто как умеет)  
 
Доброе время суток
Цитата
ageres1982 написал:
множество
Вариант
Код
Public Function LikeLookup(ByVal keyValue As String) As String
    Dim lRow As Long, pSheet As Worksheet, vData, i As Long, sResult As String
    Set pSheet = ThisWorkbook.Worksheets("All")
    lRow = pSheet.Cells(pSheet.Rows.Count, 4).End(xlUp).Row
    vData = pSheet.Range(pSheet.Cells(2, 3), pSheet.Cells(lRow, 4)).Value
    sResult = "Ошибка"
    For i = 1 To UBound(vData, 1)
        If vData(i, 2) = keyValue Then
            sResult = vData(i, 1)
            Exit For
        End If
    Next
    LikeLookup = sResult
End Function
 
Добрый день!
Код
Sub test()
    arr1 = Sheets("Поиск").Range("A2" & ":A" & Cells(Rows.Count, 1).End(xlUp).Row)
    arr2 = Sheets("All").Range("C2" & ":D" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = LBound(arr1) To UBound(arr1)
        For s = LBound(arr2) To UBound(arr2)
            If arr1(i, 1) = arr2(s, 2) Then
               Sheets("Поиск").Cells(i + 1, 2) = arr2(s, 1)
               Exit For
            End If
        Next
    Next
End Sub
 
Андрей_26, при процедурном подходе - не лучше ли было бы использовать словарь? К чему этот цикл в цикле?
 
Андрей VG Андрей_26, Спасибо огромное.
Андрей_26  - забыл сказать что "Option Explicit"  поэтому переменные нужно явно прописать, и не отработала  4 строчка Что_ищем_ошибка должно быть ошибка.
Андрей VG - если Вам не сложно не могли бы приложить пример с вызовом функции в макросе.  :)
 
 
Андрей VG, Андрей, привет. Но ведь все зависит от количества попыток поиска. Может оказаться что поиск в лоб найдет быстрее, чем формирование словаря по полному объему.
Странно что не предложил запросом сделать, но тс просил
Цитата
ageres1982 написал:
с использованием формулы, массивов,словаря
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Может оказаться что поиск в лоб найдет быстрее, чем формирование словаря по полному объему.
В принципе можно посчитать, но как правило, на малых объёмах инициализация словаря малозаметна, а вот на больших данных фактов - перебор быстро ведёт к тормозам.
 
БМВ - sql запросом тоже приветсвуется :) т.к изначально не было ограничения всё зависит от того кто как умеет  см: (c использованием формулы, массивов,словаря ....(кто как умеет) )
 
Цитата
ageres1982 написал:... переменные нужно явно прописать, и не отработала  4 строчка
Да переменные не объявлял, сделать это ни проблема. На счет 4 строки специально ничего ни делал. Просто показал пример возможной реализации через массивы.
 
Добрый день.

Вариант реализации со словарем.
Предварительно необходимо подключить Microsoft Scripting Runtime
Скрытый текст
Код
Option Explicit
Sub SearchMatch()
    Dim dicData As New Dictionary
    Dim arrSource As Variant
    arrSource = Sheets("All").Range("C2" & ":D" & Cells(Rows.Count, 1).End(xlUp).Row)
    
    Dim i As Long
    For i = LBound(arrSource) To UBound(arrSource)
        dicData.Add arrSource(i, 2), arrSource(i, 1)
    Next i
    
    Dim arrSearch As Variant
    arrSearch = Sheets("Поиск").Range("A2" & ":A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = LBound(arrSearch) To UBound(arrSearch)
        If dicData.Exists(arrSearch(i, 1)) Then
            Sheets("Поиск").Cells(i + 1, 2) = dicData(arrSearch(i, 1))
        Else
            Sheets("Поиск").Cells(i + 1, 2) = "Ошибка"
        End If
    Next i
    
End Sub

Изменено: vokilook - 28.07.2020 14:26:24 (Option explicit)
 
А вот так будет вставлять формулы либо по кругу, либо по порядку.  
 
Андрей VG Андрей_26 БМВ vokilook msi2102 спасибо огромное
 
Уважаемые Гуру а как можно решить пример с использованием запроса sql ?
 
И ещё про PQ не спросили :)
И ещё комментарий - если объявлять так:
Код
    Dim dicData As Object
    Set dicData = CreateObject("Scripting.Dictionary")

то Microsoft Scripting Runtime подключать не надо.
Изменено: Hugo - 29.07.2020 18:17:39
 
Цитата
ageres1982 написал:
sql
Свои исходный файл кладёте в папку c:\path. Распаковываете архив. В любой книге на рабочем листе, вкладка "Данные", кнопка "Существующие подключения" в диалоге выбора указываете odc-файл из архива.
 
Hugo - спасибо взял  на заметку, очень полезно ) про PQ не спросил т.к Leftjoin показан в решении Андрей VG
Андрей VG - огромная благодарность Вам за рабочий вариант с sql , единственное хотелось бы уточнить про первый пример с функцией, как в макросе в цикле пробегаясь по столбцу "Что ищем" передавать Вашей Public Function LikeLookup значение текущей ячейки и в соседней ячейке получать результат...
Цитата
Андрей VG - если Вам не сложно не могли бы приложить пример с вызовом функции в макросе.  
 
Цитата
ageres1982 написал:
как в макросе в цикле пробегаясь по столбцу
Думаю, это станет понятно через неделю после плотного изучения книги навроде "Excel VBA для чайников". И если говорить об использовании в цикле, то лучше сразу изучать пример на словарях с целью использования данных словаря в этой функции.
 
Андрей VG Просто мне хотелось сделать полезную для всех тему с разными работающими примерами. Действитель спасибо огромное за помощь и ценный совет.
Все кто прочитают подобного рода книгу вряд ли будут задавать такие глупые вопросы как я  :)  
 
Давайте продолжим тему
Не было вариантов макросов с использованием формулы ..
 
Цитата
ageres1982 написал:
Не было вариантов макросов с использованием формулы
Что Вы имеете ввиду, если пользовательская формула, то Андрей VG, во 2 сообщении писал, если чтобы макрос вставлял формулы, то я в 11
Изменено: msi2102 - 31.07.2020 11:54:18
 
msi2102 спасибо за ответ, имел ввиду использовать в макросе конструкцию  .Formula = " "  
 
Цитата
ageres1982 написал:
использовать в макросе конструкцию  .Formula = " "  
уберите в моем макросе заполнение второго массива и заполняйте сразу ячейки на листе, через желаемую вами конструкцию и будет Вам счастье. Только макрос будет выполняться дольше, а результат останется тем же.
Страницы: 1
Наверх