[[20170721203819]]　『リストによる複数語の置換をシート毎に順番に行い』（ゆう）　ページの最後に飛ぶ

『リストによる複数語の置換をシート毎に順番に行いたい』（ゆう）

リストは以下のスプレシートからマクロを実行できたらと思っています。
https://docs.google.com/spreadsheets/d/1ib06OW0fJjeMNzwsCknexKYVqW3M0Rwq341RdiO-si8/edit?usp=sharing
【おこないたいこと】
1.原本となるシートを30枚コピー【原本となるシートを含めてシートタブには31枚;すべて同じ内容です】。
2.シートは絶対31枚です。
3.上記のアドレスのスプレシートからリストによる置換を行いたいです。範囲はすべてのシートでC9:BT52です。
4.A列はシートタブが左から何番目かを示す番号です
5.置換は、B列→C列へ置換。かつ　D列→E列へ置換。　かつ　F列をシートの名前にしたいです

例えば、シートタブの左からシート１枚目はB2からC２へ置換、かつ　D2からE２へ置換　かつF2のセルを左から１枚目のシートの名前とする
次は、シートタブの左からシート２枚目はB３からC３へ置換、かつ　D３からE３へ置換　かつF３のセルを左から２枚目のシートの名前とする。

補足：B列とD列はすべて同じ内容です

全然検討違いなのですが、以下の方法ではシート毎に毎回　文字列を設定しないといけなくて困ってます。
Sub ２つの語の置換()
Range("C9:BT52").Select
With Selection
LookAt:=xlWhole
.Replace What:="置換前文字列B列", Replacement:="置換後文字列C列"
.Replace What:="置換前文字列D列", Replacement:="置換後文字列E列"
End With
End Sub

＜使用 Excel：Excel2007、使用 OS：WindowsXP ＞

置き換えって言われてますが、

B列の値をC列にコピーして、
D列の値をE列にコピーしたらいいんですか？

それから、A列ってデータ行すべてにシート番号を入れているんですか？
F列も同様にデータ行前部に入れるんですか？

（まっつわん） 2017/07/21(金) 21:23

スプレシートにあるリストの中にA列があり、そこに仮にシート番号をA列に記載しています。そのリストを元に、違う原本となるブックのシート31枚を順番に置換していけたらと考えています。
原本となるブックのC9:BT52の範囲に、シート１枚目であれば、B2からC2へ置換。と　D2からE2へ置換です。
その歳にF2セルをシート一枚目の名前できるのであればうれしいです。
シート２枚目であれば、C9:BT52の範囲に、B3からC3へ置換　と　D3からE3へ置換です。F３セルがシート２枚目の名前になればといいなと思います。シート3枚目であれば、C9:BT52の範囲に、B4からC4へ置換　と　D4からE4へ置換です。F4セルがシート3枚目の名前になればといいなと思います。
説明不足ですみません。

（ゆう） 2017/07/21(金) 21:38

リンク先のデータを見ていないので想像ですが
こういうことでしょうか。

 Option Explicit

 Sub test()
    Dim 検索文字B As String, 置換文字C As String
    Dim 検索文字D As String, 置換文字E As String
    Dim i As Long

    For i = 1 To 31
        置換文字C = Cells(i + 1, "C").Value
        置換文字E = Cells(i + 1, "E").Value
        Worksheets(i).Name = Cells(i + 1, "F").Value

        With Worksheets(i).Range("C9:BT52")
          .Replace What:=検索文字B, Replacement:=置換文字C, LookAt:=xlWhole
          .Replace What:=検索文字D, Replacement:=置換文字E
        End With
    Next

 End Sub

（マナ） 2017/07/22(土) 09:18

↑Worksheets(i)は、どのブックであるかしていしないとエラーになりそうです。

（マナ） 2017/07/22(土) 09:30

あぁ、そうか、リンク先にファイルがあるのか、、、、
知らない人のファイルを不用意に開ける人はいないかも、、、、

悪意があろうとなかろうと、得体のしれないファイルを開けるのは、
不要なウィルス感染の危険があるので。。。

所詮、マクロは言葉で作業を自動で行ってもらうよう書くだけなので、
まずは日本語で説明できるようになりましょう＾＾

