[VBA]A列に特定の文字がある場合繰り返しをする

Question

前提・実現したいこと

A列に特定の文字が入っているところからFor文をスタートさせたいです。

↓のプログラムでは
都道府県.xlsxのL4にThisWorkbookのJ2をコピー
都道府県.xlsxnのM4にThisworkbookのL2をコピー
都道府県.xlsxのN4にThisworkbookのM2をコピー
・
・
・
と、変数cntとiを使ってデータ数分繰り返しを行っています。

今はcnt=4と具体的に数字を代入をしていますが、
始まるセルが変わる可能性もある場合(都道府県.xlsxのセルを追加した場合など）を考えて
A列に'東京'と入っている場合以下のFor文を繰り返す　というコードを書きたいです。

Dim wb As Workbook
Set wb = Workbooks("都道府県.xlsx")
Dim i As Long
Dim cnt

    cnt = 4
    
    For i = 2 To ThisWorkbook.Worksheets("A").Cells(Rows.Count, 1).End(xlUp).Row

       
        wb.Sheets(1).Range("L" & cnt) _
         =ThisWorkbook.Worksheets("A").Range("J" & i)
       
         wb.Sheets(1).Range("M" & cnt) _
        = ThisWorkbook.Worksheets("A").Range("L" & i)

         wb.Sheets(1).Range("N" & cnt) _
        = ThisWorkbook.Worksheets("A").Range("M" & i)
        
         wb.Sheets(1).Range("O" & cnt) _
        = ThisWorkbook.Worksheets("A").Range("K" & i)
    
        cnt = cnt + 1
    
    Next
    

End Sub

試したこと

試しにIF文を入れてみたのですが、
ここでcntに入れる値何が入るのかが分からなくて困っています。

ご教授お願いいたします。

Dim wb As Workbook
Set wb = Workbooks("都道府県.xlsx")
Dim i As Long
Dim cnt

    cnt = 4
    

　　'i=2から最終行まで繰り返し
    For i = 2 To ThisWorkbook.Worksheets("A").Cells(Rows.Count, 1).End(xlUp).Row
　　　　'もしA列に東京という文字が入っていたら
       　IF　wb.Sheets(1). InStr(wb.Sheets(1).Range("A" & i), "東京")　Then

        wb.Sheets(1).Range("L" & cnt) _
         =ThisWorkbook.Worksheets("A").Range("J" & i)
       
         wb.Sheets(1).Range("M" & cnt) _
        = ThisWorkbook.Worksheets("A").Range("L" & i)

         wb.Sheets(1).Range("N" & cnt) _
        = ThisWorkbook.Worksheets("A").Range("M" & i)
        
         wb.Sheets(1).Range("O" & cnt) _
        = ThisWorkbook.Worksheets("A").Range("K" & i)
    
        cnt = cnt + 1

    　　End　IF
    Next
    

End Sub

Comments

[itagagaki]

コピー先は4行目から順番に1行ずつ進むのですよね？

で、2行目から最終行までの間で、A列が東京の行だけ、コピー先の4行目以降にコピーしたいのですか？

それとも、2行目から最終行までの間で、A列が東京である最初の行を探し、見つかったら、その行から最終行までの間で、コピー先の4行目以降にコピーしたいのですか？

[aaaskw123123]

コピー先は4行目から始まるとは決まってないです.
そのため、実現したいことは後者になります。

[aaaskw123123]

A列に東京という文字が入っているか確認する必要があると思うので、
4行目から始まるかはわからない　という感じです。

[itagagaki]

えーと、確かにこれは混乱しています。
東京が入っているかどうかをチェックしているのはコピー先であるwb.Sheets(1)なのに
コピー元のシートのインデックスである i を使ってチェックしています。まずそこからおかしいです。

要件をしっかり明文化しましょう。
・「A列に」というのはどのシートの話をしているのか→コピー元の話かと思っていたらコピー先の話だった
・コピー元からは2行目から最終行まで全部をコピーするのか？
・各行のコピー先はどこ？

[aaaskw123123]

・A列➔コピー先の話
・コピー元からは２行目からデータ数分だけコピーしたいので最終行と設定しています。

[Usirow]

「コピー先A列で東京が入っているセルを見つけたら、そこを始点としてFor文を回す」のか「コピー先A列の中でFor文を回して、東京とあるセルにだけ処理をするのか」が曖昧な気がするのですが、どちらなのでしょう？

Answer 1

2行目から最終行までの間で、A列が東京の行だけコピーする
コピー先は4行目から順番に1行ずつ進む

それなら「試したこと」のコードのままでいいのではないでしょうか。

下記は、変数名がcntでは意図がわかりにくいのでdest_rowに変更して、インデントを整形しただけで、
同じコードです。

Dim wb As Workbook
Set wb = Workbooks("都道府県.xlsx")
Dim i As Long
Dim dest_row;

    dest_row = 4 'コピー先の行

　　'i=2から最終行まで繰り返し
    For i = 2 To ThisWorkbook.Worksheets("A").Cells(Rows.Count, 1).End(xlUp).Row

　　　　 'もしA列に東京という文字が入っていたら
        IF　wb.Sheets(1).InStr(ws.Range("A" & i), "東京") Then

            wb.Sheets(1).Range("L" & dest_row) _
                =ThisWorkbook.Worksheets("A").Range("J" & i)

            wb.Sheets(1).Range("M" & dest_row) _
                = ThisWorkbook.Worksheets("A").Range("L" & i)

            wb.Sheets(1).Range("N" & dest_row) _
                = ThisWorkbook.Worksheets("A").Range("M" & i)

            wb.Sheets(1).Range("O" & dest_row) _
                = ThisWorkbook.Worksheets("A").Range("K" & i)

            dest_row = dest_row + 1 'コピー先の行を1つ進める

    　　End　IF
    Next


End Sub

Comments

[aaaskw123123]

申し訳ございません。
4行目から始まるとは限らないので、後者の方でした????

[aaaskw123123]

なので、cntの値に何が入るのかわからないという質問です..すみません。

[itagagaki]

ん？4行目から始まるとは限らないとは？
コピー元は2行目から
コピー先は4行目から
ですよね？
コピー先が4行目からではないということですか？

[aaaskw123123]

コピー元は2行目から始めると決まっていて、
コピー先はA列に東京という文字がある場合
繰り返しをしたいという　感じです。

例えば
最初はA列の1,2,3行目に愛知があったとして4行目は東京があります。
もし、セルを追加して4行目に愛知と入力した場合
４行目から始まるとは断言できないので、
A列に東京がある間にFor文を回したいという意味です。

分かりにくくてごめんなさい。

[itagagaki]

これは確かに混乱しています。
要件をしっかり定義しましょう。
たとえばただ「A列」と言ったのでは曖昧です。現に誤解が生じていました。
どのシートのA列なのかを明文化しましょう。
コピー元の2行目から最終行まで、すべての行について、コピーは行うのですか？
その際、それぞれの行のコピー先は何行目になるべきなのですか？
これを文章で説明できないことにはプログラムのコーディングも不可能です。

Answer 2

Sub sample1()
    Dim wb As Workbook, saki As Worksheet
    Set wb = Workbooks("都道府県.xlsx")
    Set saki = wb.Sheets(1)

    Dim moto As Worksheet
    Set moto = ThisWorkbook.Worksheets("A")
    
    Dim cnt As Long
    cnt = saki.Range("A:A").Find("東京").Row
    
    Dim i
    For i = 2 To moto.Cells(Rows.Count, 1).End(xlUp).Row
        saki.Range("L" & cnt) = moto.Range("J" & i)
        saki.Range("M" & cnt) = moto.Range("L" & i)
        saki.Range("N" & cnt) = moto.Range("M" & i)
        saki.Range("O" & cnt) = moto.Range("K" & i)
        cnt = cnt + 1
    Next
End Sub
    
    
Sub sample2()
    Dim wb As Workbook, saki As Worksheet
    Set wb = Workbooks("都道府県.xlsx")
    Set saki = wb.Sheets(1)

    Dim moto As Worksheet
    Set moto = ThisWorkbook.Worksheets("A")
    
    Dim lastRow As Long
    lastRow = moto.Range("A" & moto.Rows.Count).End(xlUp).Row
    
    Dim i, j
    j = 2
    For i = 1 To saki.Range("A" & saki.Rows.Count).End(xlUp).Row
        If saki.Range("A" & i).Value = "東京" Then
            saki.Range("L" & i).Value = moto.Range("J" & j).Value
            j = j + i
            If j > lastRow Then Exit For
        End If
    Next
End Sub

Comments

[aaaskw123123]

回答ありがとうございます。
変数iは使わないということでしょうか・・・？

[jinoji]

このコードで、以前は４と決め打ちしていた東京の場所を決定してから、
元のコードの実行する、という考え方でした。

[aaaskw123123]

編集ありがとうございます。
Findを使ったら開始行を取得することができました。
助かりました!!
ベストアンサーにさせていただきます。