ひできちの楽天ブログ

 


Dim ws As Worksheet
Dim lastCol As Long
Dim lastRow As Long
Dim i As Long, j As Long
Dim colName As String
Dim dataType As String
Dim notNull As String
Dim maxLen As Long
Dim cellValue As String
Dim errorMsg As String
Dim hasError As Boolean

Set ws = ActiveSheet

'--- カラム数・行数 ---
lastCol = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'--- まず 9 行目のエラー表示セルをクリア ---
For j = 1 To lastCol
ws.Cells(9, j).Value = ""
Next j

hasError = False

'===========================================================
'  バリデーション処理
'===========================================================
For j = 1 To lastCol

colName = Trim(ws.Cells(3, j).Value)
dataType = UCase(Trim(ws.Cells(4, j).Value))
notNull = UCase(Trim(ws.Cells(5, j).Value))
maxLen = Val(ws.Cells(6, j).Value)

errorMsg = ""

'--- データ行は 12 行目以降 ---
For i = 12 To lastRow

cellValue = Trim(ws.Cells(i, j).Value)

'===========================
' NOT NULL チェック
'===========================
If notNull = "NOT NULL" Then
If cellValue = "" Then
errorMsg = "notNull違反"
hasError = True
Exit For
End If
End If

'===========================
' サイズ長チェック
'===========================
If maxLen > 0 Then
If Len(cellValue) > maxLen Then
errorMsg = "サイズ長違反"
hasError = True
Exit For
End If
End If

Next i

'--- エラーがあれば 9 行目に出力 ---
If errorMsg <> "" Then
ws.Cells(9, j).Value = errorMsg
End If

Next j

'===========================================================
' エラーがあれば SQL 生成を中止
'===========================================================
If hasError Then
MsgBox "データに違反があります。9行目のエラー内容を確認してください。", vbExclamation
Exit Sub
End If

'===========================================================
' ここから先は SQL 生成処理（既存のロジックを呼び出す）
'===========================================================
Call CreateInsertSQL   ' ← 既に作成済みの SQL 生成サブルーチンを呼ぶ

End Sub