ヒント:他の言語はGoogle翻訳されています。 訪問することができます English このリンクのバージョン。
ログイン
x
or
x
x
会員登録
x

or

ExcelでVlookupを使用しているときに参照セルのソース書式をコピーする方法

前回の記事では、Excelでvlookup値を設定したときに背景色を保持する方法について説明しました。 ここでは、ExcelでVlookupを実行するときに結果のセルのすべてのセルの書式をコピーする方法を紹介します。 以下のようにしてください。

ユーザー定義関数を使用してVlookupをExcelで使用する場合のソース書式のコピー


複数のワークシート/ワークブックを1つのワークシート/ワークブックにまとめる:

複数のワークシートまたはワークブックを1つのワークシートまたはワークブックにまとめることは、日々の作業において巨大な作業になる可能性があります。 しかし、もしあなたが Kutools for Excel、その強力なユーティリティ - 組み合わせる 複数のワークシート、ワークブックを1つのワークシートまたはワークブックにすばやく結合するのに役立ちます

Kutools for Excel200便利なExcelアドイン以上で、60日に制限なく試してみることができます。 今すぐダウンロードして無料トライアル!

OfficeタブOfficeでタブ付き編集とブラウジングを有効にし、作業をより簡単にします...
Kutools for Excelは、ほとんどの問題を解決し、生産性を80%向上させます
  • 何でも再利用: 最も使用されている式や複雑な式、チャート、その他をお気に入りに追加し、将来的にすぐに再利用できます。
  • 20以上のテキスト機能: テキスト文字列から数値を抽出します。 テキストの一部を抽出または削除します。 数字と通貨を英語の単語に変換...
  • マージツール:複数のワークブックとシートを1つに; データを失うことなく複数のセル/行/列を結合します。 重複する行と合計をマージ...
  • 分割ツール:値に基づいてデータを複数のシートに分割します。 1つのワークブックから複数のExcel、PDF、またはCSVファイル。 1列から複数列...
  • 貼り付けスキップ 非表示/フィルターされた行。 カウントアンドサム 背景色別; メーリングリストを作成し、 セルの価値でメールを送信する...
  • スーパーフィルター: 高度なフィルタースキームを作成し、任意のシートに適用します。 ソート 週、日、頻度などにより; フィルタ 太字、式、コメントで...
  • 300の強力な機能以上。 Office 2007-2019および365で動作します。 すべての言語をサポートしています。 企業または組織に簡単に展開できます。

ユーザー定義関数を使用してVlookupをExcelで使用する場合のソース書式のコピー


次のようなスクリーンショットがあると仮定します。 ここで、指定された値(列E)が列Aにあるかどうかをチェックし、対応する値を列Cに書式設定して返す必要があります。

1。 ワークシートにvlookupしたい値が含まれている場合は、シート・タブを右クリックし、 コードを表示 コンテキストメニューから選択します。 スクリーンショットを見る:

2。 オープニング アプリケーション用Microsoft Visual Basic VBAコードをコードウィンドウにコピーしてください。

VBAコード1:フォーマットとVlookupと戻り値

Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180706
    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
                Range(xDic.Items(I)).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 20180706
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
    End If
    Application.ScreenUpdating = True
End Function

4。 クリック ツール > リファレンス。 その後、 Microsoft Script Runtime 内箱 参照 - VBAProject ダイアログボックス。 スクリーンショットを見る:

5。 プレス 他の + Q キーを押して アプリケーション用Microsoft Visual Basic 窓。

6。 ルックアップ値に隣接する空白のセルを選択し、式を入力します =LookupKeepFormat(E2,$A$1:$C$8,3) 数式バー、を押して 入力します キー。

式中、 E2 ルックアップする値が含まれていますが、 $ A $ 1:$ C $ 8 テーブルの範囲と数値です 3 返される対応する値がテーブルの3番目の列にあることを意味します。 必要に応じて変更してください。

7。 最初の結果セルを選択したままにして、Fill Handleをドラッグして、下のスクリーンショットに示すように、すべての結果を書式とともに取得します。


関連記事:


Kutools for Excelは、ほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート そして、以前に使用したもの; セルを暗号化する パスワード付き メーリングリストの作成 そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストや数式を簡単に編集する) レイアウトを読む (多数のセルを簡単に読んで編集できます)。 フィルター範囲に貼り付ける...
  • セル/行/列を結合 データを失うことなく; セルコンテンツの分割。 重複する行/列を結合する...重複セルの防止。 範囲の比較...
  • 重複または一意を選択します空白行を選択 (すべてのセルは空です)。 スーパー検索とファジー検索 多くのワークブックで。 ランダム選択
  • 完全コピー 式の参照を変更せずに複数のセル。 参照を自動作成 複数のシートに 箇条書きを挿入、チェックボックスなど
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷 セルのコンテンツとコメント間の変換...
  • スーパーフィルター (保存して他のシートにフィルタ方式を適用する)。 高度な並べ替え 月/週/日、頻度などによる。 特殊フィルター 太字、斜体で...
  • ワークブックとワークシートを組み合わせる; キー列に基づいて表をマージします。 データを複数のシートに分割する; xls、xlsx、およびPDFのバッチ変換...
  • 300を超える強力な機能。 Office / Excel 2007-2019および365をサポートします。 すべての言語をサポートします。 企業または組織に簡単に展開できます。 フル機能の30日間の無料トライアル。
KTEタブ201905

