VBAによる数値変換がうまくいきません

質問

仕事で必要なIDの変換ツールを、EXCELを使って作っています。
いつもは手動で変換しているのですが、頻繁に作業を行わなければならないのと、作業が煩雑で入力ミスも多いため、それを防ぐためにプログラムで自動化してしまおうと考えています。

変換前　　変換後
　1000　　　1001
　1001　　　1001
　1002　　　1002
　1003　　　1002
　1004　→　1001
　1005　　　1001
　1006　　　2002
　1007　　　1003
　1008　　　1001
　1009　　　1004
　1010　　　1099

このような4～５桁の数字を、変換表にしたがってマクロで自動変換するように、ネットで見つけたVBAのプログラム文に手を加え作っているのですが、一部の数字がうまく変換できません。

・数字の変換に関して、特に法則のようなものはありません。すべて変換表にしたがって変換しています。
・変換しなければならないIDの数は、100個以上あります。

現在のVBAのプログラムは、以下の通りです。

Sub 変換()
Dim ws As Worksheet
Dim idx As Long
Application.ScreenUpdating = False
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "変換後"
Set ws = ActiveSheet
With Sheets("変換表")
For idx = 1 To .Cells(65536, 1).End(xlUp).Row
If .Cells(idx, 1) <> "" Then
ws.Cells.Replace What:=.Cells(idx, 1).Value, Replacement:=.Cells(idx, 2).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End If
Next idx
End With
Application.ScreenUpdating = True
End Sub

VBAやマクロに関する知識がほとんど無いので、何が原因なのか、どこに原因があるのかが分かりません。
どなたか詳しい方、お力を貸してください。
よろしくお願いします。

回答No.2

fujillin · ベストアンサー

"変換後"のシートの値を置き換えたいのですよね？
だとすると、こういうことでしょうか？
　・"変換表"のA列に変換前のコード、B列に変換後のコードがあると仮定
　・表に値がみつからない場合、背景色を赤（カラーコード3）にしています

Sub test()
Dim ws As Worksheet, rng As Range, fnd As Range
Dim idx As Long

Application.ScreenUpdating = False
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "変換後"
Set ws = ActiveSheet

'//　変換表の範囲を取得
With Sheets("変換表")
　Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

With ws
For idx = 1 To .Cells(65536, 1).End(xlUp).Row
　If .Cells(idx, 1) <> "" Then
　'//変換表と値を比較
　Set fnd = rng.Find(what:=.Cells(idx, 1).Value, LookAt:=xlWhole)　
　If fnd Is Nothing Then
　　.Cells(idx, 1).Interior.ColorIndex = 3　　'//値が無い場合背景を赤（=3)に
　Else
　　.Cells(idx, 1).Value = fnd.Offset(, 1).Value　'//値があれば置き換え
　End If
End If
Next idx
End With
'Application.ScreenUpdating = True

End Sub

お礼

ありがとうございます。

素晴らしいです。完璧に動作しました！
助かりました。ありがとうございます。

補足

すみません、さらに合わせて質問なのですが、
この上のコードにさらに追加して、

変換前　　　　　　　　 変換後
1000,1003,1010　　 →　1001,1002,1099
1000,,,1003,,1010　→　1001,,,1002,,1099

というように、一つのセルに「,」区切りの複数のIDが入っている場合でも
「,」はそのままで変換できるようにしたいのですが、可能でしょうか。

よろしくお願いします。

回答No.6

fujillin

No2です。補足で…

No.5で「あまりお勧めしない」と書きましたが、
　『比較表に無いものがあっても気にせず、あるものだけ変換できれていればよい』
という感じの機能であるなら、もともとのロジックのほうが簡単ですし、そのあたりは#1様が回答なさっていると思います。

ただし、変換前のコードと、変換後のコードがいろいろ重複しているようなので、ループの中で、一度コード変換したものが、つぎのループで再変換されることが無いように、表の方の順序を調整しておく必要があるような気がします。
もともとの、不具合はこのあたりに起因するものではないでしょうか？

補足

No.5、6と併せてご返答させていただきます。

本当に皆さんから色々なアドバイスをいただいているのに、自分がプログラムもVBA、マクロも全く分からないので、いただいたアドバイスを全く役に立てずにいる状態です。

ひとまずは時間が無かったので、変換しきれなかったIDに関しては、表にまとめて手動で変換することにしました。ツールとしては不完全なものになってしまいましたが、作業効率を上げる事は出来そうです。

＞『比較表に無いものがあっても気にせず、あるものだけ変換できれていればよい』

ツールの方向性としては、その方向で考えています。ツールを使って自動抽出したID番号を、機械的に変換するだけのツールなので、変換表以外のコードが出てくることはまずありません。
変換さえエラーが出なければ、機能的には問題はありません。

