Excelファイル連続置換vba

質問

Excelで複数の文字列を一気に置換するvbaを教えていただけないでしょうか。
Book2の置換リストをBook1のSheet1に完全一致で置換したいのです。
Book1.xlsx：元ファイル
Book2.xlsx：置換リスト
※下記のような配列
A列　B列
１　01_1
２　02_2
３　03_3
４　04_4
以上です。
よろしくお願いいたします。

回答No.7

nekotaro3 · ベストアンサー

置換リストブック.xlsmのSheet1のA列→検索語
その右隣のB列→置換する文字列
置換するブック.xlsmのSheet1のA列→検索して置換する範囲
とします。
※最右端の列と、その左隣の列を、関数を入れる列として一時的に使用します。
xl2013の場合、XFC:XFD列を使います。

Sub 複数の置換()
Dim bk1 As Workbook
Dim bk2 As Workbook
Dim LR1, LR2, AD, V
Set bk1 = Workbooks("置換リストブック.xlsm")
Set bk2 = Workbooks("置換するブック.xlsm")
LR1 = bk1.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = bk2.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
bk2.Activate
AD = Cells(1, Columns.Count).Address(False, False)

bk2.Sheets("Sheet1").Cells(1, Columns.Count).Resize(LR2, 1).Formula = _
"=INDEX([" & bk1.Name & "]Sheet1!B$1:B$" & LR1 & _
",MATCH(a1,[" & bk1.Name & "]Sheet1!A$1:A$" & LR1 & ",0))"

With Cells(1, Columns.Count - 1).Resize(LR2, 1)
.Formula = "=IFERROR(" & AD & ",a1)"
V = .Value
.Resize(, 2).ClearContents
End With

Cells(1, 1).Resize(LR2, 1) = V
End Sub

お礼

返事ありがとうございました。
提案いただいた置換関数を確認したところ、やはり完全一致がうまくいきませんでした。（私の理解力が悪いと思います。申し訳ありません。）そこで、Vlook関数を使用し、一致したものを上書きするというvbaでできるようになりました。いろいろご提案ありがとうございました。

回答No.6

hira_kazu

私の勘違いだったらすいませんが...
VBAでしか出来ないと勘違いはしてませんよね？
A列の内容を見てB列の内容に置換するぐらいなら
違うBookだとしてもExcel内の関数で簡単にできますよ^^

ボタンを押した時に結果が出て欲しいとかであれば、
いっそのことExcel関数でどこかの列に結果を求めておいて、
その結果をコピーしてくる処理だけを書いたらVBAとしてはかなり簡単です。

あまり詳しくないうちにVBAで置換の処理を書こうと思うと、
データ量によっては検索時間が膨大になる可能性もあります。
二分法とかのアルゴリズムを使えばまだマシになりますが、
そのような知識がないのであればExcel関数を使う方が断然早いです。

今回の例をそのままシートとみなして置換するなら...
=VLOOKUP(置換元, A1:B4, 2, FALSE)
とかでもいいですし、ほかにもMATCHとINDEXもしくは
MATCHとOFFSETを使う方法もあります。
※A1:B4の部分は置換リストがある範囲を参照

列のコピーをVBAでやるなら、マクロの記録を使うのが一番手っ取り早いです。
もちろん、
for i = 1 to 100 ※
book1.shee1.Cells(i, "D") = book2.sheet1.Cells(i, "E")
next i

※next iまでの区間をi を1から100まで加算しながら繰り返せという意味
みたいなコードでもE列からD列まで100セル分をコピーできます。
少し上のコードでは遅い時がありますが...

お礼

ご提案ありがとうございます。
対象となる表が、一列だけだったらよかったのですが、実際は、50シートで、それぞれ10列づつあるようなEXCELでしたので、vbaでなければちょっと厳しそうだったので、投稿させていただきました。結果としては、vlook関数を使用したvbaで置換できるようになったので、アドバイスはとても助かりました。ありがとうございました。

回答No.5

imogasi

