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

質問

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

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

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

中身を見ると

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

回答No.2

nao-y · ベストアンサー

> 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 さん

ヾ(✿❛◡❛)ノ(ハード)☆,。・:*:・

素晴らしい回答をありがとうございました！！

本当に本当に助かりました
＆
的確な回答に感動すらしています！

見やすい新たなコードも思い通りに動いてくれています。
すごいですね！！！

私ももっと勉強しないとです。。。
どーーしてもマクロって、苦手意識があって勉強避けてました。
でも使えたらほんとに便利ですよね！

すげー！

ファンになりました！

回答No.3

nao-y

ああ、すみません。
余談の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

お礼

丁寧な回答、ありがとうございました！！

また質問を見かけたら、教えてやってください☆

回答No.1

nao-y

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

＊＊＊

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

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

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

お礼

たびたびごめんなさい！
必要情報が洩れてました；；

Worksheets("master sheet").Cells(i, "BL").Value

値＝<オーバーフローしました。>
型＝Variant/Integer

Worksheets("請求書フォーム").Range("J1").Value

値＝2012/05/31 14:31:44
型＝Variant/Date

このようになっていました！！

よろしくお願いします☆

ｍ（＿　＿）ｍ

補足

ご回答ありがとうございます！！

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

ウォッチというものを確認してみました！

Watch : : i : 179 : Long

と表示されまして、

値＝179
型＝Long

のようです。

…(;◉∀◉)
本当にごめんなさい。
さっぱりわかりません。。。

古いシートを確認したのですが、
同じ記述でちゃんと動いています。
データ量なんて２行ほどしか追加していないのに。。。

頼ってしまい恐縮ですが、
助けてください！！

よろしくお願いします。