[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『別ファイルのリストに基づいて置換するマクロ』（小心者）
保存してあるエクセルのリストに基づいて、開いているファイルの文字を置き換える処理がしたいです。
開いたファイルにある全シートを１度に処理できるのが理想です。
見よう見まねでＶＢＡを作ってみました。
参考にしたサイト：https://oshiete.goo.ne.jp/qa/5445568.html
リスト.xlsxが開かれ、「インデックスが有効範囲にありません。」となります。
「'」部分は自分で考えていれましたが、「いいい」が何をしているのか不明…
解決に力を貸してください<(_ _)>
リスト.xlsx ●…半角スペース、☒…全角スペース
Ａ Ｂ Ｃ
1 ●+ ☒ （連続する半角スペースは全角に）
2 売上げ高 売上高
3 売り上げ 売上げ
4 取り引き先 取引先
5
つづく…
Sub テスト()
Workbooks.Open "デスクトップ\リスト.xlsx"
Dim あああ As Long '数…A列
Dim いいい As Range '範囲
Dim ううう As Workbook 'ブック
Dim えええ As Worksheet 'シート
Dim おおお As Long '数…A列の行カウント
Set ううう = ThisWorkbook
For Each えええ In ううう.Worksheets
For おおお = 1 To あああ
'「おおお」は 1 から「あああ」まで
えええ.Cells.Replace _
'えええ（シート）のセルを置換
What:=いいい(おおお, 1).Value, _
'置換前文字（A列）を
Replacement:=いいい(おおお, 2).Value, _
'置換後文字（B列）に
LookAt:=xlPart, SearchOrder:=xlByColumns
Next おおお
Next
Workbooks("デスクトップ\リスト.xlsx").Close
End Sub
よろしくお願いします！
＜ 使用 Excel：Excel2010、使用 OS：Windows10 ＞
そして、「いいい」はRange型のようですが、それがどのセル範囲なのか代入している箇所が無いように見えますよ。 代入忘れを見つけるくらい、ご自身でステップ実行し、デバッグしてください。
（???） 2020/07/08(水) 16:51
判らないこそ、参考にした元のコードそのままから始めて、少しづつ変えて動かしてみては？ 誰も、全角変数なんて使ってなかったですよね？
そして、ブレークポイントを張って、ステップ実行しデバッグする、という事も調べて、実践してみてください。 いっぱい書いて、動かして、いきなりちゃんと動作するコードなんて、そうそう書けるもんじゃないですよ。 みんな、いっぱいデバッグしてバグを見つけて直して、またデバッグして…というのを繰り返すことで、コードを完成させているのです。
（???） 2020/07/08(水) 18:53
参照サイトとは反対に、開いているブックの置き換えです。
'対象ブックOpen…は不要かと思ったのですが、そこをいじってみようと思います。
元のコードをコピペして、もう一度作成してみたいと思います。
（小心者） 2020/07/09(木) 11:16
これは、文字列「Rainy」を「Sunny」に置換するテストコードです。
まずこれを理解してから、次にいってください。
Sub テスト()
'VBE画面でAltを押しながら「V」→「S」と押してローカルウィンドウを表示してから
'ステップ実行(F8キー)で動作を確認してください
Stop
'オブジェクト変数
Dim Wb As Workbook 'ブック
Dim Ws As Worksheet 'シート
'変数
Dim bfStr As String '置換前文字
Dim AfStr As String '置換後文字
bfStr = "Rainy" '変数bfStrに「Rainy」を代入
AfStr = "Sunny" '変数afStrに「Sunny」を代入
Set Wb = ThisWorkbook 'オブジェクト変数WbにThisWorkbook（このマクロを書いたブック）を代入
For Each Ws In Wb.Worksheets 'ブックWbの中のワークシートを巡回（ワークシートをオブジェクト変数Wsに代入）
Ws.Cells.Replace What:=bfStr, Replacement:=AfStr, LookAt:=xlPart, SearchOrder:=xlByColumns
'Ws（シート）のセルを置換
'置換前文字bfStrを
'置換後文字afStrに
'「LookAt:=xlPart」は部分一致
'「SearchOrder:=xlByColumns」は縦方向に検索
Next Ws
End Sub
（子つばめ） 2020/07/09(木) 14:00
Sub テスト2()
'VBE画面でAltを押しながら「V」→「S」と押してローカルウィンドウを表示してから
'ステップ実行(F8キー)で動作を確認してください
Stop
'置換前文字「Rainy」、置換後文字「Sunny」でテスト1マクロを実行
Call テスト1("Rainy", "Sunny")
End Sub
Sub テスト1(bfStr As String, AfStr As String) 'すべてのシートのbfStrをafStrに置き換える
Dim Wb As Workbook
Dim Ws As Worksheet
Set Wb = ThisWorkbook
For Each Ws In Wb.Worksheets
Ws.Cells.Replace What:=bfStr, Replacement:=AfStr, LookAt:=xlPart, SearchOrder:=xlByColumns
Next Ws
End Sub
（子つばめ） 2020/07/09(木) 14:01
Sub テスト2の2()
'置換前文字と置換後文字列を配列変数に納める(1)
Dim RpList(1 To 2) As String
RpList(1) = "Rainy"
RpList(2) = "Sunny"
Call テスト1(RpList(1), RpList(2))
End Sub
Sub テスト2の3()
'置換前文字と置換後文字列を配列に納める(2)
Dim RpList(1 To 2, 1 To 2) As String
RpList(1, 1) = "Rainy"
RpList(1, 2) = "Sunny"
RpList(2, 1) = "Black"
RpList(2, 2) = "White"
Call テスト1(RpList(1, 1), RpList(1, 2))
Call テスト1(RpList(2, 1), RpList(2, 2))
End Sub
Sub テスト2の4()
'ループ処理(1)
Dim RpList(1 To 2, 1 To 2) As String
RpList(1, 1) = "Rainy"
RpList(1, 2) = "Sunny"
RpList(2, 1) = "Black"
RpList(2, 2) = "White"
Dim i As Long
For i = 1 To 2
Call テスト1(RpList(i, 1), RpList(i, 2))
Next i
End Sub
Sub テスト2の5() 'ループ処理(2)
Dim RpList(1 To 2, 1 To 2) As String
RpList(1, 1) = "Rainy"
RpList(1, 2) = "Sunny"
RpList(2, 1) = "Black"
RpList(2, 2) = "White"
Dim i As Long
Dim l As Long
Dim u As Long
l = LBound(RpList, 1) '配列の最小の値
u = UBound(RpList, 1) '配列の最大の値
For i = l To u
Call テスト1(RpList(i, 1), RpList(i, 2))
Next i
End Sub
この3〜5は同じことをしています。
ループ処理をしている／していない
ループの始点・終点が静的／動的
の違いがあります。
また、これらの改変をしている間、「テスト1」をそのまま使うことができています。
これもとても重要です。
（子つばめ） 2020/07/09(木) 14:04
（このようなことはVariant型の変数でのみできます）
「Variant型の変数 = セル範囲」にすると、このような配列データになります。
Sub テスト2の6()
'VBE画面でAltを押しながら「V」→「S」と押してローカルウィンドウを表示してから
'ステップ実行(F8キー)で動作を確認してください
Stop
'「置き換えリスト」シートをつくる
Call テスト3
Dim RpList As Variant
'「置き換えリスト」シートのA1:B2セルのデータを変数RpListに代入
RpList = Worksheets("置き換えリスト").Range("A1:B2").Value
Dim i As Long
For i = LBound(RpList, 1) To UBound(RpList, 1)
Call テスト1(RpList(i, 1), RpList(i, 2))
Next i
End Sub
Sub テスト3()
'置き換えリスト用のシートをつくる
Worksheets.Add
ActiveSheet.Name = "置き換えリスト"
Range("A1").Value = "Rainy"
Range("B1").Value = "Sunny"
Range("A2").Value = "Black"
Range("B2").Value = "White"
End Sub
ただ、このコードには大きな問題があります。
最後まで実行してみるとわかります。
（子つばめ） 2020/07/09(木) 14:19
Withについてはこちらを参考にしてください。
http://officetanaka.net/excel/vba/beginner/16.htm
Sub テスト3の2()
With Worksheets.Add
.Name = "置き換えリスト"
.Range("A1").Value = "Rainy"
.Range("B1").Value = "Sunny"
.Range("A2").Value = "Black"
.Range("B2").Value = "White"
End With
End Sub
SubをFunctionに変えます。
SubとFunctionの違いは、「返り値」があるかどうかです。
値を返してくれるのがFunctionで、返さないのがSubです。
ついでに新たに作成したシートを消すコードを追加します。
Worksheets.DeleteとApplication.DisplayAlertsについてはこちらを参考にしてください。
http://officetanaka.net/excel/vba/sheet/sheet04.htm
Function テスト3の3() As Variant
'Subとの違いに注目↑
With Worksheets.Add
.Name = "置き換えリスト"
.Range("A1").Value = "Rainy"
.Range("B1").Value = "Sunny"
.Range("A2").Value = "Black"
.Range("B2").Value = "White"
テスト3の3 = .Range("A1:B2")
'Subとの違いに注目
'Application.DisplayAlerts = False '上の記事を理解してからこの行のコメントアウトを解除してください。
.Delete
'Application.DisplayAlerts = True '上の記事を理解してからこの行のコメントアウトを解除してください。
End With
End Function
Functionで出した値を使えるように、テスト2を書き換えます。
Sub テスト2の7()
'VBE画面でAltを押しながら「V」→「S」と押してローカルウィンドウを表示してから
'ステップ実行(F8キー)で動作を確認してください
Stop
Dim RpList As Variant
'「置き換えリスト」シートのA1:B2セルのデータを変数RpListに代入
RpList = テスト3の3
Dim i As Long
For i = LBound(RpList, 1) To UBound(RpList, 1)
Call テスト1(RpList(i, 1), RpList(i, 2))
Next i
End Sub （子つばめ） 2020/07/09(木) 14:40
最終セルの取得についてはこちらを参考にしてください。
http://officetanaka.net/excel/vba/tips/tips130.htm
Function テスト3の4() As Variant
Dim LastRow As Long
With Worksheets.Add
.Name = "置き換えリスト"
.Range("A1").Value = "Rainy"
.Range("B1").Value = "Sunny"
.Range("A2").Value = "Black"
.Range("B2").Value = "White"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
テスト3の4 = .Range("A1:B" & LastRow)
.Delete
End With
End Function
同一ファイル内ではなく、別のファイルを開いてデータをもらいます。 (ファイルパスは仮の名前にしてあります。)
Function テスト3の5() As Variant
Dim Wb As Workbook
Dim LastRow As Long
Set Wb = Workbooks.Open("C:\Users\Admin\Desktop\Book1.xlsx")
With Wb.Worksheets.Add
.Name = "置き換えリスト"
.Range("A1").Value = "Rainy"
.Range("B1").Value = "Sunny"
.Range("A2").Value = "Black"
.Range("B2").Value = "White"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
テスト3の5 = .Range("A1:B" & LastRow)
End With
Wb.Close SaveChanges:=False
End Function
Application.DispLayAlertsは消去したので、必要に応じ書き加えてください。
あとはもう少しなので、ご自身で仕上げてみてください。
（元データがあるなら、Worksheets.Addや.Deleteは必要ないだろうと思います。）
どのように仕上げたのか見せてもらえると、とてもうれしいです。
（上記コードはシートの削除のコード等があるので、テストはバックアップをとってから実行することをおすすめします。）
（子つばめ） 2020/07/09(木) 14:56
■１
＞当てはめる適切な文字がわからず、英語だと紛れてしまうのでそれ以外にしようと思ったのです。
ちょっと勘違いされているかもしれませんが、ExcelVBAで変数に日本語を使うことは基本的には問題は無いです。
ただ、先般Microsoft社が配布したアップデートに、コード中に日本語が使われているとコード自体が削除されるという凶悪なバグがあり騒ぎになったばかりなのです。
他にも、日本語に対応したバージョンのExcelでないと正しく読めない等のリスクはあるわけで、総合して???さんは"意味の無い"ものなら「aaa」「bbb」などでもよく、デメリットしか無い方法はおすすめしませんよと仰っているのだとおもいます。
また、原則として変数名は好き勝手につけてよいものですが、システムが使う単語と同じ物は使用できません。
（システムが使う単語は予約語と呼んだりします）
ExcelVBAの予約語は基本的に英単語なので、日本語で変数名を付けるとばうっかり予約語を使ってしまうというリスクを回避できるというメリットはあります。
従って、注釈コメント付けるのでは無く、読んだらわかる日本語変数名にするのは私もアリだとおもいます。
■２
＞マクロさわったことがなく、なにもわかりません。
始めは、みんな同じです。
参考となるコードの提示があったら、１行ずつ実行してみてどのような処理をおこなっているのか研究してみるとよいとおもいます。
１行ずつコードを実行するには「ステップ実行」という方法を使います。
「ブレークポイント」とは
https://www.tipsfound.com/vba/01010
https://www.moug.net/tech/exvba/0150019.html
「ステップ実行」とは
https://www.239-programing.com/excel-vba/basic/basic023.html
http://plus1excel.web.fc2.com/learning/l301/t405.html
■３
踏まえて、提示のコードを想像込みで手直ししてみるとこんな感じでしょうか。
（コンパイルエラーにならないことしかチェックしてないのでミスっていたらごめんなさい。）
Sub テスト改()
Dim 対象シート As Worksheet
Dim カウント用 As Long
Dim リスト範囲 As Range
Dim tmp As Boolean
Stop 'ブレークポイントの代わり
'▼置換対照表を読み込む（セル範囲を取得する）
With Workbooks.Open("デスクトップ\リスト.xlsx").Worksheets(1)
Set リスト範囲 = .Range("A2:B", .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
'▼自ブックの全シートを巡回して...
For Each 対象シート In ThisWorkbook.Worksheets
'// ...リスト範囲の行数分繰り返し処理をする
For カウント用 = 1 To リスト範囲.Rows.Count
tmp = 対象シート.UsedRange.Replace( _
What:=リスト範囲.Cells(カウント用, 1).Value, _
Replacement:=リスト範囲.Cells(カウント用, 2).Value, _
LookAt:=xlPart)
Next カウント用
Next 対象シート
'▼「リスト範囲」の親の親（つまり開いたブック）を閉じる
リスト範囲.Parent.Parent.Close False
End Sub
（もこな２ ） 2020/07/09(木) 15:54
RpListに躓いて↓を眺めている段階です。
配列変数
http://officetanaka.net/excel/vba/variable/07.htm
何が理解できてなくて、何を理解とするのか…
完成に向かって徐々に進んでいこうと思います。
（小心者） 2020/07/09(木) 17:31
なんで、oshiete.goo で質問しないで、他サイトで質問するの？ （Why） 2020/07/09(木) 17:52
数日、課題の方に取り組んだのですが、どうにも進まず…
理解が及ばず、お見せできる成果がありません。
手直し頂いた方を試してみます。
順番が変わってしまい、すみません！
引き続き頑張ります。
（小心者） 2020/07/15(水) 17:12
１行ずつ試していくと、Set〜のところで
「1004 アプリケーション定義またはオブジェクト定義のエラーです。」
となるので、調べていて質問があります。
tmp…Booleanは真偽値の型
（https://wa3.i-3-i.info/word14970.htmlより）
tmp〜※
「対象シート」ブック全体置換
「リスト範囲」A列を「リスト範囲」B列に部分一致置換
繰り返し
という内容だと思うのですが理解は大丈夫でしょうか？
※部分は、「対象シート」に
置換文字あり…「真」A列→B列に置き換える
置換文字なし…「偽」なにもしない でしょうか？
（小心者） 2020/07/20(月) 17:11
誤 Set リスト範囲 = .Range("A2:B", .Cells(.Rows.Count, "A").End(xlUp).Row)
↓
正 Set リスト範囲 = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
■５
＞〜理解は大丈夫でしょうか？
微妙に違います。
(1)「対象シート」にシートを１つ取り出してセットする
For Each 対象シート In ThisWorkbook.Worksheets
(2) 対象シートの使用しているセル範囲を対象にReplaceメソッドを実行する
対象シート.UsedRange.Replace 〜〜
これを、全シート分繰り返しています。
「tmp」は(2)の部分で括弧で括ったために返り値が返ることになり、なにかに代入する必要があったために用意したのですが、よくよく考えれば括弧を付けなければいいだけでした。
■６
ということで、コードを修正。
Sub テスト改_修正()
Dim 対象シート As Worksheet
Dim カウント用 As Long
Dim リスト範囲 As Range
Stop 'ブレークポイントの代わり
'▼置換対照表を読み込む（セル範囲を取得する）
With Workbooks.Open("デスクトップ\リスト.xlsx").Worksheets(1)
Set リスト範囲 = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
'▼自ブックの全シートを巡回して...
For Each 対象シート In ThisWorkbook.Worksheets
'// ...リスト範囲の行数分繰り返し処理をする
For カウント用 = 1 To リスト範囲.Rows.Count
対象シート.UsedRange.Replace _
What:=リスト範囲.Cells(カウント用, 1).Value, _
Replacement:=リスト範囲.Cells(カウント用, 2).Value, _
LookAt:=xlPart
Next カウント用
Next 対象シート
'▼「リスト範囲」の親の親（つまり開いたブック）を閉じる
リスト範囲.Parent.Parent.Close False
End Sub
（もこな２） 2020/07/20(月) 22:26
お手数ですが、もう１点伺いたいです。
コードをPERSONALに保存し、置換したいブックを開いて実行する、というのは難しいでしょうか？
調べたところ、「ActiveWorkBook」を使うようですが
ThisWorkbook.Worksheets
↓
ActiveWorkbook.Worksheets に変更するだけでは置き換えならず…
周囲に自慢したところ、使いたいという人が複数おり、
置換したいファイルにその都度、コードを貼り付けるのは手間だと言われまして…
自分でももう少し調べてみますが、途中経過とお礼を伝えたくて書き込みました。
本当にありがとうございました！
（小心者） 2020/07/21(火) 17:38
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.