For Each ループを入れ子にして、Sheetsオブジェクトから値を参照したいです。

Question

98と入れると、自動的に9/8になります。

Private Sub CommandButton1_Click()
    Dim kugiri As String
    Dim mYmm As String
    Dim mYdd As String
    Dim mYmmdd As String
    kugiri = "/"
   
    If Len(Me.TextBox1.Text) <> 3 Then
        mYmm = Left(Me.TextBox1.Text, 1)
        mYdd = Right(Me.TextBox1.Text, 1)
    Else
        mYmm = Left(Me.TextBox1.Text, 1)
        mYdd = Right(Me.TextBox1.Text, 2)
    End If
      
    mYmmdd = mYmm & kugiri & mYdd
    
    Call Backup_start(mYmmdd)
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii < Asc(0) Or KeyAscii > Asc(9) Then
        MsgBox "月日（MMDD）を入力してください"
        KeyAscii = 0
    End If
End Sub

Sub Backup_start(mYmmdd As String)
    Dim myRange1 As Range
    Dim myRange2 As Range
    Dim sevname As String
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = Worksheets("Main")
    Set ws2 = Worksheets("List")
    MsgBox mYmmdd & "で検索します"
    
For Each myRange1 In ws1.Range("B1:B30000")
    For Each myRange2 In ws2.Range("F5:F30")
    
        If InStr(myRange1.Value, mYmmdd) <> 0 And myRange1.Offset(0, 1).Value Like myRange2 And myRange1.Offset(0, 6).Value Like "30233" Then
            Debug.Print Right(myRange1.Value, 11) & " " & myRange2
        End If
    Next myRange2
Next myRange1
End Sub

Comments

[hatena19]

コードはマークダウン記法のコードタグ内に入れてください。
コードを選択して、ツールバーの<code>をクリックでも可。
myRange1 , myRange12, mYmmdd の変数宣言のコード、mYmmddの初期値設定のコードも提示してください。
「いつもEmpty値になります。」というのはどのように確認しましたか。ステップ実行したのですか。

[hanju8810tt]

マークダウン記法のタグ？に入れなおしました。
「いつもEmpty値になります。」はステップ実行しての結果を確認しました。

[meg_]

質問のコードを実行すると「インデックスが有効範囲にありません」のエラーが出ます。

[hanju8810tt]

すいません。このコードをステップ実行すると、myRange2で"nothing"となります。

[meg_]

すみません。私の方の実行環境が悪かったみたいです。
先ほどのコードは問題なく動作しました。コード自体には問題ないかと思います。

[hanju8810tt]

問題なく動作したというのは、myRange2で別シートからの値を引っ張ってこれたということですか？

[meg_]

「MsgBox myRange2.Value」で値を表示できましたよ。

※Windows8.1、Excel2013

[hanju8810tt]

ありがとうございます。なぜかこちらでは”Withブロック変数が・・・"となります。どこが悪いんだろう・・・

[hanju8810tt]

あ、表示はできました。しかし期待通り動作しないです。stepしてみたんですが、これだとmyRang1が1回目実行するときにmyRang2全周ループしてます。それはいいんですが、myRang1が2週目ループしたときにmyRang2がまったく動作していないです。どうすればよいんでしょうか・・

[meg_]

Forループの直後でそれぞれ「Debug.Print myRange1.Address」「Debug.Print myRange2.Address」でセルの移動を確認してみてください。ループしていると思いますが。。。

[hanju8810tt]

ありがとうございます！イミディエイトウィンドウで確認できました。しかし、条件に引っかからないです。3つ目の条件である、「myRange1.Offset(0, 6).Value」がEmpty値になります。なぜだ・・

[meg_]

MainシートのH列には値が入力されていますか？

[hanju8810tt]

5桁の数字なんですが、入力されているんですよね。

Answer 1

98と入れると、自動的に9/8になります。

この仕様はどうなんでしょうか？
123は12/3か1/23かどっちにも取れるかと。。。
素直に12/3とか入れませんか？
そうしたら、
DateValue関数で日付に変えやすいでしょうし。
（年の値も入れないと、意図しない動作になることがありそうな気がしますが）

数値として検索するか、文字列として検索するかの問題もあるかも知れませんが、
ループしなくても、エクセルVBAなら、
エクセルに検索の関数（Match,Vlookup等)があるので、
そちらを使った方が楽かなと思います。
どうせなら、シート上に数式を入れたらさらに楽で、
処理も速いかなと思います。

Comments

[hanju8810tt]

ありがとうございます！DateValueとVlookupで自動化プログラムができました！