Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Microsoft 365)
このVBAでエラーが発生する理由と解決策を教えてください
投稿日時: 22/06/22 18:11:13
投稿者: のらねこ

「貼付」シートの選択範囲に「置換リスト」シートのA列の値が含まれる場合はB列の値に置換するマクロを作りました。
概ね動くのですが、稀に実行時エラー13で止まってしまいます。
原因と解決策についてアドバイスをいただけましたら幸いです。
なお、エラーが起こるケースは選択セルが多く、かつセル内の文字数が多い場合なような印象を受けています。
デバックでコードの状態を見ると赤字の箇所でエラーになっています。
ーーーー
sub 置換マクロ()
 Dim wsIn As Worksheet: Set wsIn = ThisWorkbook.Worksheets("貼付")
 Dim wsList As Worksheet: Set wsIn = ThisWorkbook.Worksheets("置換リスト")
 Dim rng As Range
 Dim i As Long
 Dim j As Long
 Dim i2 As Long
 Dim myAry()
 
 Set rng = Selection
 i2 = 2 '「置換リスト」A列開始位置
 
'選択セルが1つの場合、そのまま置換
 If rng.Count = 1 Then
  Do
   rng = Replace(rng, wsList.Cells(i2, 1).Value,wsList.Cells(i2, 2))
   i2 = i2 +1
  Loop Until wsList.Cells(i2, 1) = ""
 Else
 
'選択セルが複数の場合は、配列myAryに格納してから置換・セル書き換え(高速化対応)
  myAry() = rng
  Do
   For i = LBound(myAry, 1) To UBound(myAry, 1)
    for j = LBound(myAry, 2) To UBound(myAry, 2)
     myAry(i, j) = Replace(myAry(i, j), wsList.Cells(i2, 1).Value,wsList.Cells(i2, 2))
    Next
   Next
   i2 = i2 +1
  Loop Until wsList.Cells(i2, 1) = ""
  rng = myAry()
 End If
End Sub

回答
投稿日時: 22/06/22 18:47:29
投稿者: simple

wsListに何も割り当てられていないので。
たぶんこれは投稿時のミスとして。
 
よくあるのは、Selectionの値がエラー値のときに、型不一致エラー13になるケースです。
いずれにしても、エラーになったときに、該当のセルは何かをよく観察することです。

回答
投稿日時: 22/06/22 19:00:03
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

 Dim wsIn As Worksheet: Set wsIn = ThisWorkbook.Worksheets("貼付")
 Dim wsList As Worksheet: Set wsIn = ThisWorkbook.Worksheets("置換リスト")

↑の2行目の
>Set wsIn = ThisWorkbook.Worksheets("置換リスト")
赤字の部分は、正しいですか?

回答
投稿日時: 22/06/22 21:07:07
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
'選択セルが複数の場合は、配列myAryに格納してから置換・セル書き換え(高速化対応)
  myAry() = rng

↑の命令、実際に動いていますか?
動いているとしたら、
掲示板への投稿ミスかな?

投稿日時: 22/06/23 12:59:14
投稿者: のらねこ

いつもありがとうございアmす。
誤記失礼いたしました。
 
Dim wsList As Worksheet: Set wsList = ThisWorkbook.Worksheets("置換リスト")
 
が正しいコードです。
 
>simpleさん
エラーを含むかどうか、考えにありませんでした!
午前中はデータを確認できなかったので、午後確認してみます。
 
>WinArrowさん
引用:
'選択セルが複数の場合は、配列myAryに格納してから置換・セル書き換え(高速化対応)
  myAry() = rng
 
↑の命令、実際に動いていますか?
動いているとしたら、
掲示板への投稿ミスかな?
 
実行時13エラーが出る特定のデータ以外では想定通り動いています。
(これで回答になっているでしょうか・・・?)

回答
投稿日時: 22/06/23 15:55:46
投稿者: WinArrow
投稿者のウェブサイトに移動

実行時エラー13は、データ型が整合していないというエラーです。
 
掲示板にhコードを手入力しないでください。
回答者は、掲示板のコード以外、見ることができないので、
エラーがでているコードの説明されても、
本当なお?
と疑問を抱いてしまいます。
キャッチボールが増えて、解決までに時間が掛かってしまいます。
コードはコードペインから、コピペしてください。
 
エラーが出た場所で、「デブッグ」に切替えて、変数の値を調べることで、対処できると思います。
 
なお、コードの記述について、
Valueプロパティを付けたり、付けなかったり、統一したほうがよいです。
セルの場合は、可読性の観点から、Valueプロパテイを省略しない方がよいです。

回答
投稿日時: 22/06/23 16:28:19
投稿者: WinArrow
投稿者のウェブサイトに移動

ヒント
 
二階建てループを1階建てループに変更して、すっきりさせましょう。
 
参考コードを書いておきます。

Option Explicit

Sub 参考コード()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Rx1 As Long

    Set Ws1 = Sheets(1)
    Set Ws2 = Sheets(2)

    For Rx1 = 1 To Ws2.Range("A" & Ws2.Rows.Count).End(xlUp).Row
        With Ws1
            .Range("B2:C5").Replace what:=Ws2.Cells(Rx1, "A").Value, replacement:=Ws2.Cells(Rx1, "B").Value
        End With
    Next
    

End Sub


投稿日時: 22/06/23 18:58:57
投稿者: のらねこ

>WinArrowさん
コード手入力の件失礼いたしました。次回から気をつけます。
またコードをすっきりさせる案のアドバイスもありがとうございます。
ヒントを元に改良案を考えてみます。
 
>simpleさん、WinArrowさん
simpleさんのアドバイスの通り、選択範囲内にエラー値が含まれていたために実行時エラーが発生していました。
 
そこで置換前にIsError関数で選択範囲にエラーがあるか判断してエラーを含む場合はメッセージボックスで表示の上マクロを中断する仕組みを入れようと思ったのですが、どうやら計算結果としてのエラーだけではなく「#NAME?」などの文字列を含む場合でもデータ型違いになってしまうようです。
IsError関数以外の検索方法というと「#」を含むか検索する方法か、Excelのエラー表示を全て一つ一つ検索するコードにするしか対応イメージが湧かないのですが、何か良い案があればアドバイスを頂けますと幸いです。
データ内に「#」を大量に含むケースがあるため、「#」を含むか検索する方法はできるだけ採用したくありません。
何卒よろしくお願いいたします。

回答
投稿日時: 22/06/23 22:55:57
投稿者: simple

エラーは発生した時点で解消しておくことかと思います。
もっといえば、IFERROR関数などでエラーになったときの対応策を講じておくのが普通でしょう。
もっとも、グラフを書く際に、意図的に#N/Aを設定することはあります。(outlier対応)
 
>データ内に「#」を大量に含むケースがあるため
どのようなケースなんでしょう。
後学のために、どういう意図で発生するのか教えて下さい。
文字列の一部に含まれるというケースなら返答不要です。それは今回のテーマからはずれます。
 
なお、
・TypeName(セル.Value)とすると,"Error"という文字列が返ります。(.Valueは省略不可)
・ワークシートのIsError関数を使えば、ブール値として結果を知ることもできるでしょう。

投稿日時: 22/06/24 08:56:50
投稿者: のらねこ

>simpleさん
 
>文字列の一部に含まれるというケースなら返答不要です。それは今回のテーマからはずれます。
 返答不要とのことでしたが、ご指摘の通りフリーテキスト項目での文字列使用です。
 
>なお、
>・TypeName(セル.Value)とすると,"Error"という文字列が返ります。(.Valueは省略不可)
>・ワークシートのIsError関数を使えば、ブール値として結果を知ることもできるでしょう。
 
この方法で対策を検討してみます。
ご教授ありがとうございました!