变量"i"未定义，但它在某个时间点工作

Question

我一直得到一个变量未定义的错误"i〃；在下面的代码中。我用"；这就是问题区域"；。我最近才开始有这个问题。问题是，代码一直在工作，但后来它开始产生问题，现在它已经完全停止了工作。

我基本上从一个excel文件开始，其中A、B、C、D、E、F列填充了内容，然后VBA的第一部分将其转换为模板格式，文件必须使用该格式才能上传到数据库中。问题片段之前：

• 列C移到列G
• D列移到K列
• E列移到O列
• F列移到S列

起初，代码运行得很完美。事实上，我拍了它的视频并向同事们展示，因为我非常兴奋，因为这是我自己写的第一个代码(仍在学习(。不幸的是，有一天，它开始打破问题代码段停止正确计算行数的地方，只在第一行插入1和True，然后停止。我又试了几次代码，现在它只会弹出一个弹出窗口，上面写着"；变量未定义"；对于"；i"；。

有人想过它为什么会这样坏吗？

Sub Template_Setup()
'PLEASE READ IMPORTANT NOTE:
'IMPORTANT NOTE: ItemName must be Column_A and row 1, Content must be Column_Band row 1, AnswerA is Column_Cand row 1,AnswerB is Column_D and row 1, AnswerC is Column_E and row 1,AnswerD is Column_F and row 1
'No row 1 that contains labels for upload

Dim Col As String
Dim lastrow As Integer
Dim ws As Worksheet
Set ws = ActiveSheet
'Column Insert Code
Columns("C:E").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Columns("G:I").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow

Columns("K:M").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Columns("O:Q").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
'
'Insert values before adding row 1
'Autofills of columns
Range("A1").Value = "RadioButton"
With Range("B:B")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, -1).FillDown
End With
End With
Range("F1").Value = "Active"
With Range("C:C")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 3).FillDown
End With
End With
Range("L1").Value = "0"
With Range("K:K")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 1).FillDown
End With
End With
Range("T1").Value = "0"
With Range("S:S")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 1).FillDown
End With
End With
Range("H1").Value = "0"
With Range("G:G")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 1).FillDown
End With
End With
Range("P1").Value = "0"
With Range("O:O")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 1).FillDown
End With
End With
Range("J1").Value = "1"
With Range("G:G")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 3).FillDown
End With
End With

Range("N1").Value = "2"
With Range("K:K")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 3).FillDown
End With
End With
Range("R1").Value = "3"
With Range("O:O")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 3).FillDown
End With
End With

Range("V1").Value = "4"
With Range("S:S")
With Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 3).FillDown
End With
End With
'Correct Answers Followed by 1's and True
'THIS IS THE PROBLEM AREA
For i = 1 To 4
Select Case i
Case 1
Col = "G"
Case 2
Col = "K"
Case 3
Col = "O"
Case 4
Col = "S"
End Select
lastrow = Sheet1.Range(Col & Rows.Count).End(xlUp).Row
For r = 1 To lastrow
If ws.Range(Col & r).Interior.Pattern <> xlNone Then
ws.Range(Col & r).Offset(, 1).Value = 1
ws.Range(Col & r).Offset(, 2).Value = "TRUE"
End If
Next
Next

'Insert 1 Row Above Row 1
'
Rows(1).Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow
'Insert Row1 Template headers
Range("A1").Value = "ItemType"
Range("B1").Value = "Name"
Range("C1").Value = "Content"
Range("D1").Value = "RightAnchor"
Range("E1").Value = "IsRequired"
Range("F1").Value = "ItemStatus"
Range("G1").Value = "Answer"
Range("H1").Value = "PointValue"
Range("I1").Value = "Correct"
Range("J1").Value = "ExportValue"
Range("K1").Value = "Answer"
Range("L1").Value = "PointValue"
Range("M1").Value = "Correct"
Range("N1").Value = "ExportValue"
Range("O1").Value = "Answer"
Range("P1").Value = "PointValue"
Range("Q1").Value = "Correct"
Range("R1").Value = "ExportValue"
Range("S1").Value = "Answer"
Range("T1").Value = "PointValue"
Range("U1").Value = "Correct"
Range("V1").Value = "ExportValue"
Range("W1").Value = "MinValue"
Range("X1").Value = "MaxValue"
Range("Y1").Value = "StepValue"
Range("Z1").Value = "InitialValue"
Range("AA1").Value = "LeftAnchor"
Range("AB1").Value = "SectionNav"
Range("AC1").Value = "PageNav"
Range("AD1").Value = "QuestionNo"
Range("AE1").Value = "StartNo"
Range("AF1").Value = "NoFormat"
Range("AG1").Value = "AnswerFormat"

End Sub

Answer 1

在代码的顶部，与其他声明一起，添加：

Dim i As Long
Dim r As Long

这就是@Tragamor评论的意思。

@GSerg的评论告诉你为什么会突然发生这种事。您将在模块的顶部找到单词Option Explicit。它一定是在代码停止工作之前添加的。始终使用Option Explicit是一种很好的做法，因为这意味着有来声明(Dim(变量，这可以防止意外的错误，而且通常很难发现错误。