Note: The other languages of the website are Google-translated. Back to English

Как скопировать исходное форматирование ячейки поиска при использовании Vlookup в Excel?

В предыдущих статьях мы говорили о сохранении цвета фона при значениях vlookup в Excel. В этой статье мы собираемся представить метод копирования всего форматирования ячеек полученной ячейки при выполнении Vlookup в Excel. Пожалуйста, сделайте следующее.

Копирование исходного форматирования при использовании Vlookup в Excel с пользовательской функцией


Копирование исходного форматирования при использовании Vlookup в Excel с пользовательской функцией

Предположим, у вас есть таблица, как показано на скриншоте ниже. Теперь вам нужно проверить, находится ли указанное значение (в столбце E) в столбце A, и вернуть соответствующее значение с форматированием в столбце C. Для этого сделайте следующее.

1. На листе, содержащем значение, которое вы хотите просмотреть, щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню. Смотрите скриншот:

2. В дебюте Microsoft Visual Basic для приложений окно, скопируйте ниже код VBA в окно кода.

Код VBA 1: Vlookup и возвращаемое значение с форматированием

Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20211203
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Set xRg = Application.Range(xDicStr)
                xRg.Copy
                Range(xDic.Keys(I)).PasteSpecial xlPasteFormats
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
    Application.CutCopyMode = True
End Sub

3. Затем нажмите Вставить > Модулии скопируйте приведенный ниже код VBA 2 в окно модуля.

Код VBA 2: Vlookup и возвращаемое значение с форматированием

Public xDic As New Dictionary
'Update by Extendoffice 20211203
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = " "
        xDic.Add Application.Caller.Address, " "
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address(External:=True)
    End If
    Application.ScreenUpdating = True
End Function

4. Нажмите Инструменты > Рекомендации. Затем проверьте Среда выполнения сценария Microsoft коробка в Ссылки - VBAProject диалоговое окно. Смотрите скриншот:

5. нажмите другой + Q ключи для выхода из Microsoft Visual Basic для приложений окно.

6. Выберите пустую ячейку рядом с поисковым значением и введите формулу. =LookupKeepFormat(E2,$A$1:$C$8,3) в Панель формул, а затем нажмите Enter .

Внимание: В формуле E2 содержит значение, которое вы будете искать, 1 австралийский доллар: 8 канадских долларов это диапазон таблицы, а число 3 означает, что соответствующее значение, которое вы вернете, находится в третьем столбце таблицы. Пожалуйста, измените их по своему усмотрению.

7. Продолжайте выбирать первую ячейку результата, а затем перетащите маркер заполнения вниз, чтобы получить все результаты вместе с их форматированием, как показано на скриншоте ниже.


Статьи по теме:


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (42)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
это дает мне ошибку компиляции, синтаксическую ошибку

пожалуйста, помогите
Этот комментарий был сведен к минимуму модератором на сайте
Добрый день,
Код обновлен в статье. Спасибо за ваш комментарий.
Этот комментарий был сведен к минимуму модератором на сайте
Я также получил ошибку компилятора.
Это исправляется, если вы измените следующую переменную на фактическую "". Нет ';' в середине.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Извините за ошибку, код в статье обновлен.
Ошибка " " должна быть в двух кавычках " ". Спасибо за ваш комментарий.
Этот комментарий был сведен к минимуму модератором на сайте
У меня такая же ошибка.

Вам нужно будет изменить " " на фактическое "', без ';' как указано ниже
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Извините за ошибку, код в статье обновлен. Спасибо, что поделились.
Этот комментарий был сведен к минимуму модератором на сайте
Это здорово, спасибо! Единственная проблема в том, что я считаю, что это работает нормально, если я ищу на том же листе, но не могу заставить его работать, когда я пытаюсь выполнить поиск на отдельном листе исходных данных. Буду продолжать попытки
Этот комментарий был сведен к минимуму модератором на сайте
Юлия, поправьте строчки:
в функции LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name

в Sub Worksheet_Change:
Листы(Разделить(xDic.Items(I), "|")(1)).Range(Разделить(xDic.Items(I), "|")(0)).Копировать
Этот комментарий был сведен к минимуму модератором на сайте
Эй, Хьюго,


У меня такая же проблема, как у Юли. На других листах не работает. Не могли бы вы помочь написать код для всей функции и подлиста? Я не уверен, где заменить/вставить xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Nam and Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Копировать


