none
VBAで一つのサブジェクトの中に複数の置き換えが出来るものか。 RRS feed

  • 質問

  • こんばんわ。

    先日は素早い回答を誠にありがとうございました。

    あの後早速データを組んでみましたところ、おかげさまで作業の効率が飛躍的に上がり、とても助かりました!

    早速なのですが、また質問させてください。

    日々の自分の作業の中で、EXCELを利用して置き換えをすることが多く、

    データ量が膨大な為出来る限りプログラムで予め処理を出来るようにしたいと思っております。

    作業の記録を使用して、置き換えに必要なコードは推測することが出来たのですが、

    例えば最初はC列の”○○様”という表示の”様”を置換したあとに、

    K列の”◆◆データ”の”データ”を同じマクロで消すことは出来るのでしょうか?

    sub text()

    range("C2:C28").select

             Selection.Replace what:="様”、replacement:="", lookat:=xlPart, _

       searchorder:= xlByRows, MatchCase :=False, searchformat:=False, _

       ReplaceFormat:=False

    end sub

    でC列2~28のセル内に”様”が入力されている場合、上記のコードで空白に置き換えることは出来ましたので、

    C列内に違う置換のコードを入力してみても問題なく置換できたのを確認が取れたので、

    同じ要領で

    sub text()

    range("C2:C28").select

             Selection.Replace what:="様”、replacement:="", lookat:=xlPart, _

       searchorder:= xlByRows, MatchCase :=False, searchformat:=False, _

       ReplaceFormat:=False

    range("K2:K28").select

             Selection.Replace what:="データ”、replacement:="", lookat:=xlPart, _

       searchorder:= xlByRows, MatchCase :=False, searchformat:=False, _

       ReplaceFormat:=False

    end sub

    と入力したところ、”400エラー”が出てしまい、対処法が分からず困っています・・・・・・。

    きっと、すでに分かっている方にはとてもつたないコードに見えるかと思いますので、

    ≪ここを省略しても、もっと簡単に出来るよ!≫という返信も、後学の為、ぜひお聞かせ頂ければ嬉しいです。

    毎度お手間をおかけしますが、どうぞよろしくお願いいたします!

    2019年5月10日 10:48

回答

  • (各箇所)what:="様"とreplacementの間が 、になっていますが ,  が正しいです。タイプミスでしょうか?
    Range.Replace メソッド (Excel)については下記を参考にされるのが良いと思います。
    https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.replace 
    ちなみに、開いているシート(アクティブになっているシート)に対して、単に指定文字を削除するのなら、
    sub text_Rep()
        Range("C2:C28").Replace "様", ""
        Range("K2:K28").Replace "データ", ""
    end sub
    作業の記録を使用で問題が起こる可能性は、コードが長くなるというより、必要な情報が書き込まれない場合にあります。
    上記コードには、BookやSheetの情報がなく、対象のシート以外がアクティブになっているときに実行されれば、
    思わぬ結果が起こり、場合によっては、取り返しが出来ません。そのような事を防ぐ為に
    作業の記録を使用する時は、作業をしない他のシートから記録を開始して、作業するシートタブを選択して行うようにした方が良いと思います。そうすることで、シートの情報が記録され、他のシートで実行される事を防げます。
    特徴として、作業の記録を使用の場合は、.selectやSelection.が多くなり、VBAを覚えるにつれ、それが、少なくなるようです。ちなみに、このコードでのsheetの指定方法は、
    With sheets("シートの名前")
        .Range("C2:C28").Replace "様", ""
        .Range("K2:K28").Replace "データ", ""
    End with


    • 編集済み Takumi_Q 2019年5月10日 20:00
    • 回答としてマーク Tearwell 2019年5月19日 6:25
    2019年5月10日 19:20

