VBAでFOR文を回してデータを突合させたい

Question

前提

Sheet1(start)とSheet2(goal)からそれぞれ日付データを取得し、FOR文で突合させて、足りない部分をSheet1からSheet2へコピーしたいです。

詳細

日付	データA	データB
11/22	1000	2000

以上のようなデータが入ったシートが2枚あり、日付の部分を突合させ、不足分をコピペするといったものです。ただこれまでPythonしか使ったことがないため、データの型定義など慣れないことが多く、うまくいかないため質問いたしました。

発生している問題・エラーメッセージ

アプリケーション定義またはオブジェクト定義のエラーです

該当のソースコード

Sub compare_date()

Dim L, lRow, M, mRow As Long
 
lRow = Sheets("start").Cells(Rows.Count, "A").End(xlUp).Row '日付データの最終行
mRow = Sheets("goal").Cells(Rows.Count, "A").End(xlUp).Row '比較先の日付データの最終行

For L = 2 To lRow
    If L >= Sheets("goal").Cells(M, "A") Then
        MsgBox L
    End If
Next L

End Sub

試したこと

MsgBoxを使っているのはコピーする前に、とりあえず値が適切か拾いたいためです。

試したこととしては、For Each などにして、FOR文を回しましたが、Variant型にしないといけずうまくいきませんでした。

Answer 1

変数Mが未定義です。また、Lはただの行数を表す数値なので時刻比較をしたいならば正しいとは言えません。

Sub compare_date()

Dim L as long , lRow as long, M as long, mRow As Long
 
dim wsStart as worksheet:set wsStart=thisworkbook.Sheets("start")
dim wsGoal as worksheet:set wsGoal=thisworkbook.Sheets("goal")

lRow = wsStart.Cells(Rows.Count, "A").End(xlUp).Row '日付データの最終行
mRow = wsGoal.Cells(Rows.Count, "A").End(xlUp).Row '比較先の日付データの最終行

For L = 2 To lRow
    for M= 2 to mRow
        If wsStart.cells(L,1).value >= wsGoal.Cells(M, 1).value Then
            MsgBox L '時刻を出したいならwsStart.cells(L,1).value
            '処理的に一行一回ずつしか表示させなくていい気がするので内側のループを飛ばす
            Exit For
        End If
    nest M
Next L

End Sub

範囲セル指定でfor each を使う場合は変数にrangeオブジェクトが代入されるためrange型でないとエラーが出るのが原因です(variantは自動解釈してくれるので問題ありませんが、Long型だと型違いでエラーが出たということです)

for eachを使う場合

```ここに言語を入力
Sub compare_date()

Dim L as Range , lRow as long,
Dim M as Range, mRow As Long
 
dim wsStart as worksheet:set wsStart=thisworkbook.Sheets("start")
dim wsGoal as worksheet:set wsGoal=thisworkbook.Sheets("goal")

lRow = wsStart.Cells(Rows.Count, "A").End(xlUp).Row '日付データの最終行
mRow = wsGoal.Cells(Rows.Count, "A").End(xlUp).Row '比較先の日付データの最終行

For each L in wsStart.Range(wsStart.cells(2,1),wsStart.cells(LRow,1)
    for M= 2 to mRow    '一応、for each分は順番の保障がされてないので比較側は普通に回す
        If L.value >= wsGoal.Cells(M, 1).value Then
            MsgBox L.value
            Exit For
        End If
    nest M
Next L

End Sub

ただ、同じ時刻のデータがあるかどうかを比較するならばVBAにもDictionaryがあるのでこちらを使ったほうが簡潔で便利だと思います。
https://daitaideit.com/vba-dictionary/

Answer 2

型定義以前にロジックの問題ですね。

L >= Sheets("goal").Cells(M, "A")
L(行番号)と日付(A列の値)を比較しても無意味です。

突合するなら、２つのシートの値を二重ループして比較する必要があります。

下記のようなコードになるでしょう。

Sub compare_date()

    Dim L As Long, lRow As Long, M As Long, mRow As Long '型宣言は一つずつする必要がある
 
    lRow = Sheets("start").Cells(Rows.Count, "A").End(xlUp).Row '日付データの最終行
    mRow = Sheets("goal").Cells(Rows.Count, "A").End(xlUp).Row '比較先の日付データの最終行

    For L = 2 To lRow
        For M = 2 To mRow
            If Sheets("start").Cells(L, "A") = Sheets("goal").Cells(M, "A") Then
                Exit For
            End If
        Next M
        If M > mRow Then MsgBox L 'カウンターが最終行より上なら一致する値無し
    Next L

End Sub

ただ、Excelなので、関数を使えば２重ループする必要はないです。
あるいは、連想配列(Dictionaryオブジェクト)を使うと高速化できます。

エクセルのMatch関数を使って検索するコード例

Sub compare_date2()

    Dim rStart As Range, rGoal As Range
    With Sheets("start")
        Set rStart = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp)) '日付データのセル範囲
    End With
    With Sheets("goal")
        Set rGoal = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp))  '比較先の日付データのセル範囲
    End With
    
    Dim l As Variant, i As Long
    For Each l In rStart
        On Error Resume Next
        i = WorksheetFunction.Match(l, rGoal, 0) 'Matchで一致データを検索
        If Err.Number <> 0 Then MsgBox l.Row '一致データがないとエラーになるのでメッセージ表示
        On Error GoTo 0
    Next l

End Sub