まず、前提条件を明確にしましょう。
書き換えの文字のリストは、
コピーしたシートにはないですよね？

どのブックのどのシートのどのセル範囲に、シート毎の置き換え文字のリストがあり、
どのブックのどのシートのどのセル範囲に、文字を置き換えられる表があるのでしょうか？

（まっつわん） 2017/07/22(土) 09:42

 こんな感じ

 Sub test()
     Dim a, i As Long
     a = Sheets("sheet1").Cells(1).CurrentRegion.Value
     For i = 2 To UBound(a, 1)
         With Sheets(CStr(a(i, 6))).Range("C9:BT52")
             .Replace a(i, 2), a(i, 3), 2
             .Replace a(i, 4), a(i, 5), 2
         End With
     Next
 End Sub
（seiya） 2017/07/22(土) 09:59

まっつわんさんへ、確かに得体のしれないファイルかもしれないですね。すみませんでした。
前提条件として、コピーしたシートには書き換えの文字リストはありません。
[置換用文字リスト]のいうブックの[リスト]という名前のシートに以下ようにセルに入力しています。
A2からA32に、1から31の数字があります(これは、[予定表]とういうブックにすべて同じ内容のシート31枚あるのですが、シートの左から何番目になるかを指す数字です)。
B2からB32に検索文字Bがあります。C2からC32に置換文字Cがあります。
C2からC32に検索文字Cがあります。D2からD32に置換文字Dがあります。
F2からF32にシートの名前となる文字が入力しています。

[行いたいこと]
[予定表]ブックにある[7.1]という原本のシートを30枚コピー。すると、シートの名前は[7.1(2)]・・・[7.1(30)]と合計31枚になります。
[予定表]ブックのシートを単純に左から数えて順番に1つ目を、検索文字B2セルから置換文字C2セルに置換。
検索文字D2セルから置換文字E2セルに置換。F2セルに入力されている文字をシートの名前に置き換える。
次に、[予定表]ブックのシートを単純に左から数えて順番に2つ目を、検索文字B3セルから置換文字C3セルに置換。検索文字D3セルから置換文字E3セルに置換。F3セルに入力されている文字をシートの名前に置き換える。
同様に31回繰り返す作業を行いたいです。
最後は、[予定表]ブックのシートを単純に左から数えて順番に31番目を、検索文字B32セルから置換文字C32セルに置換。検索文字D32セルから置換文字E32セルに置換。F32セルに入力されている文字をシートの名前に置き換える。

説明不足があるようでしたら、言ってください。
（ゆう） 2017/07/22(土) 14:31

（マナ）さん、（seiya）さん、説明不足ですみません。
どのブックのどのシートのどのセル範囲に、シート毎の置き換え文字のリストがあり、
どのブックのどのシートのどのセル範囲に、文字を置き換えられる表があるのか伝えられずすみませんでした。
書いていただいたマクロを実行すると、上記の説明不足により、結果がずれてしまいました。
書いていただいたマクロを二つのブックを開いた状態で、ブックを指定して行えたらと奮闘中ですが、うまくいきません。
奮闘中ですが、置換文字リストのブックのシートに結果がでたりしています。
（ゆう） 2017/07/22(土) 15:01

 >[置換用文字リスト]のいうブックの[リスト]
 Sheet1 じゃなっかたの？

 よく分からんけど
 Extension .xlsx/m は必要に応じて用変更

 Sub test()
     Dim a, i As Long
     a = WorkBooks("置換用文字リスト.xlsm").Sheets("リスト").Cells(1).CurrentRegion.Value
     For i = 2 To UBound(a, 1)
         With Workbooks("予定表.xlsx").Sheets(i-1).Range("C9:BT52")
             .Replace a(i, 2), a(i, 3), 2
             .Replace a(i, 4), a(i, 5), 2
         End With
     Next
 End Sub
（ seiya） 2017/07/22(土) 16:57

（ seiya）さんありがとうございます。思った通りにできました。説明不足の中すごく感謝しています。
そこで、置換用文字リストのブックにあるリストシートの中でのF列のセルを予定表ブックのシートの名前に順番に置き換えることはできないでしょうか？
予定表ブックの左から１枚目のシートの名前を、置換用文字リストのF2セルに。
以降は、同様に2枚目のシートの名前を、置換用文字リストのF3セルに。
これを繰り返して行っていきたいです。

