Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
関数で検索した文字に書式も元データと同じにする
投稿日時: 22/01/02 02:43:41
投稿者: まさ@まさ

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

回答
投稿日時: 22/01/02 09:40:16
投稿者: WinArrow
投稿者のウェブサイトに移動

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

回答
投稿日時: 22/01/02 11:35:54
投稿者: WinArrow
投稿者のウェブサイトに移動

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

投稿日時: 22/01/02 14:01:49
投稿者: まさ@まさ

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

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

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

 
 
シート1で塗りつぶしや文字色変更、太字をそのまま反映させたいのです。
検索値が変更されると自動で変更が理想ですが
 
関数では書式まで反映させる事は不可、また、条件付き書式でも
特定の文字や値ではないので不可と考えております
 

回答
投稿日時: 22/01/02 15:18:50
投稿者: simple

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

投稿日時: 22/01/02 16:18:18
投稿者: まさ@まさ

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

回答
投稿日時: 22/01/02 18:06:53
投稿者: WinArrow
投稿者のウェブサイトに移動

書式の複写について
 
悦明では、参照先表では、
例えば
G列セルは、単独セルに対して
参照値を表示しているセルは結合されています。
お互いのセル書式設定が異なり、単純には複写できません。
勿論、関数でセルの書式を参照することはできません。
 

参照先:G4
参照元:A5:C5(結合セル)
 
G4セルの書式をA5:C5に複写すると、結合が解除されてしまいます。
 
※VBAで対応する場合、
(1)関数をVBAに組み込み、参照先セルのアドレスを取得する。
(2)参照元セルの結合範囲を記憶する
(3)関数で取得したセル全体をコピペする。
(4)再結合する。
 
のような手順でコードを考えればできるかも・・・

回答
投稿日時: 22/01/02 21:46:02
投稿者: simple

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

回答
投稿日時: 22/01/04 14:22:11
投稿者: めんたん

すでに書かれていますが、マクロでやるならVLOOKUP関数は忘れてFindメソッドで検索、コピペの流れが楽ではないですか?
 
>シート2の検索値を変更するとシート1の該当する物がシート2に表示される仕様となっています。
 
シート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("シート1").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

投稿日時: 22/01/04 17:13:50
投稿者: まさ@まさ

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


	ABCDE
1	昼勤			
2				
															
																								

1A BCDEF GHIJ KLMN OP
2 2022年 1月 4日 =DATE(C6,H6,L6)
3
 
  A・・・・・・・・・・・AC
10=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BO10,シート3!$F$4:$H$3000,3,FALSE),"")
11↑
・↑
・↑
・↑
23↑
 
  ABCDEFG
24=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BO24,シート3!$F$4:$I$3000,4,FALSE),"")
 
  HIJKL
24=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BO25,シート3!$F$4:$J$3000,5,FALSE),"")
 
  A・・・・AC
28=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BO28,シート5!$F$4:$I$3000,3,FALSE),"")
 
  AB(2セル毎に結合されているABまで)
30=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BS10,シート5!$F$4:$I$3000,3,FALSE),"")
 
  ABC
33=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BO33,シート1!$F$4:$K$3000,シート1!H$2,FALSE),"")
34


70
  DEF
33=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BO34,シート1!$F$4:$K$3000,シート1!I$2,FALSE),"")
34

70
 
  G:AA
33=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BO33,シート1!$F$4:$K$3000,シート1!J$2,FALSE),"")
34

70
 
   AB:AC
33=IFERROR(VLOOKUP($B$5&$G$5&$K$5&$A$1&BO33,シート1!$F$4:$K$3000,シート1!K$2,FALSE),"")
34

70
 
上記とほぼ同じだが載せておきます(下記)
  AD:AG
1夜勤
2
 
 AE:AI AJ:AM AN:AQ   AR:AS
5 2022年 1月 5日 =DATE(AE5,AJ5,AN5)
 
AD:BFまで上記とほぼ同じ数式
 
 
上記数式に出てくるBO、BP等の数式
BO10:BO25==IFERROR(INDEX(シート3!D:D,1/LARGE(INDEX((シート3!E:E=シート2!$K$5&A1)/ROW(シート3!C:C),0),ROW(シート3!C1))),"")
BP10:BP25==IFERROR(INDEX(シート3!D:D,1/LARGE(INDEX((シート3!E:E=シート2!$AN$5&AD1)/ROW(シート3!C:C),0),ROW(シート3!C1))),"")
 
