[[20220309051656]]　『置換でエラー発生』（悟renai）　ページの最後に飛ぶ

『置換でエラー発生』（悟renai）

A列内のカッコ付き文字列（含むカッコ）を削除する。

Macro1()は、マクロの記録でOK。
test()は、c = Replace(Columns.value, "(*)", "")で「メモリー不足しています」でNG。

どうすれば良いでしょうか？

Sub Macro1()
'
' Macro1 Macro
'

    Columns("A:A").Select
    Selection.Replace What:="(*)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

End Sub

Sub test()

    Dim c As Range

    For Each c In Columns("A:A")
        c = Replace(Columns.value, "(*)", "")
    Next
End Sub

＜使用 Excel：unknown、使用 OS：unknown ＞

 ＞どうすれば良いでしょうか？

 ちょっと質問の意図が分かりません。
 Macro1()でやればいいじゃないですか。

 何か手段に拘っての質問なら、何を生かしたいのですか？ それを説明してください。

 ※メモリー不足の原因はここだと思います
 　　　　　　　　　　　↓
 　　　　　　　　　　Columns.Value

 　シート全体のセルの値ですからねぇ・・。
 　更にその結果をA列に入れようとしている？　まったく理解できないコードです。

（半平太） 2022/03/09(水) 08:05

 仮に修正するなら、
 Dim c As Range
 For Each c In Columns("A:A").Cells
    c.Value = Replace(c.Value, "(*)", "")
 Next
 でしょうけど、
 Replace関数では、ワイルドカードが使えなかったと思います。

 希望の動作をさせるなら、以下のような感じでしょうか。
 Sub Macro2()
    Columns("A:A").Replace "(*)", "", xlPart
 End Sub
（三文） 2022/03/09(水) 08:13

回答感謝します。

＞Columns.Value
＞シート全体のセルの値ですからねぇ・・

範囲を限定する事でOKでした。
c = Replace(Columns("A:A").Value, "(*)", "")

適用範囲が*が数値の場合のみに限定したい場合はどうすれば良いでしょうか？

（悟renai） 2022/03/09(水) 08:34

 以下一案

 Sub Trial()
     Dim regEx As Object
     Dim c As Range

     Set regEx = CreateObject("VBScript.RegExp")

     regEx.Global = True
     regEx.Pattern = "\(\d+\)"

     For Each c In Columns("A").SpecialCells(xlCellTypeConstants, 2)
         c = regEx.Replace(c.Value, "")
     Next
 End Sub

（半平太） 2022/03/09(水) 10:09

半平太さん、コードをありがとうございます。

処理出来ました。

SpecialCells メソッドの使い方がわからなかったので以下を参考にしました。
https://excelwork.info/excel/specialcells/

SpecialCells(xlCellTypeConstants, 2)

Object.SpecialCells ( type [, value ] )

	type ： 定数が含まれているセル （xlCellTypeConstants  2  定数が含まれているセル）
	value： 文字 （xlTextValues  2  文字）

思うに「Columns("A").SpecialCells(xlCellTypeConstants, 2)」は、
処理は、A列の内文字が含まれているセルを対象にする事だと思いますが違っていますか？

近いコードとしては、

	 ln = Cells(Rows.Count, "A").End(xlUp).Row
	 For i = 1 To ln

正規表現を使ったReplaceでカッコ内が数値の場合だけ処理するのだと何となく判りました。
（.patternでカッコと数値を指定）

私がネットを参考にして作成したコードが以下ですが
正規表現を使うとすごく短いコードになるのに驚きました。

Sub test()

    Dim i As Long
    Dim ln As Long
    Dim s
    Dim r
    Dim p

    ln = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To ln
        Call FindInParenthesesRegExp(Cells(i, 1), r)
        For Each p In r
            Cells(i, 2) = p
        Next
        MsgBox p

        If IsNumeric(p) = True Then
            Cells(i, 3) = Replace(Cells(i, 1), "(" & Cells(i, 2) & ")", "")
        Else
        End If

    Next

End Sub

'// s：対象文字列 // resultArray：検索結果配列
Sub FindInParenthesesRegExp(s, resultArray)

    Dim reg As New RegExp       '// 正規表現クラスオブジェクト
    Dim r                           '// 検索結果文字列

    '// 検索条件＝括弧内以外を抽出
    reg.Pattern = "^(.*?)\(|\)(.*?)\(|\)(.*?).*$"
    '// 文字列の最後まで検索する
    reg.Global = True

    '// 検索一致文字をカンマに置き換える
    r = reg.Replace(s, ",")

    '// 先頭と最後のカンマ文字を除去する
    r = Mid(r, 2, Len(r) - 2)

    '// 括弧内の文字列を括弧の数だけ配列として取得
    resultArray = Split(r, ",")
End Sub
（悟renai） 2022/03/09(水) 11:20

 ＞思うに「Columns("A").SpecialCells(xlCellTypeConstants, 2)」は、
 ＞処理は、A列の内文字が含まれているセルを対象にする事だと思いますが違っていますか？

 その通りです。
 当初、A列全部のセルを処理するコードの様だったので、
 これは無駄すぎる、何とかしないとなぁ・・と思ったので。

 他に質問らしきところはなさそうなので、私はこれまでとします。

（半平太） 2022/03/09(水) 11:44

半平太さん、回答感謝します。

＞他に質問らしきところはなさそうなので、私はこれまでとします。

もう少し、続きが有るのですが。。。

変数が配列かどうかは、IsArrayで調べますが、

以下の変数p,ppは配列変数と思っていたのですが
IsArrayで調べると"P=Array No"となります。

調べ方が悪いのでしょうか？

コードの不備がありましたので少し変更しました。

Option Explicit

Sub test2()

    Dim i As Long, ln As Long
    Dim r
    Dim p
    Dim pp

    ln = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To ln
        Call FindInParenthesesRegExp(Cells(i, 1), r)
        For Each p In r
            If IsArray(p) = True Then
                MsgBox "p=Array Yes"
            Else
                MsgBox "P=Array No"
            End If

            If IsNumeric(p) Then
                pp = p
            Else
            End If
        Next

        Cells(i, 2) = Replace(Cells(i, 1), "(" & pp & ")", "")

    Next

End Sub
（悟renai） 2022/03/09(水) 12:49

r は配列ですが、pはその要素ですから、この場合文字列です。

（γ） 2022/03/09(水) 12:54

 2022/03/09(水) 11:20さんの　test()が自作部分ですか？
 余計なお世話ですが、気づいたところをいくつか。

 1.FindInParenthesesRegExpの中でseparatorとしてカンマを使っていますが、
  カッコの中にカンマがあると誤動作します。（例えば、(1,234.5)など普通にありえるのでは？）

 2．
     For Each p In r
         Cells(i, 2) = p
     Next
     だと結局、カッコ内文字列の最後の要素しか対象になりません。
 3.
     MsgBox p
     If IsNumeric(p) = True Then
         Cells(i, 3) = Replace(Cells(i, 1), "(" & Cells(i, 2) & ")", "")
     Else
     End If
     確認されているとおり pは Emptyになります（Nextで抜けた直後なので）。
     IsNumeric(p) = Trueは常に成立するので、
     結果として、最後の要素だけが処理されることになります。
 3.そもそも各要素ごとに置換処理がなされていません。

 4. Cells(i, 2)をバッファーに使っていますが、不適当です。
    1,234.5を書き込んだとき、1234.5という元の文字列とは別のものになるので、
    置換は行われません。

 ちなみに、
 | 範囲を限定する事でOKでした。
 | c = Replace(Columns("A:A").Value, "(*)", "")
 ビックリしました。きちんと動作確認することを推奨します。
 エラーになることは必然です。
 配列に対して文字列操作はできません。

（γ） 2022/03/09(水) 12:56

γさん、アドバイスありがとうございます。

＞r は配列ですが、pはその要素ですから、この場合文字列です。

文字列と聞いて
最初から Dim p as string,pp as string では、だめなのですね。
（Dim r のように as **** が無いのは気持ちが悪いのでなるべく指定するようにしています。
と言って何でも as variant でもなおさらです。）

＞1.FindInParenthesesRegExpの中でseparatorとしてカンマを使っていますが、
＞カッコの中にカンマがあると誤動作します。（例えば、(1,234.5)など普通にありえるのでは？）