すべての返信

  • (各箇所)what:="様"とreplacementの間が 、になっていますが ,  が正しいです。タイプミスでしょうか?
    Range.Replace メソッド (Excel)については下記を参考にされるのが良いと思います。
    https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.replace 
    ちなみに、開いているシート(アクティブになっているシート)に対して、単に指定文字を削除するのなら、
    sub text_Rep()
        Range("C2:C28").Replace "様", ""
        Range("K2:K28").Replace "データ", ""
    end sub
    作業の記録を使用で問題が起こる可能性は、コードが長くなるというより、必要な情報が書き込まれない場合にあります。
    上記コードには、BookやSheetの情報がなく、対象のシート以外がアクティブになっているときに実行されれば、
    思わぬ結果が起こり、場合によっては、取り返しが出来ません。そのような事を防ぐ為に
    作業の記録を使用する時は、作業をしない他のシートから記録を開始して、作業するシートタブを選択して行うようにした方が良いと思います。そうすることで、シートの情報が記録され、他のシートで実行される事を防げます。
    特徴として、作業の記録を使用の場合は、.selectやSelection.が多くなり、VBAを覚えるにつれ、それが、少なくなるようです。ちなみに、このコードでのsheetの指定方法は、
    With sheets("シートの名前")
        .Range("C2:C28").Replace "様", ""
        .Range("K2:K28").Replace "データ", ""
    End with


    • 編集済み Takumi_Q 2019年5月10日 20:00
    • 回答としてマーク Tearwell 2019年5月19日 6:25
    2019年5月10日 19:20
  • ちゃんと内容を読んでないので勘違いしている可能性がありますが、そもそもから
    Range.Replace メソッドは指定の文字がどこの位置にあっても置換してしまうので
    例えば対象セル範囲内に 子 などの敬称以外の「様」が入っていた場合も消えてしまいます。

    少し前に『王子様』という名前をつけられた人が改名を認められたというニュースがありましたが
    そういう特殊な例でなくても氏名等に「様」の字が使われる可能性は恐らくあるでしょう。

    データというのもデータという単語ではなく、何らかの語句の一部だったりしたときも置換の
    対象になってしまいます。

    それらの可能性が一切ないというのでもなければ別の方法を検討した方がいいかもしれないです。

    2019年5月11日 7:59
  • こんばんわ。

    早速のご返答頂いたにも関わらず、なかなか返信できず大変申し訳ありませんでした。

    また、短い文の中に参考記事のURLまで、本当にうれしく感じました。

    お恥ずかしいお話ですが、

    作業の記録についてもとても単純に考えてしまっており、

    取り返しのきかない事態になることを想定してなかったもので、

    ご回答を頂いてからはじめてハッとしました・・・・・・・。

    それに、こんなにコードがシンプルになるものなのですね。

    分からないなりにですが、ただ記録をコピー&ペーストしたよりも

    はるかに端的でわかりやすくなっているように感じましたし、

    いつかそんな風にコードを記入出来たらいいなと心から思いました。

    この短期間のうちに、過分なお気遣い、本当にありがとうございました!

    2019年5月12日 14:28
  • こんばんわ。

    今のところ、実際に使う単語がご指摘のトラブルに至る可能性が低いものですので、

    ひとまずはreplace関数を利用したコードを使用しようかと考えています

    ただ、重複しやすい単語を利用する場合になってしまうと、本当に懸念されている通りで、

    かといってこちらでは妙案も思い浮かばず・・・という、なんとも痛し痒しな状態になってしまい。

    もう少し、そんな場合でも凡庸性があるようなコードを自然と書けるよう、

    少しづつそんな場合の対処も自然に思い浮かべられるよう、頑張ろうと思います。

    ご心配とご指摘、本当にありがとうございました!

    2019年5月12日 14:44
  • セルの末尾にある”様”だけを削除するなら、For~Nextで次のようなのはどうでしょうか。

    With sheets("シートの名前")

      For i=2 to 28

          Replace(Right(.cells(i,3).value,1),"様", "")
          Replace(.cells(i,11).value, "データ", "")

      next i

    End With

    2019年5月12日 15:12
  • セルの末尾にある”様”だけを削除するなら、For~Nextで次のようなのはどうでしょうか。

    With sheets("シートの名前")

      For i=2 to 28

          Replace(Right(.cells(i,3).value,1),"様", "")
          Replace(.cells(i,11).value, "データ", "")

      next i

    End With

    このコードは正常に動かないと思います。
    また、Range オブジェクトをループ処理する場合、可能なら極力 For ループではなく
    For Each ループを使ったほうがいいです。

    単に様かどうかにかかわらず C2:C28 範囲内の末尾の文字を削除し、K2:K28 範囲内に
    含まれているすべての データ という文字を削除 (各文字列の位置にかかわらない & ひとつの
    セルに複数の データという字が含まれていた場合も対象) するのなら以下でできると思います。
    (なお、範囲内に空白セルが含まれていることを想定していないのでもし、あった場合はエラーに
    なると思います)

    Sub test()
        Dim rng As Range
    
        With Sheets("シートの名前")
            For Each rng In .Range("c2:c28")
                rng.Value = Left$(rng.Value, Len(rng.Value) - 1)
                rng.Range("i1").Value = Replace$(rng.Range("i1").Value, "データ", "")
            Next
        End With
    End Sub
    

    2019年5月13日 2:21
  • もし、データそのものに直接 様 が入っていないといけない状態でなおかつそのデータを扱う際は
    様 があってはいけない状態ではなく、表示上あればいいだけであるならば、セルの書式設定で
    表示形式を [ユーザー定義]、種類を 「@"様"」とすれば表示上は末尾に "様" が付き、データ
    そのものには "様" を含まないというやり方もあります。

    2019年5月13日 2:50
  • こんばんわ。

    結論から申し上げますと、Takumi-Qさんの書かれていた

    シンプルなコードで、私の当座の目標は達成出来ました。

    そして、本来の”様”と”データ”に当たる部分がぼやけた表現になっており、

    それがご回答者さまを振り回してしまった要因になっている事、

    大変申し訳なく思っております。すみません。

    ”様”の部分に関しましてですが、本来は別の単語が入力されております。

    本当ははっきりとした単語を記入して教えを乞うのが一番良いのでしょうが、

    大切な情報である為、明確に書くのは控えさせて頂きたいところがありました。

    データとしては

    ・猫様

    ・猫様(ぶち猫)

    ・(空欄が入っています)

    ・ぶち猫様

    ・猫

    ・(空白が入ってます)

    のように、列に空白が入ることもあり、

    上の例ですと”様”を確実に消したいというようにしかお伝えが出来ず、

    しかもその時点でも、infadeさんの書かれていた

    ≪空白があった場合にはエラーになると思います≫

    に当たってしまい、折角記入して頂いた手間を考えると、率直にお伝えするのがかなり心苦しく・・・。

    もっといろんな方に伝わるように書けたらいいのになと、自分の記入の仕方が歯がゆかったです。

    (※そして、ここより先はKokemomoさんの発言を検証の材料として扱ってしまっておりますが

    もしご不快でしたら削除いたしますので、遠慮なく教えて下さいませ。)

    私はinfadeさんのコメントを辿っていて下記の点がよく分からなかったのですが

    >Range オブジェクトをループ処理する場合、可能なら極力 For ループではなく
    >For Each ループを使ったほうがいいです。

    とおっしゃっていたのはどうしてなのでしょうか?

    この状況で質問を重ねるのは大変心苦しいのですが、

    知りたい!と思ったので、聞かせて頂けたらとてもとても嬉しいです。

    あと、表示形式をそんな風に動かしていいこと、本当に目から鱗でした。

    お話聞かせて頂いて、使いどころを考え思わずワクワクしてしまいました!

    何かと心配りが後手後手になっておりますが、

    皆さま、どうぞ最後までお付き合いいただければありがたく、嬉しいです。

    よろしくお願いいたします!

    2019年5月13日 15:01
  • 私はinfadeさんのコメントを辿っていて下記の点がよく分からなかったのですが

    >Range オブジェクトをループ処理する場合、可能なら極力 For ループではなく
    >For Each ループを使ったほうがいいです。

    とおっしゃっていたのはどうしてなのでしょうか?

    この状況で質問を重ねるのは大変心苦しいのですが、

    知りたい!と思ったので、聞かせて頂けたらとてもとても嬉しいです。

    あと、表示形式をそんな風に動かしていいこと、本当に目から鱗でした。

    自分もそこまで詳しいわけではないのですが、For ループよりも For Each ループの
    ほうが早くオブジェクトに対するループとしてはより効率がいいといわれているようです。

    Microsoft 社もオブジェクトなどをループするときは For Each ループのほうを
    推奨している...らしいです。
    ほかにカウンタ変数を使う必要がなく、オブジェクトのコレクションなどを
    ループ処理する場合は For Each のほうが見た目的にはわかりやすいと思います。
    ただ、近年ではそれもかなりの僅差であとは好みの問題だったりもするようですけどね。

    (上記にも関連する情報はあると思うけど)


    2019年5月14日 2:06
  • infadeさま、ご指摘ありがとうございます。また、Teawellさま、間違い含みの返事を投稿して申し訳ありませんでした。左辺を忘れておりました。

          .Cells(i, 3).Value = Replace(Right(.Cells(i, 3).Value, 1), "様", "")
          .Cells(i, 11).Value = Replace(.Cells(i, 11).Value, "データ", "")

    2019年5月15日 13:28
  • >infadeさまへ

    こんばんわ。

    コードについて検索をかけると、膨大な記事がでてきてしまうので、

    各種コードについて初見に近い自分には正解を見つけるのがとても難しいです・・・。

    それだけに、参考文献の引用まで、本当に助かります。

    ”For Each”についても、そんないわれがあったのですね。

    ゆくゆくは、VBAを使って、Outlookのメールデータを

    EXCELでデータ化したいという野望を持っているので、

    そういった情報のとっかかりを頂けると、次のステップに向けてのやる気に繋がるので嬉しいです。

    この短期間の間に、端的でわかりやすい回答を何度も打って頂き、ありがとうございました!

    2019年5月15日 14:59
  • >KokemomoYamamomoさまへ

    こんばんわ。

    右も左もわからない状態で、緊張と不安の中このフォーラムで質問しておりました。

    きっと、わかる方には簡単すぎるような自分の疑問に対して、返事を頂けるのかとても不安でした。

    それだけに、Kokemomoさんが答えようとして下さった気持ち、とてもありがたかったです。

    どうか、お気になさらないでくださいね。

    わざわざ打ち直して頂いたものも、何か次の機会があれば使わせて頂きます。

    お時間いただいてご回答下さり、ありがとうございました!

    2019年5月15日 15:07
  • スレッドに関わって下さった皆様へ。

    こんにちわ。

    そろそろ頃合い化と思いますので、質問を閉めさせて頂きます。

    今回は、Takumi-Qさんにベストアンサー(?)をお渡しさせていただきます。

    しかし、先日の質問から丁寧に回答を頂ける、

    infadeさんや、

    KokemomoYamamomoさん、

    お二人にも、

    言葉だけになってしまいますが、

    功労賞を個人的に差し上げさせてください。

    皆さま、本当にありがとうございました!

    2019年5月19日 6:25