VBAマクロエラー【オーバーフローしました。】

質問

以下のVBAマクロで突然エラーが出るようになってしまいました。
原因がわからず困っています。
どなたかご教授ください。

該当部分 :S = Range("B2").End(xlDown).Row
エラーMrg:実行時エラー'6': オーバーフローしました

----------マクロ文----------

Sub 部担コード読み替え()

Dim R_Count As Integer
Dim P_Sheet As String
Dim S As Integer

'データ取込用のファイルを開く
Workbooks(D_Book).Activate

Sheets("Data1").Select

Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
S = Range("B2").End(xlDown).Row
Range("C2:C" & S).Formula = "=SUBSTITUTE(SUBSTITUTE(RC[-1],""%"",""1""),""*"",""2"")"
Range("C1").Value = "部担コード"

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select

Sheets("Data2").Select
Columns("C:C").Select
Selection.Insert Shift:=xlToRight

Range("B2").Select
S = Range("B2").End(xlDown).Row
Range("C2:C" & S).Formula = "=SUBSTITUTE(SUBSTITUTE(RC[-1],""%"",""1""),""*"",""2"")"
Range("C1").Value = "部担コード"

Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("G2:G" & S).Formula = "=SUBSTITUTE(SUBSTITUTE(RC[-1],""%"",""1""),""*"",""2"")"
Range("G1").Value = "キー"

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

Range("A1").Select

End Sub

回答No.3

High_Score

No2です。補足します。
Cells(Rows.Count,2)の代わりにCells(Rows.Count, "B")と表記も出来ます。この法がわかり易いでしょう。

回答No.2

High_Score

普通の対処
Dim S As Long
S = Cells(Rows.Count, 2).End(xlUp).Row

B列のエクセル最終行(Rows.Count)まで飛び、そこから上方向へデータを探します。データが空っぽなら１行目まで戻るのでS=1です。この方法の利点はデータ途中で空白があっても、そこで終わらずデータのある最終行まで探してくれる事です。

質問文のはB2からスタートし、下方向へデータの終わりを探しに行くものです。B2が偶然空白の場合は、空白の終わりを探しに行きます。元々データが空っぽで下の行も全部空白の場合、マクロは「空白の終わり」を探して延々とエクセルで用意されている最終行までデータを探しに行きます。つまりS=エクセルの最終行番号、確か１００万程度。そこでオーバーフローしたのでしょう。

根本原因は、マクロが空っぽのデータに対応してないためです。IntegerをLongに修正すれば確かにオーバーフローは無くなります。この場合、C列に延々と下までゼロが入るのではないでしょうか？

回答No.1

eden3616

Dim S As Integer

変数Sの宣言部分で整数型 (Integer)が宣言されています。
Integer型の変数では-32,768 ～ 32,767の範囲の整数値しか扱う事が出来ません。

S = Range("B2").End(xlDown).Row

この行はB2からCtrl+↓をした時の行番号を変数Sに格納します。
上記理由から、対象（B2からCtrl+↓したセル）の行番号が32768行以上になりませんか？

エクセルが取り扱える最大行数の1048576行まで対応するには
宣言部分の型を長整数型 (Long) にしてください。
以下の部分を修正する事で変数Sは-2,147,483,648 ～ 2,147,483,647の
範囲の値を取り扱えるようになります。

Dim S As Integer
　　　↓
Dim S As Long

お礼

早速のご回答ありがとうございます！
会社のPCで明日試してみます！！