メインコンテンツへスキップ

ExcelでVlookupを使用するときに、ルックアップセルのソースフォーマットをコピーするにはどうすればよいですか？

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

ユーザー定義関数を使用してExcelでVlookupを使用する場合のソースフォーマットのコピー

ユーザー定義関数を使用してExcelでVlookupを使用する場合のソースフォーマットのコピー

以下のスクリーンショットのようなテーブルがあるとします。 次に、指定された値（列E）が列Aにあるかどうかを確認し、対応する値を列Cにフォーマットして返す必要があります。これを実現するには、次のようにしてください。

1.ワークシートにvlookupする値が含まれている場合は、シートタブを右クリックして、 コードを表示 コンテキストメニューから。 スクリーンショットを参照してください：

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) に 数式バー、を押して 入力します キー。

Note：式では、 E2 検索する値が含まれています。 $ A $ 1：$ C $ 8 はテーブルの範囲であり、数値は 3 は、返される対応する値がテーブルの XNUMX 列目にあることを意味します。 必要に応じて変更してください。

7. 最初の結果セルを選択し続け、フィル ハンドルを下にドラッグして、以下のスクリーンショットに示すように、すべての結果とその書式設定を取得します。

関連記事：

最高のオフィス生産性向上ツール

🤖 Kutools AI アシスタント: 以下に基づいてデータ分析に革命をもたらします。 インテリジェントな実行   |  コードを生成  |  カスタム数式の作成  |  データを分析してグラフを生成する  |  Kutools関数を呼び出す...
人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup  |   複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー   |  ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化   |  リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット:  12 テキスト ツール (テキストを追加, 文字を削除する、...）   |   50+ チャート 種類 (ガントチャート、...）   |   40+ 実用的 式 (誕生日に基づいて年齢を計算する、...）   |   19 挿入 ツール (QRコードを挿入, パスから画像を挿入、...）   |   12 変換 ツール (数字から言葉へ, 通貨の換算、...）   |   7 マージ＆スプリット ツール (高度な結合行, 分割セル、...）   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

説明

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (44)
No ratings yet. Be the first to rate!
Dorina
Dorina
This comment was minimized by the moderator on the site
It seems to work but excel freezes from it and goes unresponsive. HELP!!!!
Reply
0
0
Kaila
Kaila
This comment was minimized by the moderator on the site
So - this macro works, but every time I use it my spreadsheet stops responding for roughly 3 minutes (even for one single line of data). Any tips?
Reply
0
0
slb
slb
This comment was minimized by the moderator on the site
Is there a way to use this on the same sheet with two different lookups. Ie. Lookup Column M in array A:B, return column B with formatting. Then Lookup in Column N in array C:D and return column D with formatting?
Ive got the first set working perfectly, and the second set wont work at all. No error, just most of the rows are blank
Reply
0
0
kirtiraj
kirtiraj
This comment was minimized by the moderator on the site
This code only works when data is in same sheet.
Reply
0
0
crystal
crystal     kirtiraj
This comment was minimized by the moderator on the site
Hi kirtiraj,To lookup values across worksheets and keep the formatting, you need to place the VBA code 1 in the code window of the result worksheet, and place the VBA code 2 in the Module code window.
Reply
Report
0
0
John
John
This comment was minimized by the moderator on the site
I get a compile error: "Expected: end of statement", with the word "New" highlighted in: "Public xDic As New Dictionary".
I'm not a developer, just trying to solve a problem in a long set of sheets. So thank you for the help.
Reply
0
0
M B
M B
This comment was minimized by the moderator on the site
HeyThe code does not work in Microsoft Excel 2019 (16.0.13929.20360) 64-bit Can provide details if asked...
Reply
0
0
ed
ed     M B
This comment was minimized by the moderator on the site
Please provide details
Reply
0
0
ABCD
ABCD
This comment was minimized by the moderator on the site
How to make this work if the value we are trying to look up sits in a different worksheet?
Reply
0
0
crystal
crystal     ABCD
This comment was minimized by the moderator on the site
Hi,
To lookup values across worksheets and keep the formatting, you need to place the VBA code 1 in the code window of the result worksheet, and place the VBA code 2 in the Module code window.
Reply
Report
1
0
FrenchToastKB
FrenchToastKB     crystal
This comment was minimized by the moderator on the site
That's Bad***!
Thanks for the coding and the tip on how to make the formula work across separate worksheets, friend.
However the coding should modified for the Subworksheet_Change for the one below:
Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20230328
Dim I As Long
Dim xKeys As Long
Dim xDicStr As String
On Error Resume Next
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.EnableEvents = 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
Application.EnableEvents = True
End Sub


If you combine the coding I wrote below for the Result worksheet and the coding provided here in the Module, it will work when using separate worksheets.
You'll thank me later!
Cheers
Reply
Report
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
So, I got this to work. However, how I'm using it is I have the lookupkeepformat formula already entered in multiple rows. I then enter a letter (A-J) in column A and this letter tells the lookup formula which data I want. After it pulls the data, the cursor ends up in the cell where it finished entering the lookup data. How can I have the cursor return to column A?
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
I'm getting an error
Reply
0
0
Rah2153
Rah2153
This comment was minimized by the moderator on the site
I am adding these modules to my PERSONAL.XLSB file. I have Outlook 2016. And when I use this user-defined function, my excel doesn't crash or give an error. But it does not pull in the format of the source cell. It only pulls in the value. Since this function is located in the PERSONAL.XLSB file, my formula looks like this;=PERSONAL.xlsb!LookupKeepFormat(E2,$A$1:$C$8,3)
I had copied the code into 2 modules as directed, but this just doesn't work. Any ideas why?
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations