[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ブック全体の特定の文字列を複数置換したい』(さんま)
特定の文字列をブック全体置換したいです。
置換したい文字列が複数あります。
別ブックの参照シートのA列に置換後の文字列、B列に置換前の文字列が入っています。
それにならってアクティブブックの全シートのC列の値を置換したいです。
宜しくお願い致します。
< 使用 Excel:Excel2010、使用 OS:Windows7 >
置換したい文字は、文章の一部分ですか? それとも置換対象の文字のみ記入されていますか?
できるだけ具体的にデータ提示頂ければ誤解等なく、スムーズに問答できるのですが・・・ (稲葉) 2016/02/08(月) 11:11
置換したい文字は、文章の一部分ですか?
それとも置換対象の文字のみ記入されていますか?
↑置換対象の文字のみ記入されてます。
やりたいことは新しいシステムにデーターを移行する作業をしようとしてます。
C列に記入されている 多数のメーカー名を別ブック参照シートに明記してある数字に置き換えたいのです。
すみませんが、宜しくお願い致します。
(さんま) 2016/02/08(月) 11:16
別ブックのA列とB列を入れ替えて、 置換対象が入力されている表に一列追加し、 VLOOKUPで表示させた後、値に置き換える方法はどうですか? (稲葉) 2016/02/08(月) 11:38
その方法も考えたのですが、ボタン一つで作業ができればとかんがえておりました。
やはり難しいでしょうか??
(さんま) 2016/02/08(月) 13:02
Private Sub CommandButton1_Click() Dim myDIC As Object Dim i As Long Dim j As Long Dim D As Variant Dim K As Variant
Set myDIC = CreateObject("Scripting.Dictionary") myDIC.CompareMode = vbTextCompare
With Workbooks.Open(ActiveWorkbook.Path & "\別ブック.xlsx", False, True) With .Sheets("参照シート") For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row If Not myDIC.Exists(.Cells(i, "A").Value) Then myDIC.Add .Cells(i, "A").Value, .Cells(i, "B").Value End If Next i End With .Close End With
D = myDIC.Items K = myDIC.keys
For i = 1 To Sheets.Count If Sheets(i).Name <> Me.Name Then With Sheets(i) For j = 0 To myDIC.Count - 1 .Range("A1", .Cells.SpecialCells(xlLastCell)).Replace K(j), D(j), xlPart Next j End With End If Next i End Sub (???) 2016/02/08(月) 13:16
一回限りだよね? 問答してる時間、というか10分の作業で済むことを人の手を借りてなんとかしようってのがおかしい・・・ 関数なら間違えたことに気づくけど、VBAで出力した結果を検証する時間のほうが長いと思うのだが。 (稲葉) 2016/02/08(月) 14:12
ところで、A列とB列の関係を逆に考えていました。すいませんが、"A"と"B"を逆にしてください。
なお、C列に限らず、全セルを変換するコーディングになっています。都合が悪い場合は、
Range指定してる箇所を調整してください。
(???) 2016/02/08(月) 14:31
ブック一つに対して変換作業を行う回数は1回限りなんですが、変換作業するブックが300あたりあります。
それと???さんの推測通り、対象文字列が50弱あるので。。。。
なるほど!! A列B列が反対なんですか。調整します。
C列と指定しましたが、全て対象でも問題ないのでそもまま使わせて頂きます。
それと、
If Sheets(i).Name <> Me.Name Then
↑のMEの対象がありません。と出てしまうので、Me.Nameを Activesheet にしてみたのですが、
うまくいきません。。。
このVBAの置き場所はActivebookでいいのですよね???
(さんま) 2016/02/08(月) 14:55
ブックが多数あるというならば、辞書のあるブックにマクロを記述し、ボタン一発で指定フォルダ以下全てのブックの文字列置換を
する方が楽だと思いますよ。300回もボタン貼って、マクロコピーして、ボタンを押す作業をするのでしょうか?
(???) 2016/02/08(月) 15:10
考察不足でした。。その通りですね。。
私の考えではエクセルのリボンにVBAを登録しておいてその作業の時にvbaを呼び出して作業!
という考えでした。
確かに???さんの言う通り、一回の作業で済めば一番いいです。
とりあえず、自分なりにVBAを考えてみたいと思います。
アドバイス有難うございました。
(さんま) 2016/02/08(月) 15:20
完全一致の場合と指定することはできますか???
(さんま) 2016/02/08(月) 18:18
横から失礼します。
>>ただ今作成平成日付を西暦に変換するVBAも組んでいるのですが、実行すると ・・・H22→H2+2と認識されてしまい19902となってしまいます。
そのコードをアップしてもらわないことにはコメントができませんねぇ。 正しくロジックを組めば正しく変換されるはずですので。
あぁ、それと、変換対象の 平成なんとか というデータは 文字列なんですかね? それとも、日付型データで、表示書式で 平成何とか と表示されているだけなんですかね?
(β) 2016/02/08(月) 22:05
H22の文字列ですが、セル中の文字列の一部ではなく、指定単語のみ格納されているならば、
変換文字列の先頭に「'」を付けてみてはいかがでしょうか。
(???) 2016/02/09(火) 09:21
???様が作成して頂いたvbaを少し変更して作ってみましたが、やはり問題解決されません。。。
β様
あぁ、それと、変換対象の 平成なんとか というデータは 文字列なんですかね?
それとも、日付型データで、表示書式で 平成何とか と表示されているだけなんですかね? ↑文字列です。H22 という形で文字列で入っております。
???様
私のサンプル中で、myDIC.CompareMode = vbTextCompare という行があります。
これは変換対象をテキスト扱い、つまり大文字小文字の区別無く変換する指定。
この行を消せば、デフォルトのバイナリ指定になり、完全一致した場合だけ置換されます
↑これを削除してみたのですが、できませんでした。。。
ちなみに指定単語のみの格納です。
’とつけるということは表中のすべてのH〇〇というところを’H〇〇にするということでしょうか???
Sub 西暦変換()
Dim myDIC As Object Dim i As Long Dim j As Long Dim D As Variant Dim K As Variant
Set myDIC = CreateObject("Scripting.Dictionary") ' myDIC.CompareMode = vbTextCompare
With Workbooks.Open("\\モジュール\西暦に変更.xlsx", False, True) With .Sheets("西暦変換") For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row If Not myDIC.Exists(.Cells(i, "A").Value) Then myDIC.Add .Cells(i, "A").Value, .Cells(i, "B").Value End If Next i End With .Close End With
D = myDIC.Items K = myDIC.keys
For i = 1 To Sheets.Count With Sheets(i) For j = 0 To myDIC.Count - 1 .Range("A1", .Cells.SpecialCells(xlLastCell)).Replace D(j), K(j), xlPart Next j End With Next i End Sub
やはり
H22→H2+2と認識されてしまい19902となってしまいます。
(さんま) 2016/02/09(火) 13:40
Sub test() Range("A1", Cells.SpecialCells(xlLastCell)).Replace "平成22", "H22", xlPart End Sub (???) 2016/02/09(火) 14:22
問題の文字列を新規ブック、新規シートに転記し、シートモジュールに以下のマクロを貼り、再現確認してみてください。
↑再現確認してみたところ、特になにも起こりませんでした。
再現する変換元と先、セルに入力されている文字列と書式設定を教えてください。
↑
再現する変換元データー
A列 B列
1965 S40
1966 S41
1967 S42
1968 S43
. .
. .
. .
2016 H28
変換先データー
A列、B列は日付等と関係ない文字列がはいっています。
C列
H24
H22
H20
S41
S48
.
.
.
↑このように形式は標準で入っております。
例えばH22ならば2010に変換したいのですが、19902(つまりH2と2と認識される)になってしまうというわけです。
すみませんがアドバイス宜しくお願い致します。
(さんま) 2016/02/09(火) 14:36
年の古いものから並んでいますが、それだとH22より前に、H2とマッチしてしまいますよね?
変換表を降順で並び替えてから、置換実行してみてください。
(???) 2016/02/09(火) 14:53
なんと!!そういうことですか!!!
ただ今変換表を降順に並び替えてから実行したところ、思った通りに出来ました!
無事に解決しました。
本当にありがとうございました。
感謝しております。
また何かありましたら宜しくお願い致します。
(さんま) 2016/02/09(火) 15:31
ネットワークドライブ上のファイルを対象に変換するようですが、速度のためと、
失敗した場合を考えて、ローカルにファイルコピーしてから試してください。
西暦変換シートにActiveXのボタンを貼り付け、以下のマクロとしてください。
マクロ入りになるので、ブックはxlsmファイル保存してください。
実行すると、カレント以下のサブフォルダも含め、全て文字列置換します。
Private Sub CommandButton1_Click() Dim myDIC As Object Dim i As Long Dim j As Long Dim k As Long Dim DT As Variant Dim KY As Variant Dim cFiles As Variant
Set myDIC = CreateObject("Scripting.Dictionary") ' myDIC.CompareMode = vbTextCompare
With Sheets("西暦変換") For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row If Not myDIC.Exists(.Cells(i, "B").Value) Then myDIC.Add .Cells(i, "B").Value, .Cells(i, "A").Value End If Next i End With
DT = myDIC.Items KY = myDIC.keys
cFiles = Split(CreateObject("WScript.Shell").Exec("CMD /C DIR /A:-D/B/S """ & ActiveWorkbook.Path & "\*.xls*""").StdOut().ReadAll(), vbNewLine) For i = 0 To UBound(cFiles) - 1 If Not Mid(cFiles(i), InStrRev(cFiles(i), "\") + 1) Like "*" & Me.Parent.Name Then With Workbooks.Open(cFiles(i)) For j = 1 To .Sheets.Count With .Sheets(j) For k = 0 To myDIC.Count - 1 .Range("A1", .Cells.SpecialCells(xlLastCell)).Replace KY(k), DT(k), xlPart Next k End With Next j .Save .Close End With End If Next i End Sub (???) 2016/02/09(火) 16:03
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.