If Next语句在VB中的第二个时间循环之后不起作用

Question

我很困惑为什么这个if语句在第二个循环后不起作用。我想选取以"0"开头的单元格；并提取"[]"之间的句子；。提取句子后，应将其作为一个列表放在另一张纸上。

此代码仅在InStr(Cells(i, 1), "[") > 0。但是，此后该测试失败。

我哪里错了？

Public rowCount As Integer

Sub Copy()
Dim startNum As Integer, endNum As Integer
Dim str As String
Dim e As Long
Dim le As Long

Worksheets("DataBase").Activate
rowCount = Cells(Rows.Count, 1).End(xlUp).Row 
Dim i As Long
Dim ExtractionStrings As String
Dim arr() As Variant

For i = 4 To rowCount
For e = 1 To rowCount
If InStr(Cells(i, 1), "[") > 0 Then
startNum = InStr(Cells(i, 1), "【")
endNum = InStr(startNum + 1, Cells(i, 1), "]")
If startNum <> 0 And endNum <> 0 Then
startNum = startNum + 1
ExtractionStrings = Mid(Cells(i, 1), startNum, endNum - startNum)
str = ExtractionStrings

Worksheets("Sheet1").Activate
Cells(i, 1) = str
Else: MsgBox ("Error")
End If
End If
Next e
Next i
End Sub

Answer 1

之所以会发生这种情况，是因为当第一次匹配在循环中发生时，您正在激活输出工作表"Worksheets("Sheet1(.Activate"，因此下一次迭代时，Instr评估将在输出工作表中进行。

工作表最好总是有一个变量，比如：

Dim myWsh 
Set myWsh  = Workbook("x").sheets(1)

因此，在进一步的代码中，您可以在工作表中引用项目，而无需激活它：

myWsh.cells(1,1).value = "my value"

请尝试以下操作，我已经添加了工作表变量并删除了内部循环(不知道这是为了什么(

Option Explicit
Public rowCount As Integer

Sub Copy()

Dim databaseWsh As Worksheet:  Set databaseWsh = ThisWorkbook.Worksheets("DataBase")
Dim outputWsh As Worksheet:  Set outputWsh = ThisWorkbook.Worksheets("Sheet1")

Dim rowCount As Long: rowCount = databaseWsh.Cells(databaseWsh.Rows.Count, 1).End(xlUp).Row
Dim outPutCount As Long: outPutCount = 1

Dim i As Long
For i = 1 To rowCount

Dim startNum As Integer: startNum = 0: Dim endNum As Integer: endNum = 0
If InStr(1, databaseWsh.Cells(i, 1), "[") > 0 Then
startNum = InStr(1, databaseWsh.Cells(i, 1), "[")
endNum = InStr(startNum + 1, databaseWsh.Cells(i, 1), "]")
If startNum <> 0 And endNum <> 0 Then
Dim ExtractionStrings As String
startNum = startNum + 1
ExtractionStrings = Mid(databaseWsh.Cells(i, 1), startNum, endNum - startNum)
outputWsh.Cells(outPutCount, 1) = ExtractionStrings
outPutCount = outPutCount + 1
Else: MsgBox ("Error")
End If
End If
Next i

End Sub