スキルアップ・問題解決なら、モーグにおまかせ！

お世話になっています。
 
課題に記載した内容は
ブルックアップで検索した結果に書式も同じように表示したいのです。
勉強部屋？見ても理解できなかった？ので投稿しました
 
したいことは
データシートがあります。
a3は年
b3は月
c3は日
e3はシフト
f3は課題
g3は対象
h3は内容
i3は記載者
 
d3はブルックアップ検索に必要な検索値を入れています。
 
シート1のa33:c33に上記f3記載分を表示
d33:f33に上記g3を表示
g3:aa33にh3を表示
ab3:ac3にi3を表示
 
します。
ブルックアップで検索した内容は33行目より下へ
対象が無くなるまで検索。
 
データシートで塗りつぶしや太字も反映したいのですが
勉強部屋のやり方ではマニュアル？的な感じがするのですが
違いますか？
やはりvbaでも検索を入れないといけないのでしょうか？
 
コードも書いてなく解りにくいとは思いますが宜しくお願いします

>ブルックアプップ
って何？
 
VLOOKUPのことな？
 
>勉強部屋
何処にあるの？
 
表のレイアウトを説明しましょう。
入力した関数を説明しましょう。
 
VBAで何をしたいのか？不明ですが、
このマクロをどのようなタイミングで起動ししたいのか？

数式の入っているセルに書式を設定したいということと思いますが、
一般的な話として
関数は、「値」を参照します。(書式は参照の非対象）
従って、書式は関数では扱えないと考えた方がよいです。
 
数式の入っているセルに、他セルの書式を複写することは可能です。
敢えて、VBAを使わなくても
他セル（範囲）をコピーして、貼り付けたいセルお選択して、
「形式を選択して貼付け」→「書式」を選択します。
 

お世話になっております。
ご回答ありがとうございます。
 
タイミングとしては自動でが一番好ましいです。
シート2の検索値を変更するとシート１の該当する物がシート２に表示される仕様となっています。
（検索値はシート２の上段の方に設置）
 
レイアウトは下記の通りです
シート１

　　　　A　BC	D	E	F	G	H	I	　　　　J
3	年月日	��	検索値	表示1	表示2	表示3	表示4　　	表示5
4				　　　　経済	日本	円安が進行中	田中
5										
6										

	A:C	DEF	G:AA　　	AB:AC
32	表示2	表示3	表示4　　	表示5
33	経済	日本	円安が進行中	田中

【検索について】
前提の記載が少し変わってきているようです。
・何を検索値に、
・どこから取得するかが回答者にわかるように示したほうがよいと思います。
VLOOKUPの計算式を示すのが一番明確です。
いや、そこはできていて、それは問題じゃない、ということなら、
途中までで結構なので、マクロを示してください。
 
【書式の取り扱いについて】
シート２には1項目につき複数列が指定されていますが、
結合セルということですか？
そして、すでにセルは結合されているのですか？
それとも結合自体もマクロで実行するのですか？
 
結合されたセルにコピーペイストしても書式はうまくコピーされないようなので、
・できれば結合セルはやめる。
（列の幅でいくらでも調整は可能では？結合セルは、百害あって一利なしです）
・どうしても結合なら、コピーペイストしてから結合する
のがよいと思います。
 
# 勉強部屋とは、"速攻テクニック"のことでしょうか。どの記事なのかおしえてください。

お世話になっております。
 
勉強部屋とは即効テクニックのVBA、書式関連で見ました。
 
検索値についてですが
=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BO10,シート１・・・・
みたいな感じで年&月&日&表示1&�ｂﾅす
 
BO10に�ｌ沚�欄を設けています
=IFERROR8INDEX(シート１D:D,1/LARGE(INDEX(シート1!C4:C3000=シート2!K5)/ROW(シート1!C4:C3000),0),ROW(シート1!C1))),"")
 
結合されているのかというご質問ですが
されています。
解除はできますが
G:AAについては長文記載のため解除したくないです。
（後から結合でも問題はありませんが）
 
マクロというより関数で抽出まで作成して
書式の転記に困っているところです

書式の複写について
 
悦明では、参照先表では、
例えば
Ｇ列セルは、単独セルに対して
参照値を表示しているセルは結合されています。
お互いのセル書式設定が異なり、単純には複写できません。
勿論、関数でセルの書式を参照することはできません。
 
例
参照先：G4
参照元：A5:C5（結合セル）
 
G4セルの書式をA5:C5に複写すると、結合が解除されてしまいます。
 
※ＶＢＡで対応する場合、
(1)関数をＶＢＡに組み込み、参照先セルのアドレスを取得する。
(2)参照元セルの結合範囲を記憶する
(3)関数で取得したセル全体をコピペする。
(4)再結合する。
 