OfficeタブはOfficeにタブ付きインターフェイスを提供し、作業をより簡単にします

  • Word、Excel、PowerPointでタブ付き編集と読み取りを有効にする、出版社、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を50%向上させ、毎日数百回のマウスクリックを削減します。
オフィシタブ底
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Pat · 4 months ago
    Here is the file and pic
  • To post as a guest, your comment is unpublished.
    Pat · 4 months ago
    HI, I am new to using VBA and tried using this code in my spreadsheet, but the text formatting on the Rec2 tab doesn't come over to Rec tab when lookup is used. Any help would be greatly appreciated. Thanks Pat
  • To post as a guest, your comment is unpublished.
    Gareth · 5 months ago
    hi i got the error "compile Error: Ambigious name detected: xDic
  • To post as a guest, your comment is unpublished.
    Jack · 6 months ago
    hi i got the error "compile Error: Ambigious name detected: xDic
  • To post as a guest, your comment is unpublished.
    Aurelie · 7 months ago
    Hello, Thanks for the code. I do not get any error message but the formula only works as a normal vlookup would. Could you please assist? Thanks for your time.
    • To post as a guest, your comment is unpublished.
      Joel · 4 months ago
      Hello

      I have exactly the same issue, did you figure out how to solve it?

      Thanks!
  • To post as a guest, your comment is unpublished.
    Leigh · 8 months ago
    Hello, I've been using the above code in Excel 2010 with no problems to date. However, I was recently upgraded to Office 2016 and now the code crashes Excel every time I try to fill down more than one row. Unfortunately, it is not giving me an error other than "Microsoft Excel has stopped working". I was wondering if you have come across this issue previously, and if there is something I need to do to make it work in 2016. Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Leigh,
      The code works well in my Excel 2016. We are trying to upgrad the code to solve the problem. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Laura · 11 months ago
    Hello. I created a blank spreadsheet and duplicated your example in Excel 2013, but keep getting a Compile error: Syntax error and Dim I As Long is highlighted. Is there something I'm missing? I would love to get this working. Thank you.
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Laura,
      Don't forget to enable the Microsoft Script Runtime option as mentioned in step 4.
  • To post as a guest, your comment is unpublished.
    Jeni · 1 years ago
    I tried this one and the the one that pulls just the color background and am getting the same error. Compile error: Ambiguous name detected. I click OK and it highlights xDic. Any suggestions? I'm not super familiar with all of this so please help/explain :) thanks in advance
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Jeni,
      Don't forget to enable the Microsoft Script Runtime option as mentioned in step 4.
  • To post as a guest, your comment is unpublished.
    Heather M · 1 years ago
    Also, if I add your formula as part of an "If" statement (see below), it formats the cell however it wants LOL (or at least it seems so. One cell, the text went shadowed and bold with a top border on the cell; another cell, the text centered)


    =IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
  • To post as a guest, your comment is unpublished.
    Heather M · 1 years ago
    Hi,

    I get no errors and it does the lookup, but because my lookup value is on another worksheet (a more likely scenario), it doesn't pull the formatting. Is there a tweak to the code that I can make for that? (Be very specific as to where the change needs to go as I'm a coding novice) Thank you! I'm excited to add this feature to one of my spreadsheets!!
    • To post as a guest, your comment is unpublished.
      Chirag · 11 months ago
      Hi, any luck on this question, how can we get the formatting to be looked up across sheets?
  • To post as a guest, your comment is unpublished.
    Nivian Govender · 1 years ago
    Hi There


    I have tried to use the code however I am getting the error in the attached pic. Any assisting will be greatly appreciated.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Sorry for the mistake, the code has been updated in the article. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Julia · 1 years ago
    Greatly appreciate the follow-up Hugo!
    Unfortunately like Vi, I am too much of a novice to work out where to insert your suggested code fixes...

    Thanks again, have a great day :)
  • To post as a guest, your comment is unpublished.
    Vi · 1 years ago
    Hey Hugo,


    I have the same problem as Julia. It doesn't work on other sheets. Could you help write code for the whole function and sub worksheet? I am not sure where to replace/insert 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)).Copy


    thanks in return
  • To post as a guest, your comment is unpublished.
    Hugo · 1 years ago
    Julia, correct this lines:
    in Function LookupKeepFormat:
    xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name

    in Sub Worksheet_Change:
    Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy
  • To post as a guest, your comment is unpublished.
    Julia · 1 years ago
    This is great, thank you! The only problem is, I find it works fine if I'm looking up in the same sheet, but can't get it to work when I'm trying to do a lookup in a separate sheet to the source data. Will keep trying
  • To post as a guest, your comment is unpublished.
    LTBallard · 1 years ago
    I got the same error.

    You will have to change the &quot; &quot for actual "', without ';' as indicated below
    LookupKeepFormat = &quot; &quot;
    xDic.Add Application.Caller.Address, &quot; &quot;

    LookupKeepFormat = ""
    xDic.Add Application.Caller.Address ""
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Sorry for the mistake, the code has been updated in the article. Thank you for sharing.
  • To post as a guest, your comment is unpublished.
    ltballard · 1 years ago
    I also got the compiler error.
    It gets corrected if you change the following variable with actual "". No ';' in the middle.
    LookupKeepFormat = &quot; &quot;
    xDic.Add Application.Caller.Address, &quot; &quot
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Sorry for the mistake, the code has been updated in the article.
      The mistake &quot; &quot; should be two quotation marks " ". Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Sayed · 1 years ago
    it give me Compile Error ,Syntax error

    please help
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      The code has been updated in the artcle. Thank you for your comment.