ツールの改良に関しては、皆さんのご意見を参考にして、仕事の合間を見て行おうと思います。少し自分でもVBAやマクロを勉強して、皆さんからのアドバイスを理解できるようにならないといけないですね。

ご回答いただいた皆様、どうもありがとうございました。

回答No.5

fujillin

No2です。

＞「,」はそのままで変換できるようにしたいのですが、可能でしょうか。
可能です。

いずれにしろ、それぞれのコードを分解しないと表と比較できないので
　dat = Split(.Cells(idx, 1).Value, ",")
みたいにして分解しておいて、
これまでと同様に、個別コードを表と比較して変換してから、
　Join(dat, ",")
などのように、再度、連結してから、その値を戻すことになります。
（当然ながら、","が無い場合でも処理できるようにしておく必要があります）

また、表中に該当するコードがなかった場合の処置をどうするのかなどを、きちんと決めておく必要もあります。
まぁ、複雑になりそうなので、あまりお勧めはしませんが…

回答No.4

turuzou

勘違いならご免なさい。
対象の数字の全角と半角の違いとか？
StrConv 関数等を使用してみるとか？
失礼しました。

補足

文字はすべて半角です。

StrConv 関数は、設定した文字列を形式変換するためのものなのですね。
今は、特に使用していません。

よろしくお願いします。

回答No.3

Wendy02

#1です。

>残念ながら、アドバイスをいただいたやり方では、うまくいきませんでした。
>何か、ほかのところに原因があるのでしょうか。

#2さんが、完璧だというなら、今さら、何か新たにアドバイスを書くつもりもありませんが、私が頭が悪いのでしょうか、どういう問題があるのか、今もさっぱり分かりません。

その元のコード自体に不足している部分があるはずだとしか思えませんね。

私は、わざわざ、別のコードを書き直すほどではないはずだと思っています。特別に、問題があるコードではありません。ただ、おそらく、質問者さんが書いていない情報に、大事な問題が残っているはずだとしかいえません。

元のコードは、文字列を変換するもので、質問中には、数字を変えるように書いていたのだから、xlWhleと書いたのだけれども、どういう状況で上手く行かないのか、書かれていないのでは、私の知識では、今の状況下に解決は見えません。

単なる置換のコードなんでしょうから、難しいものではないはずです。
Replace メソッド自体は間違いないはずです。もしかしたら、もともと、だいたいはできていて、#2の補足の問題ではなかったのではないでしょうか。そうしたら、分かるわけありません。

ただし、

>変換前　　　　　　　　 変換後
>1000,1003,1010　　 →　1001,1002,1099
>1000,,,1003,,1010　→　1001,,,1002,,1099

ワークシートに、「.(カンマ)」切り自体で置いておくこと自体、標準的なものではないようですね。

補足

申し訳ありません。私の質問の仕方、返答の仕方に問題があったようです。

変換したい数列なのですが、

"2006"
",,,1009,,,2005,2006,,,"

という感じで、「"」と「,」で区切られた数列です。これを変換表にしたがって、「"」と「,」は残したまま、数字だけを変換したいのです。

それで、一番最初の質問に記載したコードでは、とりあえず変換はできるのですが、一部の数字の変換がうまくいきません。数字は4桁から5桁で、変換できていない数字は、主に5桁の数字や、変換の過程で桁数が変わるものが多いように思います。
たとえば

変換前　 　変換表　 　変換後
　10001　　11003　　　10011
　11004　　9002　　　　9001
　6001　　　11002　　　9002
　1006　　　2002　　　　2001

という感じです。ただ、同じような数字が正常に変換されているので、これが原因ではないような気がしています。
また、変換表は「変換前のＩＤ」と「変換後のＩＤ」の2列しかない単純なものなのですが、これを変換前のＩＤを優先して並び替えたときと、変換後のＩＤを優先して並び替えたときで、変換結果が異なってくるのですが、その理由も良くわかりません。

表の書式なども関係があるのでしょうか。今現在は、変換するデータを貼り付けるセルを「文字列」に、変換表を「標準」に設定してあります。

VBA、マクロに関しては全く知識が無いので、説明もうまくいかずご迷惑をおかけしますが、どうかよろしくお願いいたします。

回答No.1

Wendy02

こんばんは。

質問の内容のコードは、文字列を対照にしたもののようです。
一部分があったら、その部分だけを替えるという主旨ですが、入れ替えという方式です。

一度、以下の部分を変えてみてください。
前：LookAt:=xlPart
　　↓
　　LookAt:=xlWhole

これで、どうなるか様子をみてください。

お礼

ありがとうございます。

残念ながら、アドバイスをいただいたやり方では、うまくいきませんでした。
何か、ほかのところに原因があるのでしょうか。