BO24==IFERROR(INDEX(シート3!D:D,1/LARGE(INDEX((シート3!C:C=シート2!$K$5)/ROW(シート3!I:I),0),ROW(シート3!I1))),"")
BO25==IFERROR(INDEX(シート3!D:D,1/LARGE(INDEX((シート3!C:C=シート2!$K$5)/ROW(シート3!J:J),0),ROW(シート3!J1))),"")
 
BO28==IFERROR(INDEX(シート4!D:D,1/LARGE(INDEX((シート4!E:E=シート2!$K$5&A1)/ROW(シート4!H:H),0),ROW(シート4!C1))),"")
 
BO33:BO70==IFERROR(INDEX(シート1!$E:$E,1/LARGE(INDEX((シート1!$D$4:$D$3000=シート2!$K$5&$A$1)/ROW(シート1!$C$4:$C$3000),0),ROW(シート1!C1))),"")
 
BP33:BP70==IFERROR(INDEX(シート1!$E:$E,1/LARGE(INDEX((シート1!$D$4:$D$3000=シート2!$AN$5&$AD$1)/ROW(シート1!$C$4:$C$3000),0),ROW(シート1!C1))),"")
 
BS10:BS23==IFERROR(INDEX(シート5!D:D,1/LARGE(INDEX((シート5!E:E=シート2!$K$5&A1)/ROW(シート5!H:H),0),ROW(シート5!C1))),"")
 
漏れあるかもしれませんがシート2はこんな感じです
 
シート1
  A B  C  D    E  F    G    H I  J K    L
3年 月 日 検索値 検索値 シフト 表題 対象 内容 記載者 確認者
4
5
6
D=C5&G5
F=A5&B5&C5&G5&E5
=連番
 
シート3
A:Gまでは同じ  H   I    J   K
3 内容 教育 教育 記載者
 
 
シート4
A:Gまでは同じ H   I      J
3 対象者 内容 記載者
 
シート5はシート4と同じ
 
 
シート2のフォームは固定で
シート1・3・4・5は特にこだわりはありません。
 
わかりにくいとは思いますがよろしくお願いします

回答
投稿日時: 22/01/04 18:15:23
投稿者: めんたん

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

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

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

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

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

投稿日時: 22/01/04 18:52:33
投稿者: まさ@まさ

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

回答
投稿日時: 22/01/04 20:33:17
投稿者: WinArrow
投稿者のウェブサイトに移動

>結果の表示先を指定する方法はありますでしょうか?
 
表示先セルが結合セルということすよね?
結果1の結合セル個数分、右へシフト(セルを挿入)ご結合
結果2の結合セル個数分、右へシフト(セルを挿入)ご結合
結果3の結合セル個数分、右へシフト(セルを挿入)ご結合
のような具合でいかがですか?

投稿日時: 22/01/04 20:59:01
投稿者: まさ@まさ

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

回答
投稿日時: 22/01/04 23:37:09
投稿者: WinArrow
投稿者のウェブサイトに移動

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

簡単・・・個人差があります。
マクロの記録でコードを作成してみたら、いかがでしょう。

回答
投稿日時: 22/01/05 00:11:30
投稿者: simple

【モデル例】

  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  

・B6 には =VLOOKUP($A6,$J$1:$M$4,2,FALSE) が入っているものとします。
  他のセルも同様です。
・また、K1:M4 の各セルには文字色、背景色、太字などの書式が施されているものとします。
  ただし、文字ごとに異なる書式は使っていないものとします。
  (その場合は別途の対応が必要です。確認に対して回答がなかったので不明です。)
 
【書式をコピーするコード例】
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

(一応の動作を確認してあります)
 
以上が骨子となる技術だと思います。
 
実例に即していえば、
たとえば、シート2のA33:C33の結合セルに対する処理であれば、
上記の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"))

一部修正:
引数には、結合セルの最左最上の単一セルを渡し、
プロシージャ側でMergeAreaに拡大することとしました。
こちらのほうがコード作成の負荷が少し省けるかもしれません。

回答
投稿日時: 22/01/05 08:53:33
投稿者: めんたん

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

簡単な貼り付けの例

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

 
 
 

回答
投稿日時: 22/01/05 11:16:21
投稿者: Suzu

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

引用:
ただし、文字ごとに異なる書式は使っていないものとします。
  が含まれるのであれば、なおさら楽ちんかと。
 
最も、
引用:
データシートで塗りつぶしや太字も反映したいのですが

が 条件付き書式で表せる様な判定基準が明確であるなら、その条件を VBA化する事が出来るでしょう。

投稿日時: 22/01/05 21:27:51
投稿者: まさ@まさ

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

トピックに返信