#2ですが、それとは無関係に、
Ｂｏｏｋ２に、置換に関する情報が入っているということらしいが、変換前ー後の対の数は多いのですか。
配列と言っているのに、Ａ列、Ｂ列とはなに？。配列に入れて処理するのだろう、ということ？
言いたいのは、実際に近い例を挙げて、したいことを文章で書いて質問すべきだ。質問者の設計（解決構想、これが往々にして頼りないように思う場合がある）が、変に入り込んでいないか？。
１を０１＿１にしたいということだが、１は文字列か？
１という文字など、データのいろんなところに出てくる可能性があり、単純にエクセルの置換では余分なものまで、置換してしまわないか？少なくとも、元データの列ぐらいは指定して限定しないとと思った次第。セルデータの先頭の意味的に項目番号の１とか、言うことはできないのか。
それならＩＮＳＴＲ関数で探した方がよいかもしれないと想像する。正規表現までは大げさだと思うが。
この例はあくまで、質問者が実際データから思い付いた例に過ぎないのか。変に実例から質問のモデル例を作ると読者は誤解する。
ーー
ＷＥＢに載っているコードでやってみたということらしいが、正確な回答をもらうためには、どういう不都合が起こったのか書いておいて質問すべきだと思う。

回答No.4

kkkkkm

No3です。
こちらに変更してください。

Dim Wb1 As Workbook, Wb2 As Workbook
Dim MySheetName As String

Set Wb1 = Workbooks("Book1.xlsm")
Set Wb2 = Workbooks("Book2.xlsx")

Set 商品リスト = Wb1.Worksheets("新商品")
MySheetName = 商品リスト.Range("D2")
Set 変更データ = Wb2.Worksheets(MySheetName)

回答No.3

kkkkkm

> Book1.xlsx：元ファイル→新商品
> Book2.xlsx：置換リスト→データ
> ページ内では、シート名を「データ」というようにvba内に固定していましたが、「データ」シートにシート名を入力できるようにし、変更が自由にする事は可能でしょうか。

マクロがBook1に書かれているとしてシート名をBook1の新商品シートのセルD2に記載するとしたら、記載してただいたページのコードの一部を以下に変更してください。

Dim Wb1 As Workbook, Wb2 As Workbook
Dim MySheetName As String

Set Wb1 = Workbooks("Book1.xlsm")
Set Wb2 = Workbooks("Book2.xlsx")

MySheetName = Wb1.Worksheets("新商品").Range("D2")
Set 商品リスト = Wb1.Worksheets("新商品")
Set 変更データ = Wb2.Worksheets(MySheetName)

回答No.2

imogasi

>複数の文字列を一気
「AをＢで置換する」、を１対として、希望するだけの対の回数を繰り返さないといけないと思う。質問者の言うように言うのは簡単だが、複数対を１つのＲＵＮの実行ではできないだろう。置換のＶＢＡのコードなどＧｏｏｇｌｅででも照会すればすぐ出てくるだろう。それを配列の要素分だけ繰り返せば仕舞だろう。
質問者は、どれほどやってみたのか。丸投げではないのか。
http://www.thespreadsheetguru.com/the-code-vault/2014/4/14/find-and-replace-all
など近いのではないか。
一部改変
Sub Multi_FindReplace()
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long
fndList = Array("a", "b", "c", "d")
rplcList = Array("x", "y", "z", "u")
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
Next x
End Sub
例データ
Ｓｈｅｅｔ１で
元データ
a
　　 b
　 　 c
d
置換後
Ｓｈｅｅｔ１
ｘ
　　 y
　 　 z
u

お礼

早急な回答ありがとうございます。
自分なりに様々なサイトを閲覧し、似ているvbaを扱ってみました。
しかし、途中でデバック等が発生し上手く起動せず質問させていただいた次第です。丸投げのような形を取ってしまい申し訳ございません。
ご回答ありがとうございます。

回答No.1

kkkkkm

> Book2の置換リストをBook1のSheet1に完全一致で置換したいのです

具体的にどのような置換をしたいのかよくわかりません。「下記のような配列」のデータで元ファイルのデータと一致したものを置換(何に置換する?)したいのか、「下記のような配列」が置換リストでそれに一致する元ファイルのデータを置換したいのか(とすれば何に置換する?)

置換元データはどこ?
完全一致の検索データは何?
一致したら何に置換する?

上記が全く分かりません。

どちらにしても、以下のページを参考にしてみてください。
http://kokodane.com/2013_macro_35.htm

補足

早急な回答ありがとうございます。
上記URL拝見しました。
私も投稿する前にマクロ講座36回を参照しておりました。
http://kokodane.com/2013_macro_36.htm
投稿内容でいうなら、下記のような割り当てとなります。
Book1.xlsx：元ファイル→新商品
Book2.xlsx：置換リスト→データ
ページ内では、シート名を「データ」というようにvba内に固定していましたが、「データ」シートにシート名を入力できるようにし、変更が自由にする事は可能でしょうか。