エクセルでセル内の特定数値を他セルの数値で置換

Question

エクセルで下記のようなことを行いたいです。アドバイスお願い致します。

●処理前
Ａ１⇒　2151　　Ｂ１⇒　RSS1234　　Ｃ１⇒　RSS1234+RSX1234
Ａ２⇒　3412　　Ｂ２⇒　RSS5678　　Ｃ２⇒　RSS5678+RSX5678
Ａ３⇒　5486　　Ｂ３⇒　RSS3456　　Ｃ３⇒　RSS3456+RSX3456
・
・
・

●処理後要望
Ａ１⇒　2151　　Ｂ１⇒　RSS2151　　Ｃ１⇒　RSS2151+RSX2151
Ａ２⇒　3412　　Ｂ２⇒　RSS3412　　Ｃ２⇒　RSS3412+RSX3412
Ａ３⇒　5486　　Ｂ３⇒　RSS5486　　Ｃ３⇒　RSS5486+RSX5486
・
・
・
Ａ列が空白になるまで上記置換が行われる。

となるようなVBAを作りたいのです。
アドバイスお願い致します。

kagakusuki · Accepted Answer

置換をしたい範囲を選択しているシート上にあるC列～Ｄ列としますと、次の様なVBAマクロは如何でしょうか？

Sub Macro()

Dim SN As String
    Dim LR As Long
    
    SN = ActiveSheet.Name
    ActiveSheet.Select
    If Application.WorksheetFunction.Count(Columns("A:A")) = 0 Then Exit Sub
    LR = Application.WorksheetFunction.Match(9E+307, Columns("A:A"))
    Range("B1:D" & LR).Replace What:="=", Replacement:=" ="
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(SN).Select
    Sheets(Sheets.Count).Range("C1:D" & LR).FormulaR1C1 = _
        "=SUBSTITUTE('" & SN & "'!RC,MID('" & SN & "'!RC2,MIN(" & _
        "IF(ISNUMBER(FIND(0,'" & SN & "'!RC2)),FIND(0,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
        "IF(ISNUMBER(FIND(1,'" & SN & "'!RC2)),FIND(1,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
        "IF(ISNUMBER(FIND(2,'" & SN & "'!RC2)),FIND(2,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
        "IF(ISNUMBER(FIND(3,'" & SN & "'!RC2)),FIND(3,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
        "IF(ISNUMBER(FIND(4,'" & SN & "'!RC2)),FIND(4,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
        "IF(ISNUMBER(FIND(5,'" & SN & "'!RC2)),FIND(5,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
        "IF(ISNUMBER(FIND(6,'" & SN & "'!RC2)),FIND(6,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
        "IF(ISNUMBER(FIND(7,'" & SN & "'!RC2)),FIND(7,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
        "IF(ISNUMBER(FIND(8,'" & SN & "'!RC2)),FIND(8,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)," & _
        "IF(ISNUMBER(FIND(9,'" & SN & "'!RC2)),FIND(9,'" & SN & "'!RC2),LEN('" & SN & "'!RC2)+1)" & _
        "),4),'" & SN & "'!RC1)"
    Sheets(SN).Range("C1:D" & LR).Value = Sheets(Sheets.Count).Range("C1:D" & LR).Value
    Application.DisplayAlerts = False
    Sheets(Sheets.Count).Delete
    Application.DisplayAlerts = True
    Range("B1:D" & LR).Replace What:=" =", Replacement:="="

End Sub

尚、上記のマクロは、あくまで

>⇒Ｂ列には、必ず数値が含まれております。
>置換をしたい範囲のセル（Ｄ列までとすれば、Ｂ～Ｄ列）には、ＶＢが走る前の初期状態ではいずれも必ず同じ４桁の数値が含まれている状態に致します。

という条件が満たされている場合においてのみ有効なマクロです。
　もしB列に入力されている文字列が、例えば

R2S1234

等の様に4桁ではない数字（この場合は「2」）が、4桁の数字よりも前の部分に含まれているものであった場合には、正しい結果を得られませんので注意して下さい。

keithin · Answer

＞Ｂ列～〇列のセル内に含まれる特定の4桁の数値を

最初のご相談の情報提供と違う事が書かれていますが、今度は「特定の」例えば9999がＢ列以右の「全ての行の全てのセルに共通で」記載があるということで、今度こそ間違いないんですね。
いずれにしても回答済みのマクロからほとんど何も変える必要がありません。


sub macro1()
　dim c as string, cx as string
　dim i as integer
　dim r as long

　for i = 1 to len(range("B1"))
　　if mid(range("B1"), i, 1) like "[0-9]" then
　　　cx = cx & mid(range("B1"), i, 1)
　　end if
　next i