誤動作の種があるのですね。
ネットから探してきたコードですが
今回の場合カッコの中にカンマがある事が無いのが幸いしています。

＞2．
＞ For Each p In r
＞ Cells(i, 2) = p
＞ Next
＞だと結局、カッコ内文字列の最後の要素しか対象になりません。

この件は、気がついたので新しく書いたコード(上記のtest2())で対応しました。

＞3.
＞４．

指摘されたようにpは Emptyになるので何か変だと思った結果test2()のマクロになりました。
「そもそも各要素ごとに置換処理がなされていません。」
test2()でも変な所がまだ残っていますか？
（悟renai） 2022/03/09(水) 13:28

＞r は配列ですが、pはその要素ですから、この場合文字列です。
というのは、
IsArray(p)つまり pは配列なのか、の結果について言及したものです。
つまり「pは配列ではないですよ」と言っただけですが。
通じていますか？

＞test2()でも変な所がまだ残っていますか？

カッコ内に数字があるものが複数あるケースで検証されたのですか？
他人に尋ねる前に、ご自分で確認するのは当然ですよね。

（γ） 2022/03/09(水) 13:51

＞通じていますか？

配列で無く、文字列と受け取ったので最初に文字列変数として宣言したのですが
残念ながら、言っている事が理解できません。

＞カッコ内に数字があるものが複数あるケースで検証されたのですか？

どんなパターンがあるか想定されない形式が有るかも判りませんが
セル内にカッコ内に数字だけのセルは複数ある事は今の所極少数です。
（本末転倒になりそうですがその箇所は抑えて後で手作業で処理する予定。）

（悟renai） 2022/03/09(水) 14:25

 >配列で無く、文字列と受け取ったので最初に文字列変数として宣言したのですが
http://officetanaka.net/excel/vba/error/compilation_error/error_18.htm

（通行人） 2022/03/09(水) 15:06

通行人さん、アドバイスに感謝します。

参考記事の肝は、
「For Eachステートメントで使用する制御変数には、
Inの後で指定するコレクションのメンバと同じオブジェクト型を指定しなければなりません。」

ｒが配列なのでPも変数形式は同じ配列と言う事ですよね。？

コードを又少し変更しました。

Option Explicit

Sub カッコ内が4桁の数字の場合削除()

    Dim i As Long, ln As Long
    Dim r
    Dim p
    Dim pp

    ln = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To ln
        Call FindInParenthesesRegExp(Cells(i, 1), r)
        For Each p In r
          If IsNumeric(p) And Len(p) = 4 Then  'カッコ内の文字は数値で桁数が４桁を削除対象に
               pp = p
          Else
           End If
        Next

        Cells(i, 2) = Replace(Cells(i, 1), "(" & pp & ")", "")

    Next

End Sub

Sub カッコ内が4桁の数字の場合削除2()

     Dim regEx As Object
     Dim c As Range

     Set regEx = CreateObject("VBScript.RegExp")

     regEx.Global = True
     regEx.Pattern = "\(\d{4}\)"

     For Each c In Columns("A").SpecialCells(xlCellTypeConstants, 2)
         c.Offset(0, 1) = regEx.Replace(c.Value, "")
     Next
 End Sub
（悟renai） 2022/03/09(水) 15:48

 ＞ｒが配列なのでPも変数形式は同じ配列と言う事ですよね。？

 MSでは、
 「For Each...Next ステートメントは、コレクション内の オブジェクトごと、
 または 配列内の要素ごとに、ステートメントのブロックを繰り返します。」
 となっています。

 配列の中身が値の場合、Variant型になります。

 Dim arr() As String
 arr = Split("1,2,3,4,5",",")
 Dim var As Variant
 For Each var In arr
 Next

 オブジェクトコレクションであれば、そのオブジェクトの型となります。

 Dim ws As Worksheet
 For Each var In Worksheets
 Next

 オブジェクトが配列なら、そのオブジェクトの型となります。

（tkit） 2022/03/09(水) 16:10

tkitの記載された内容を整理すると

ｒは配列で中身は値なのでInの後のPも変数形式はVriantと言う事ですよね。？
（今回は違いますが、
もしｒがオブジェクトコレクションであれば、Inの後のPもｒと同じオブジェクトの型式となる）

であればコードは以下のように記載すると言う理解で良いですか？

Sub カッコ内が4桁の数字の場合削除()

    Dim i As Long, ln As Long
    Dim r As Variant、p as variant,pp as variant)

又は
Sub カッコ内が4桁の数字の場合削除()

    Dim i As Long, ln As Long
    Dim r
    Dim p
    Dim pp

（悟renai） 2022/03/09(水) 16:38

 >ｒは配列で中身は値
 微妙に誤解しているようですが、

 コレクションはオブジェクトの集まり
 例) ShapesコレクションのメンバはShapeです。
 For Each は、コレクションのメンバ（オブジェクト）を1つづつ取り出して処理する
 For Each の制御変数は、オブジェクト型かVariant型

 配列変数は変数の集まり、
 例) 文字列型の配列の要素は、文字列型です。
 For Each は、配列変数の要素（変数）を1つづつ取り出して処理する
 ただし、For Each の制御変数はVaiantでないといけない <= For Each ステートメントの方のしばり

 このようなコードのとき、
  Dim StrAry(1 To 3) As String
  Dim v As Variant
  StrAry(1) = "A"
  StrAry(2) = "B"
  StrAry(3) = "C"
  For Each v In StrAry ' これはOK
      Debug.Print v                  ' <= vの型は、[Variant/String] <= Variant型変数にString型変数が納められている
  Next

 vをString型にすると、For Each ステートメントの制限でエラーになる

 この話は、配列の要素に対して、IsArrayがFalseなるというところから始まってると思いますが、
 StrAryは配列だけど、
 StrAry(1)は配列ではなくString 
 For Each の制御変数 vも For Each 中では、配列ではなく、Vairnat/String 
（´・ω・`） 2022/03/09(水) 17:09

´・ω・`さんの回答、感謝します。

VBA初心者は、知識不足で解説されても中々理解が追いつかないのが残念です。

今、私の中で分かっているのは
For Each の次に来る変数(p)は,オブゼクト以外はVariantで無くては行けないと言うことです。

なので「Sub カッコ内が4桁の数字の場合削除()」のｐはVariantでDIM宣言をしなくていけない。

配列が頭の中に合って融通がききませんが

以下の説明で少し見えてきました。

	この話は、配列の要素に対して、IsArrayがFalseなるというところから始まってると思いますが、
	 StrAryは配列だけど、
	 StrAry(1)は配列ではなくString 
	 For Each の制御変数 vも For Each 中では、配列ではなく、Vairnat/String

マクロ初心者は、何回も具体例を上げて説明を受けていますがガッテンするまでは至っていません。

「Sub カッコ内が4桁の数字の場合削除()」のコードに置き換えて考えていますが
結局、オールマイティ（逃げに成ってしまいますが）で避けて通れないDIMの宣言は、

	Sub カッコ内が4桁の数字の場合削除()
	    Dim i As Long, ln As Long
	    Dim r As Variant、p as variant,pp as variant)
	又は
	Sub カッコ内が4桁の数字の場合削除()
	    Dim i As Long, ln As Long
	    Dim r
	    Dim p
	    Dim pp

に至ります。

（悟renai） 2022/03/09(水) 18:47

敬称無しに思うところはありますが。

変数宣言は必要な型通り宣言するだけです。
いちいち確認が必要とは思いません。

配列の理解がいまいちのようなので、その辺を。

多くの変数が必要な場合、
Dim n0 As Long
Dim n1 As Long
Dim n2 As Long
Dim n3 As Long
Dim n4 As Long ...
でもいいですが、宣言が多くなること、変数名が多くなることで
面倒なことがいろいろあるので、
Dim n(4) As Long
こんな風に出来ますよってことです。
単に変数の集まりです。

（´・ω・`）さん 2022/03/09(水) 17:09の回答が
配列、For Eachを網羅したベストな回答だと思いますが。

配列については、様々なサイトで親切丁寧に解説されているところが、
多々ありますので、調べてみては。
（tkit） 2022/03/10(木) 10:23

コメント返信：

[ 一覧(最新更新順) ]

[[20220309051656]] 『置換でエラー発生』（悟renai） ページの最後に飛ぶ

[[20220309051656]]　『置換でエラー発生』（悟renai）　ページの最後に飛ぶ