のような手順でコードを考えればできるかも・・・

1.データをVLOOKUPで引っ張って来ているなら、
  取得先の位置を知るためには、
  関数式と同等の検索を、
  Match関数なりFindメソッドなりを使って実行する必要があります。
  式だけを見て取得先位置を判断することはできません。
  なんらかの検索が必要なのは当然です。
  (Findメソッドを使った質問をされているので、そこはそちらで可能なはずです。）
 
2.その取得先の位置が決まりさえすれば、
  そのセルの書式を対象セル（取得元）に書式コピーをすればよいと思います。
   
3.ただし、セルのなかの一部の文字列といった「文字単位の書式」も対象なら、
  VLOOKUPなどの「式のままでは無理」です。（これは仕様です。）
  いったん値に変換する必要があります。
  結果的に式がなくなりますが、それで後続の処理に支障があるか否かの判断が必要です。
 
4.また、上記2の書式コピーをすると、結合セル情報が消えて、いったん非結合セルになるので、
  その修復が必要です。
 
即効テクニックのURLとかも出されないし、
VLOOKUPの式も一部しか示さなかったりと、
情報を極力秘密にしておきたいようですから、
上記を参考に、あなたがトライしてください。
 
そのうえで具体的な質問事項があれば、また質問してください。

すでに書かれていますが、マクロでやるならVLOOKUP関数は忘れてFindメソッドで検索、コピペの流れが楽ではないですか？
 
>シート2の検索値を変更するとシート１の該当する物がシート２に表示される仕様となっています。
 
シート２モジュールで
 
Private Sub Worksheet_Change(ByVal Target As Range)
     
Application.EnableEvents = False
 
For Each a In Target.Areas
    For Each r In a.Cells
        If Not Intersect(r, Range(セルの変更を検知する範囲)) Is Nothing Then
            Set f = Sheets("シート１").Columns(検索範囲).Find(What:=r.Value, LookIn:=xlValues, Lookat:=xlWhole)
            If Not f Is Nothing Then
                f.Offset(, 右に必要な数だけシフト).Resize(1, 右に必要な数だけ拡張).Copy _
                Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        End If
   Next
Next
 
Application.EnableEvents = True
 
End Sub

お世話になっております。
ご回答ありがとうございます。
 
関数での抽出は無し（内容の）ということでしょうか？
コード内でご親切に書いていただいているのに検索値を入れるところも理解できずです
 
 
vba初心者ですので内容が理解できておらずすみません。
 
フォームや数式に公開制限があると書かれている方もおられましたのでのせておきます。
 
シート２

	ABCDE
1	昼勤			
2				
															
																								

コードの中身が分からないということで、Findメソッドの使い方を以下に。
Findメソッドで検索値を見つけ、見つけたセルを基点に必要なセル範囲をコピペすることで
VLOOKUP関数と同じようなことをしています。
 

シート１
       A        B               ・・・   F                 G
1     検索値    表示したい内容１　　　　表示したい内容２   表示したい内容３
2     果物      りんご                  赤　　　　　　　　 100円　
3     野菜      キャベツ                緑                 200円 
4     魚        鯛                      赤                 300円 
5     肉        牛                      赤                 400円

シート２
       A        B        C       D
1     検索値    結果１   結果２  結果３
2     魚
3     野菜

Sub 検索する()
    
    For Each r In Sheets("シート２").Range("A2:A3") 'シート２のA2:A3に検索値が入ってる
        
        '完全一致で検索
        Set f = Sheets("シート１").Columns("A").Find(What:=r.Value, _
                                                     LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not f Is Nothing Then  '検索値がヒットしたら
            
            f.Offset(, 1).Copy r.Offset(, 1) 'シート１のB列をコピペ
            
            f.Offset(, 5).Resize(1, 2).Copy r.Offset(, 2) 'シート１のF:G列をコピペ
            
        End If
    Next
    
End Sub

コード実行結果
シート２
       A        B        C       D
1     検索値    結果１   結果２  結果３
2     魚        鯛       赤      300円
3     野菜      キャベツ 緑      200円  

ありがとうございます
 
載せていただいたテストでは上手くいきました。
 
実際のファイルに記述して実行しましたが
検索値の右側セルに評価されます。
 
結果の表示先を指定する方法はありますでしょうか？

>結果の表示先を指定する方法はありますでしょうか？
 
表示先セルが結合セルということすよね？
結果１の結合セル個数分、右へシフト（セルを挿入）ご結合
結果２の結合セル個数分、右へシフト（セルを挿入）ご結合
結果３の結合セル個数分、右へシフト（セルを挿入）ご結合
のような具合でいかがですか？

お世話になっております。
 
できるかどうかわかりませんが
 
シート2の
結果が転記される部分を
非結合に元々して、転記する際に結合が
コード的にも簡単でしょうか？
 

引用:
シート2の
結果が転記される部分を
非結合に元々して、転記する際に結合が
コード的にも簡単でしょうか？

【モデル例】

  A     B:C    D:E     F:H     I      J     K     L     M   
1                                     a     a1    a2    a3
2                                     b     b1    b2    b3
3                                     c     c1    c2    c3
4                                     d     d1    d2    d3
5                                                                        
6 a     a1     a2      a3                                        
7 c     c1     c2      c3                                        
8 b     b1     b2      b3                                        
9 d     d1     d2      d3  

Sub test()
    Dim k&, j&
    For k = 6 To 9
        Call getFormat(Cells(k, 2), Cells(k, "A"), Range("J1:J4"), 2)
        Call getFormat(Cells(k, 4), Cells(k, "A"), Range("J1:J4"), 3)
        Call getFormat(Cells(k, 6), Cells(k, "A"), Range("J1:J4"), 4)
    Next
End Sub

Function getFormat(targetR As Range, s As String, searchR As Range, col As Long)
    Dim m As Long
    Dim rng As Range
    
    m = Application.Match(s, searchR, 0)
    Set rng = searchR.Cells(m, col)                 'コピー元のセル範囲
    
    '結合セルに値貼り付け
    Set targetR = targetR.MergeArea
    targetR.UnMerge                                 'いったん結合を解除
    rng.Copy
    targetR(1).PasteSpecial Paste:=xlPasteFormats   '書式貼り付け
    targetR.Merge                                   '再結合
End Function

    s = [B5] & [G5] & [k5] & [A1] & Cells(k, "BO")
    Call getFormat(Cells(33, "A"), _
                   s, _
                   Worksheets("Sheet1").Cells(4, "F").Resize(3000, 1), _
                   Worksheets("Sheet1").Cells(2, "H"))

>結果の表示先を指定する方法はありますでしょうか？
 
結果を表示する箇所を指定する方法はいくつでもあります。
どの方法が一番良いかは個人によりますね。
 

簡単な貼り付けの例

Range("A1").Copy 'A1をコピー
Range("A2").PasteSpecial xlAll 'A2に貼り付け

Range("A1").Copy 'A1をコピー
Range("A1").Offset(1, 0).PasteSpecial 'A2に貼り付け

Range("A1").Copy 'A1をコピー
Range("A1").Offset(1, 1).PasteSpecial 'B2に貼り付け

Range("A1").Copy 'A1をコピー
Range("A1").Offset(1, 1).Resize(1, 2).PasteSpecial 'B2:C2に貼り付け

Range("A1").Copy 'A1をコピー
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial '実行するたびC列の最後尾+1に貼り付け

Range("A1").Copy 'A1をコピー
Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial '実行するたび1行目の最右列+1に貼り付け

Application.CutCopyMode = False

検索値を元に対象のみを抽出して表示する際に、既存の書式も併せて表示したい。
 
その際の書式セル結合が予め決まっている との事ですが、
そのセル結合は何のためでしょうか？
　例えば、
　　・更にその抽出結果の内容のセルをコピーし別範囲に張り付けたいのでしょうか？
　　それとも
　　・セル幅や罫線が決まっているでそれを触りたくない？
 
　後者なのであれば、それこそ、その部分だけを抽出後にVBAで処理すれば良いと思います。
 
　ですので、
　　1.検索値決定後 ボタン押下タイミング等で、検索値がある対象にあるか判定
　　2.ある場合は、元のシートをコピーしオートフィルター等で抽出
　　3.抽出後、希望のセル幅罫線を描く
　　4.画面上で検索対象値を見せる必要があるなら、
　　　　A）抽出後の必要範囲をコピーし、検索値を入力したシートにリンク貼り付け
　　　　B) または、抽出後のシートの1行目に、行を挿入し 検索値を表示させる
 
　　　画面上ではなく、印刷だけで良いなら、ヘッダー部に 検索値情報を入れ印刷プレビュー
 
　　特に、Simpleさんが気にされている様に　

引用:
ただし、文字ごとに異なる書式は使っていないものとします。

引用:
データシートで塗りつぶしや太字も反映したいのですが

お世話になっています。
 
情報不足ですみません。
シート2のフォームは固定です
 
マクロの記録がありましたね
コードを書かないといけないと固定観念にとらわれていました。
 
文字毎に異なる書式は使用していません。
内容の強調という意味で塗りつぶしか文字色の変更のみとなります。
 
特に検索値を見えるようにするというのは考えていません。
色々な方が触るシートなので計算式を非表示にしてるぐらいです。
 
マクロの記録とお教え頂いた内容をふまえ
考えてみたいと思います。