Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Получение результатов функции ВПР в ячейке через Макрос
 
#1
09.06.2019 07:52:23
Добрый день.
Никак не получается в макросе прописать функцию ВПР чтобы в ячейке вместо формулы выводились уже результаты работы (значение ).
Формулу вставлять получается но есть риск что в дальнейшем при использовании пользователи могут что-то исправить в формуле.
Да и думаю что это должно облегчить файл. При открытии не будут происходить повторные вычисления. В заполненных ячейках уже будет текст.

Чтобы было понятнее опишу порядок действий.
В столбце 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 - 09.06.2019 12:34:10
 
 
 
#2
09.06.2019 08:41:24
Синтаксис использования функций листа в коде 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
И присваивать имя ВСЕМ ячейкам листа ( в Вашем случае 'База') не лучшая идея
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
09.06.2019 09:09:43
Уважаемый Sanja,
Огромное Вам спасибо за помощь.
Не знаю сколько бы мучился если бы не Вы!!
 
 
 
#4
09.06.2019 09:36:00
Пожалуйста.
П.С. Исправьте пожалуйста свое стартовое сообщение.
Строки с кодом 'оберните' соответствующим оформлением (на панели инструментов значок <...>)
Согласие есть продукт при полном непротивлении сторон
 
 
 
#5
09.06.2019 12:57:48
Сообщение поменял как Вы сказали.
Извиняюсь не часто создаю темы и не был в курсе как их правильно оформлять.

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

Как вы заметили вторая строчка столбца C не заполнилась и в этом же столбце появились данные из столбца B.
Также при удалении данных с листа Заполнение на листе Сводный за месяц остаются заполненные ячейки.
Что не так я сделал. Я всего лишь скопировал код листа Заполнение на лист Сводный за месяц.
Все должно было получиться без проблем.
 
 
 
#6
09.06.2019 14:22:43
Цитата
maxscorpio написал: Все должно было получиться без проблем.
Не совсем. Работа со Сводными таблицами отличается от работы с обычным диапазоном
Согласие есть продукт при полном непротивлении сторон
 
 
 
Страницы: 1
Наверх