　for r = 1 to range("A65536").end(xlup).row
　　cells(r, "B").resize(1, 255).replace _
　　　what:=cx, replacement:=cells(r, "A"), lookat:=xlpart
　next r
end sub



＞どのような条件を明確にすれば良いかさえ分からない

「あなたのエクセルの、いま目に見えてる事実」と「それがどうなって欲しいのかの姿」を目に見えるままありのまま正しく丁寧に情報提供すればいいんです。

MarcoRossiItaly · Answer

＞説明不足大変失礼致しました。逆に分かりにくくなるのではと思っておりましたが、お言葉に甘えて……

補足を読んでもまだ、条件が足りていません。したがって誰かが勝手に仮定なりをしなければ、答えを出すことはムリです。「不可能」なのですから、分かりやすさ以前の問題です。


＞……人間が一行ずつやるなら（Ｄ列までとするなら）Ｂnセル～Ｄnセルを範囲選択して、特定数値である9999を……

まさにそういう、人間が手作業で行うなら、どういうルールでの処理になるのか？ということです。基本原則が不明だということです。

●補足のデータ例では、B 列～ D 列の 9 セルに全て「9999」と書いてありますね。ところが質問文における同じ範囲には、「1234」、「5678」、「3456」と 3 種類も出てきますよ。
●補足を見る限り、各文字列中の何文字目に置換対象の文字列が出てくるのかも一定していないようです。


人間は、「何となく」といった感覚で、本当は複雑な条件が組み合わさった作業でも、簡単にこなしてしまったりします。しかし、同じことを機械に理解させるには、数学の問題を解くのと同じで、具体的かつ過不足のない条件を与える必要があります。問題が複雑になれば、それだけ数学力なり論理力が求められるようになります。

例えば「回文を作成せよ」という問題があれば、(1)文末の句点がある場合のみそれを除去、(2)文字列中の全ての文字の位置を昇順で付番、(3)番号の降順で文字を並べ替え、(4)(1)で句点を除去していた場合のみそれを付加、といったふうに、「紛れ」のないルールを明示します。そうでなければ、永遠に答えには到達できません。

条件さえはっきりして、その条件の数がそれほど多くはないとすれば、置換するコードを書くこと自体は簡単です。

kagakusuki · Answer

どの様な処理を行えば良いのかという条件に、不明な点がありますので、追加情報を御教え願います。

●A列に自然数以外の値（文字列、負の数、小数点以下の桁を含んでいる数値）が入力されていた場合には、どの様にすれば良いのでしょうか？

●B列のセルに数値が含まれていない場合には、どの様にすれば良いのでしょうか？

●B列のセルに

=A20+ABC1234+DEF5678+GHI987654

等の様に、複数種類の数値が入力されていた場合には、どの数値を置換対象の数値とすれば良いのでしょうか？

以上の点に関して御教え願います。

keithin · Answer

＞エクセルでセル内の特定数値を他セルの数値で置換

というご相談ですが、現実にやりたいのは「Ｂ列やＣ列にある不特定の数字」をＡ列の特定の数字に置き換えたい、となっています。
たとえばＢ１を見て「１２３４」という数字がここでは対象になると「特定して」、Ｂ１，Ｃ１の１２３４を２１５１に置き換えたいとか。そういった「あなたが何をしたいのか（ルール）の説明」が足りてません、という事です。

で。
説明を待っていても始まらないので、勝手にそーいうルールだとします。

作成例：
sub macro1()
　dim r as long
　for r = 1 to range("A65536").end(xlup).row
　　cells(r, "B").resize(1, 2).replace _
　　　what:=right(cells(r, "B"), len(cells(r, "A"))), _
　　　replacement:=cells(r, "A"), _
　　　lookat:=xlpart
　next r
end sub

＃参考
ご質問に書かれてる（「説明のための（事実と違う？）」）サンプルからでは、実はこーでもいいと読み取れます。

sub macro2()
　dim r as long
　for r = 1 to range("A65536").end(xlup).row
　　cells(r, "B") = "RSS" & cells(r, "A")
　　cells(r, "C") = cells(r, "B") & "+RSX" & cells(r, "A")
　next r
end sub

実際にヤリタイ事に応じて、適切に応用してください。

エクセルでセル内の特定数値を他セルの数値で置換

置換をしたい範囲を選択しているシート上にあるC列～Ｄ列としますと、次の様なVBAマクロは如何でしょうか？

＞Ｂ列～〇列のセル内に含まれる特定の4桁の数値を

＞説明不足大変失礼致しました。

この回答への補足

どの様な処理を行えば良いのかという条件に、不明な点がありますので、追加情報を御教え願います。

この回答への補足