マクロ初心者(；◔ิд◔ิ)オーバーフロー！！

Question

異なるシートで一致するデータがあった場合、
そのセルを選択して値貼り付けするというマクロを作りました。

作ったとはいえ、
教えてもらったマクロを試行錯誤して使えるようにアレンジしただけなので、
なにがなんだかよくわかっていません。

下記の記述でマクロを使用していましたが、
突然エラーになって使用できなくなりました。。。。

中身を見ると

If Worksheets("master sheet").Cells(i, "BL").Value = Worksheets("請求書フォーム").Range("J1").Value Then

この部分が黄色に塗りつぶされてるのですが、
どこをどう直したらいいのか全くわかりません。

ちなみに、ほかのファイルでも同じようなマクロを使用していますが、
そちらは問題なく使用できています。

なんとか教えていただけないでしょうか。

よろしくお願いします！！！！

Sub こぴぺ()
'
' こぴぺ Macro
      Dim sheet1 As Worksheet
    
    Set sheet1 = Worksheets("請求書フォーム")
    sheet1.Activate

Dim target As Range
  Dim i As Long
   Worksheets("請求書フォーム").Range("J1").Select

For i = 1 To Worksheets("master sheet").Range("BL65536").End(xlUp).Row
  If Worksheets("master sheet").Cells(i, "BL").Value = Worksheets("請求書フォーム").Range("J1").Value Then
    If target Is Nothing Then
      Set target = Worksheets("master sheet").Range("BL" & i)
    Else
      Set target = Union(target, Worksheets("master sheet").Range("BL" & i))
    End If
  End If
  Next i

Set sheet1 = Worksheets("master sheet")
    sheet1.Activate

If Not target Is Nothing Then target.Select

Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

nao-y · Accepted Answer

> Watch : : i : 179 : Long

これは、i=179行目、つまり"master sheet"の179行目でエラーが発生した
ということです。

> Worksheets("master sheet").Cells(i, "BL").Value
> 値＝<オーバーフローしました。>
> 型＝Variant/Integer
> Worksheets("請求書フォーム").Range("J1").Value
> 値＝2012/05/31 14:31:44
> 型＝Variant/Date

ということは、"master sheet"のセルBL179でオーバーフローが起こっている
ということです。

この結果から察するに、"master sheet"のBL列と、"請求書フォーム"のJ1セルには
日付が入っているということですね。
普通、日付が入っているセルのValueの型は、"Variant/Date"となるはずですが、
BL179の型は何故かInteger型になってますね。。。

Integer型にセットできる数の範囲は-32,768～32,767です。
ところが、Date型の値、例えば「2012/5/31」は数値に直すと「41060」になります。
「41060」を無理やりInteger型の変数にセットしようとすると、
「そんな大きな数セットできませ～ん」と「オーバーフロー」エラーになるんです。

もしかしてBL179の表示が、YYYY/MM/DDじゃなくて、「41060」とか数字になってませんか？
なってなくても、BL列を選択→右クリック→セルの書式設定で、
分類：日付、種類：1997/3/4
を選んでみてから、マクロを実行してみてください。

＊＊＊
ところで。。。

> やっぱり突っ込みどころ満載なんですね（；△；）
> もしよろしければ、遠慮なく突っ込んでいただけたら嬉しいです！

では遠慮なく（笑）
まあいずれも動作には影響はなくて、後からソースコードを見たときの
見易さの問題なんですけどね。

・変数宣言（"Dim"で始まる文）は、普通、Subの最初にまとめて書きます。

つまり、
> Dim target As Range
> Dim i As Long
は、
> Dim sheet1 As Worksheet
のすぐ下に書きましょう。

・Worksheets("請求書フォーム").Range("J1").Select
という一文は必要ないと思います。
Selectした後、そのセルに対して何の操作も行っていませんから。

・If Worksheets("master sheet").Cells(i, "BL").Value = Worksheets("請求書フォーム").Range("J1").Value Then
の"="以降は、
sheets1.Range("J1").Value 
と書けます。
・"Worksheets("master sheet")"はWith文を使って簡略化できます。

つまり、"For i=1～"から"sheet1.Activate"は以下のように書けます。

With Worksheets("master sheet")
    For i = 1 To .Range("BL65536").End(xlUp).Row
        If .Cells(i, "BL").Value = sheet1.Range("J1").Value Then
            If target Is Nothing Then
                Set target = Worksheets("master sheet").Range("BL" & i)
            Else
                Set target = Union(target, Worksheets("master sheet").Range("BL" & i))
            End If
        End If
    Next i
    .Activate
End With

以上、余談でした。

nao-y · Answer

ああ、すみません。
余談のWith文のところを以下の通り修正します。

With Worksheets("master sheet")
 For i = 1 To .Range("BL65536").End(xlUp).Row
    If .Cells(i, "BL").Value = sheet1.Range("J1").Value Then
        If target Is Nothing Then
            Set target = .Range("BL" & i)
        Else
            Set target = Union(target, .Range("BL" & i))
        End If
    End If
 Next i
 .Activate
End With

nao-y · Answer

ソースコードについて２，３ツッコミたいところは
ありますが(^^;、それは置いておいて…。

＊＊＊

うーん、iがIntegerだったらオーバーフローはわかるんですが…

ちなみに、エラーになったときの
i
Worksheets("master sheet").Cells(i, "BL").Value
Worksheets("請求書フォーム").Range("J1").Value 
値と型を教えてください。

エラーで止まったときに、ソースコードのiをドラッグ→右クリック→ウォッチ式の追加
をすると、「ウォッチ」と左上に書かれたウィンドウに“i”という行が現れ、値と型を確認できます。
Worksheets(～.Valueについても同様です。

マクロ初心者(；◔ิд◔ิ)オーバーフロー！！

ああ、すみません。

> Watch : : i : 179 : Long

ソースコードについて２，３ツッコミたいところは

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング