Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Получение результатов функции ВПР в ячейке через Макрос
 
Добрый день.
Никак не получается в макросе прописать функцию ВПР чтобы в ячейке вместо формулы выводились уже результаты работы (значение ).
Формулу вставлять получается но есть риск что в дальнейшем при использовании пользователи могут что-то исправить в формуле.
Да и думаю что это должно облегчить файл. При открытии не будут происходить повторные вычисления. В заполненных ячейках уже будет текст.

Чтобы было понятнее опишу порядок действий.
В столбце D листа Заполнять вводятся наименования материалов и согласно им из листа Каталог при помощи ВПР заполняются столбцы B и C.

Почему-то простые формулы типа сложить или умножить выдают в ячейку результаты, а ВПР постоянно выводит ошибку.
С программированием не знаком. Обычно беру готовые решения и переделываю под свои нужды.
А тут зашел в тупик)))

Вот код который я смог найти. Он состоит из двух частей одна из которых выдает ошибку.

Код
Private Sub Worksheet_Change(ByVal Target As Range)
    
    For Each cell In Target   'проходим по всем измененным ячейкам
       If Not Intersect(cell, Range("D8:D10000")) Is Nothing Then  'если изменененная ячейка попадает в диапазон A2:A100

В этой части кода имеется ошибка.
           
Код
With cell.Offset(0, -1)         'вводим в соседнюю справа ячейку дату
               .Value = VLOOKUP([@Наименование], База, 3)
               .EntireColumn.AutoFit  'выполняем автоподбор ширины для столбца B, чтобы дата умещалась в ячейке
            End With
           
Ниже все работает но вставляется только формула. Чего я хотел бы избежать.

             
Код
With cell.Offset(0, -2)         'вводим в соседнюю справа ячейку дату
               .Value = "=IFERROR(VLOOKUP(RC[2],База,2,),"""")"
               .EntireColumn.AutoFit  'выполняем автоподбор ширины для столбца B, чтобы дата умещалась в ячейке
            End With
       End If
    Next cell
End Sub
Изменено: maxscorpio - 9 Июн 2019 12:34:10
 
Синтаксис использования функций листа в коде VBA может выглядеть так
Код
For Each cell In Target
    With WorksheetFunction
        cell.Offset(0, -1) = .IfError(.VLookup(cell, Worksheets("Каталог").Range("База"), 3), "")
    End With
Next
но я бы не стал так делать
Лучше так
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D8:D" & Cells(Rows.Count, "D").End(xlUp).Row)) Is Nothing Then
    Dim cell As Range, Baza As Range, cl As Range
    Set Baza = Worksheets("Каталог").Range("База")
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next
    For Each cell In Target
        Set cl = Baza.Find(cell)
        If Not cl Is Nothing Then
            cell.Offset(0, -1) = cl.Offset(, 2)
            cell.Offset(0, -2) = cl.Offset(, 1)
        Else
            cell.Offset(0, -1) = ""
            cell.Offset(0, -2) = ""
        End If
    Next
    Columns("B:C").EntireColumn.AutoFit
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
И присваивать имя ВСЕМ ячейкам листа ( в Вашем случае 'База') не лучшая идея
Согласие есть продукт при полном непротивлении сторон.
 
Уважаемый Sanja,
Огромное Вам спасибо за помощь.
Не знаю сколько бы мучился если бы не Вы!!
 
Пожалуйста.
П.С. Исправьте пожалуйста свое стартовое сообщение.
Строки с кодом 'оберните' соответствующим оформлением (на панели инструментов значок <...>)
Согласие есть продукт при полном непротивлении сторон.
 
Сообщение поменял как Вы сказали.
Извиняюсь не часто создаю темы и не был в курсе как их правильно оформлять.

Попробовал Ваш код применить к станице Сводный за месяц.
Получилось вроде но при дальнейшем использовании появился баг.

Как вы заметили вторая строчка столбца C не заполнилась и в этом же столбце появились данные из столбца B.
Также при удалении данных с листа Заполнение на листе Сводный за месяц остаются заполненные ячейки.
Что не так я сделал. Я всего лишь скопировал код листа Заполнение на лист Сводный за месяц.
Все должно было получиться без проблем.
111.jpg (184.44 КБ)
 
Цитата
maxscorpio написал: Все должно было получиться без проблем.
Не совсем. Работа со Сводными таблицами отличается от работы с обычным диапазоном
Согласие есть продукт при полном непротивлении сторон.
Страницы: 1
Читают тему (гостей: 1)
Наверх