спасибо в ответ
Этот комментарий был сведен к минимуму модератором на сайте
Большое спасибо за продолжение Хьюго!
К сожалению, как и Ви, я слишком новичок, чтобы понять, куда вставить предложенные вами исправления кода...

Еще раз спасибо, хорошего дня :)
Этот комментарий был сведен к минимуму модератором на сайте
Всем привет


Я пытался использовать код, однако я получаю сообщение об ошибке на прикрепленном изображении. Любая помощь будет принята с благодарностью.
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Извините за ошибку, код в статье обновлен. Спасибо за ваш комментарий.
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,

Я не получаю ошибок, и он выполняет поиск, но поскольку мое значение поиска находится на другом листе (более вероятный сценарий), он не извлекает форматирование. Есть ли настройка кода, которую я могу сделать для этого? (Будьте очень конкретны в отношении того, куда нужно внести изменения, поскольку я новичок в программировании) Спасибо! Я рад добавить эту функцию в одну из моих электронных таблиц!!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, удачи в этом вопросе, как мы можем получить форматирование для просмотра на листах?
Этот комментарий был сведен к минимуму модератором на сайте
Тоже ищу твик.
Этот комментарий был сведен к минимуму модератором на сайте
Кроме того, если я добавлю вашу формулу как часть оператора «Если» (см. ниже), она форматирует ячейку так, как хочет LOL (или, по крайней мере, так кажется. В одной ячейке текст стал затененным и жирным с верхней границей на ячейка; другая ячейка, текст по центру)


=ЕСЛИ($F19 = "", "",LookupKeepFormat(F19,'Элемент #s'!$A$1:$M$1226,2))
Этот комментарий был сведен к минимуму модератором на сайте
Я попробовал этот и тот, который тянет только цветной фон, и получаю ту же ошибку. Ошибка компиляции: обнаружено неоднозначное имя. Я нажимаю OK, и он выделяет xDic. Какие-либо предложения? Я не очень хорошо знаком со всем этим, поэтому, пожалуйста, помогите / объясните :) заранее спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Привет Джени,
Не забудьте включить параметр Microsoft Script Runtime, как указано в шаге 4.
Этот комментарий был сведен к минимуму модератором на сайте
Привет. Я создал пустую электронную таблицу и продублировал ваш пример в Excel 2013, но продолжаю получать ошибку компиляции: синтаксическая ошибка и Dim I As Long выделены. Есть что-то, что мне не хватает? Я хотел бы, чтобы это работало. Спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Лаура,
Не забудьте включить параметр Microsoft Script Runtime, как указано в шаге 4.
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, я использовал приведенный выше код в Excel 2010 без проблем до настоящего времени. Однако недавно я обновился до Office 2016, и теперь код приводит к сбою Excel каждый раз, когда я пытаюсь заполнить более одной строки. К сожалению, это не дает мне другой ошибки, кроме «Microsoft Excel перестал работать». Мне интересно, сталкивались ли вы с этой проблемой ранее, и есть ли что-то, что мне нужно сделать, чтобы она заработала в 2016 году. Спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Привет Ли,
Код хорошо работает в моем Excel 2016. Мы пытаемся обновить код, чтобы решить проблему. Спасибо за ваш комментарий.
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, спасибо за код. Я не получаю никаких сообщений об ошибках, но формула работает только как обычный поисковый просмотр. Не могли бы вы помочь? Спасибо за ваше время.
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте

У меня точно такая же проблема, как решить?

Благодаря!
Этот комментарий был сведен к минимуму модератором на сайте
привет, я получил сообщение об ошибке "Ошибка компиляции: обнаружено неоднозначное имя: xDic
Этот комментарий был сведен к минимуму модератором на сайте
привет, я получил сообщение об ошибке "Ошибка компиляции: обнаружено неоднозначное имя: xDic
Этот комментарий был сведен к минимуму модератором на сайте
Привет, я новичок в использовании VBA и пытался использовать этот код в своей электронной таблице, но форматирование текста на вкладке Rec2 не переходит на вкладку Rec при использовании поиска. Любая помощь будет принята с благодарностью. Спасибо Пэт
Этот комментарий был сведен к минимуму модератором на сайте
Вот файл и картинка
Этот комментарий был сведен к минимуму модератором на сайте
Я получаю ту же ошибку неоднозначного имени - кому-нибудь удалось ее решить?
Этот комментарий был сведен к минимуму модератором на сайте
Я получаю ту же ошибку неоднозначного имени - кому-нибудь удалось ее решить?
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL