[[20220801150637]]　『オブジェクト変数またはWithブロック変数が設定さ』（BABA）　ページの最後に飛ぶ

『オブジェクト変数またはWithブロック変数が設定されていません。を回避したい』（BABA）

銀行別の支店名を入力し、別シートを参照して支店コード.txtへ下記コードで表示しています。
Private Sub 支店名_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim r As Range, rTmp As Range, searchRng As Range
    If 支店名.Value = "" Then Exit Sub
    Set searchRng = Worksheets("銀行別支店コード").Columns("C:C")
    Set r = searchRng.Find(支店名.Value, LookIn:=xlValues, lookat:=xlWhole)
    Set rTmp = r
    Do While Cells(r.Row, "A").Value <> 金融機関名.Value
        Set r = searchRng.FindNext(r)
        If r.Address = rTmp.Address Then
            Exit Do
        End If
    Loop
    If Not r Is Nothing Then
        支店コード.Value = r.Offset(0, 1).Value
    Else
        支店コード.Value = ""
    End If

End Sub

しかし該当支店がない場合「オブジェクト変数またはWithブロック変数が設定されていません。」とエラーになり、デバックすると「Do While Cells(r.Row, "A").Value <> 金融機関名.Value」が反転しています。
該当支店が無ければ、Msgで「登録がありません」と表示し、手入力できるようにしたいのですが何処でMsgを出し、どのようなコードで回避したらよいか教えていただけませんでしょうか？
ご指南のほど、よろしくお願いいたします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

↓が参考になるやもしれません。確認されてみては？

 【参考】
[[20220728164132]]　『ユーザーフォームで銀行に対応した支店コードの入力ができません』（ケイコ）

（もこな２） 2022/08/01(月) 15:56

 質問内容とは関係ないですが、
 Do ループからExit Do するときは、支店名は一致するけど、金融機関名で一致するのがない場合です
 Exit Do する直前に Set r = Nothing いれとかないとヤバくないですか？
（´・ω・`） 2022/08/01(月) 16:29

【銀行別支店コード】シートのレイアウト

    __A___   _____B____   ___C__   ___D__
 1  銀行名   銀行コード   支店名   支店コード
 2  A銀行       0001      本店       000
 3  A銀行       0001      支店１     001
 4  A銀行       0001      支店２     002
 5  B銀行       0002      本店       001
 6  B銀行       0002      支店１     002

おそらく↑のようなレイアウトだとおもうのですが、[[20220728164132]]でも述べたように、Findメソッドで複数の条件をキーにするという発想自体がイマイチだとおもうんですよね。
ただ、まぁFind（FindNext）メソッドでは無理なのかと言われれば、↓のような感じであればできるかもしません。
興味があれば、ステップ実行して研究してみてください

 ※ユーザーフォームを用意するのが面倒なのでテストはしてません。ミスっていたらごめんなさい。

    Private Sub 支店名_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Dim r As Range
        Dim 最初のセル番地 As String
        Dim フラグ As Boolean

        Set r = Worksheets("銀行別支店コード").Columns("C:C").Find(支店名.Value, LookIn:=xlValues, lookat:=xlWhole)

        If r Is Nothing Then
            MsgBox "どの金融機関にも「" & 支店名.Value & "」はありません"
            Exit Sub
        End If

        If r.Offset(, -2).Value = 金融機関名.Value Then
            '▼１発目で見つかった場合
            支店コード.Value = r.Offset(, 1).Value
            フラグ = True
        Else
            最初のセル番地 = r.Address
            Do While 最初のセル番地 <> r.Address
                r = Worksheets("銀行別支店コード").Columns("C:C").FindNext(r)

                If r.Offset(, -2).Value = 金融機関名.Value Then
                    '▼２発目でみつかった場合
                    支店コード.Value = r.Offset(, 1).Value
                    フラグ = True
                    Exit Do
                End If
            Loop
        End If

        If フラグ = False Then
            '▼最後まで見つからなかった場合
            支店コード.Value = "該当金融機関なし"
        End If
    End Sub

（もこな２） 2022/08/01(月) 17:44

もこな２様　ありがとうございます。
諸々危ないところでした！
助かりました。

（BABA） 2022/08/01(月) 18:38

■１
＞諸々危ないところでした！
仰ってる意味がよくわかりません。何が問題だったのですか？

■２
＞助かりました。
こちらの意図としては、完成品のプレゼントではなく研究用のコードを提供したつもりですので、きちんと理解したうえで必要な部分だけご自身のコードに取り込んでください。

■３
やんわりと書いたつもりですが、[[20220728164132]]と同じ方ですよね。
掲示板のルール上ダメとはなっていないと思いますが、ニックネームをコロコロ変えることにメリットはないと思いますので、何れかに統一することをお勧めします。

 ※あまりに状況が似通っているので同じ方だとおもいますが、万が一ちがっていらごめんなさい。

＝＝＝以下、[[20220728164132]]と同じ方と仮定してのコメントです。＝＝＝

■４
指摘を受けてChangeイベントからExitイベントに変更されたようですが、やはりどうしても「テキストボックス」を使うことにはこだわりたいのでしょうか？
テキストボックスを使うからこそ、コードが割り当てられていないものを"入力"する可能性が出てくるわけですよね。

＞該当支店が無ければ、Msgで「登録がありません」と表示し、手入力できるようにしたい
↑のようなことをおっしゃっていますが、本来はそれは【マスタメンテナンス】の分野であって入力時（コード検索時）にやるようなことではないとおもいます。

よってコード検索についてVBA的に解決を図るならばこういうアプローチになっていくように思います。
【ユーザーフォームモジュールに記述】

    Option Explicit
    Dim 銀行dic As Object
    Dim 支店Dic As Object
    '====================================================================================
    Private Sub UserForm_Initialize()
        Dim i As Long
        Dim myDic As Object
        Set myDic = CreateObject("Scripting.Dictionary")

        With Worksheets("銀行別支店コード")
            On Error Resume Next
            For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
                myDic.Add .Cells(i, "A").Value, .Cells(i, "B").Value
            Next i
            On Error GoTo 0
        End With

        Set 銀行dic = myDic
        Me.ComboBox1.List = myDic.keys
    End Sub
    '====================================================================================
    Private Sub ComboBox1_Change()
        Dim i As Long
        Dim myDic As Object
        Set myDic = CreateObject("Scripting.Dictionary")

        Me.Label1 = 銀行dic.Item(ComboBox1.Value)

         With Worksheets("銀行別支店コード")
            On Error Resume Next
            For i = 2 To .Cells(.Rows.Count, "C").End(xlUp).Row
                If .Cells(i, "A").Value = Me.ComboBox1.Value Then myDic.Add .Cells(i, "C").Value, .Cells(i, "D").Value
            Next i
            On Error GoTo 0
        End With

        Set 支店Dic = myDic
        Me.ComboBox2.List = myDic.keys
        Me.ComboBox2.Value = ""
    End Sub
    '====================================================================================
    Private Sub ComboBox2_Change()
        If Me.ComboBox2.Value = "" Then
            Me.Label2 = ""
        Else
            Me.Label2 = 支店Dic(ComboBox2.Value)
        End If
    End Sub

 ※1 各オブジェクトについて細かい説明は省きます（オブジェクト名からわかりますよね）
 ※2 filter関数とか多段配列使えばいいのかもしれませんが、そこまで詳しくないので、より効率の良いアプローチは諸兄姉にお任せします。

（もこな２） 2022/08/02(火) 13:54

コメント返信：

[ 一覧(最新更新順) ]

[[20220801150637]] 『オブジェクト変数またはWithブロック変数が設定さ』（BABA） ページの最後に飛ぶ

[[20220801150637]]　『オブジェクト変数またはWithブロック変数が設定さ』（BABA）　ページの最後に飛ぶ