今でも凄く感謝しています。ありがとうございます。

（ゆう） 2017/07/22(土) 21:27

 最初に書いた方でいいのかな？

 >         With Workbooks("予定表.xlsx").Sheets(i-1).Range("C9:BT52")
 を
          With Workbooks("予定表.xlsx").Sheets(CStr(a(i, 6))).Range("C9:BT52")
（ seiya） 2017/07/22(土) 21:34

（ seiya）さん、度々ありがとうございます。
大変恐縮なのですが、私の説明不足もあるのですが、
置換用文字リストのF2セルを予定表のシートの左から一番目の名前に変えたいです。F3セルをシート２番目の名前に変えたいです。F4セルをシート３番目の名前に変えたいです。
繰り返し31回行い、F32セルをシート31番目の名前に変えたいです。

自分なりに頂いたマクロを以下のように変えてみました。恥ずかしながらマクロを以下に提示しています。
すごく初心者なので、F1セルがシート一番目の名前にきてしまいました。
F2セルをシート一番目にかえて、F３セルをシートの２番目に変えて、繰り返し名前を取得したいです。

Sub TEST()

     Dim a, i As Long
     a = Workbooks("置換用文字リスト.xlsx").Sheets("リスト").Cells(1).CurrentRegion.Value
     For i = 2 To UBound(a, 1)
          With Workbooks("予定表.xlsm").Sheets(i - 1).Range("C9:BT52")
             .Replace a(i, 2), a(i, 3), 2
             .Replace a(i, 4), a(i, 5), 2
             Worksheets(i - 1).Name = Workbooks("置換用文字リスト.xlsx").Sheets("リスト").Cells(i - 1, 6).Value

    End With
     Next
 End Sub
（ゆう） 2017/07/23(日) 07:47

 >置換用文字リストのF2セルを予定表のシートの左から一番目の名前に変えたいです。

 2行追加してください。

 Sub test()
     Dim a, i As Long
     a = WorkBooks("置換用文字リスト.xlsm").Sheets("リスト").Cells(1).CurrentRegion.Value
     For i = 2 To UBound(a, 1)
         With Workbooks("予定表.xlsx").Sheets(i-1).Range("C9:BT52")
             .Replace a(i, 2), a(i, 3), 2
             .Replace a(i, 4), a(i, 5), 2
             a(i, 6) = .Parent.Name  '<- ★
         End With
     Next
     WorkBooks("置換用文字リスト.xlsm").Sheets("リスト").Cells(1).CurrentRegion.Value = a '<-★
 End Sub
（ seiya） 2017/07/23(日) 10:04

seiyaさん、大変お世話になりました。このマクロは、思い描いていた通りの記述です。ありがとうございました。頂いたマクロなので、しっかりと意味を理解していきます。UBoundで返り値を取得するなんて初めてしりました。
（ゆう） 2017/07/23(日) 14:48

 私にとってコードを書きやすくするために配列を使用しているだけです。

 Rangeをそのまま使うとこのような記述になるでしょう...

 Sub test()
     Dim r As Range, i As Long
     Set r = Workbooks("置換用文字リスト.xlsm").Sheets("リスト").Cells(1).CurrentRegion.Columns("f").Cells
     '★　リストのF列を参照
     For i = 2 To rng.Count
         With Workbooks("予定表.xlsx").Sheets(i - 1).Range("C9:BT52")
             .Replace r(i, -3), r(i, -2), 2
             .Replace r(i, -1), r(i, 0), 2
             r.Value = .Parent.Name '<- ★
         End With
     Next
 End Sub
（ seiya） 2017/07/23(日) 15:23

 訂正

 上記の

 For i = 2 To rng.Count
 は
 For i = 2 To r.Count
 の間違いです。
（ seiya） 2017/07/23(日) 15:24

コメント返信：

[ 一覧(最新更新順) ]

[[20170721203819]] 『リストによる複数語の置換をシート毎に順番に行い』（ゆう） ページの最後に飛ぶ

[[20170721203819]]　『リストによる複数語の置換をシート毎に順番に行い』（ゆう）　ページの最後に飛ぶ