循环影响使用 Excel VBA 的代码输出

Question

我想知道是否有人可以帮助我调试我的代码。我似乎无法弄清楚为什么当循环移动到第二行时，它会更改数组中的元素。

例如：如果数组"BookMarksToDelete"中有"Dog， Cat， Bird"，则第二个循环似乎想在下一行中使其成为"og， Cat， Bird"。

有没有更好的循环方法？

For Each rw In myRange.Rows
For Each cel In rw.Cells
For i = LBound(myArray) To UBound(myArray)
Set oCell = myRange.Find(What:=myArray(i), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, 
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If oCell Is Nothing Then
sTemp = sTemp & "," & myArray(i)
Else
Set oCell = Nothing
End If
Next i
sTemp = Mid(sTemp, 2)
If Not Len(Trim(sTemp)) = 0 Then
BookMarksToDelete = Split(sTemp, ",")
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(FlName)
For i = LBound(BookMarksToDelete) To UBound(BookMarksToDelete)
Set pRng = wdDoc.Bookmarks(BookMarksToDelete(i)).Range
pRng.MoveEnd wdParagraph, 2
pRng.Delete
Next i
End If
Set wdTable = wdDoc.Tables(1)
For i = LBound(BookMarksToDelete) To UBound(BookMarksToDelete)
For Each cell In myRangeRef
If InStr(1, cell.Value, BookMarksToDelete(i), vbTextCompare) Then 
aCell = cell.Offset(, -1).Value
stTemp = stTemp & "," & aCell
End If
Next cell
Next i
stTemp = Mid(stTemp, 2)
If Not Len(Trim(stTemp)) = 0 Then
ReturnsToDelete = Split(stTemp, ",")
For i = LBound(ReturnsToDelete) To UBound(ReturnsToDelete)
For j = wdTable.Rows.Count To 2 Step -1
If Left(wdTable.cell(j, 1).Range.Text, Len(wdTable.cell(j, 1).Range.Text) - 2) = ReturnsToDelete(i) Then wdTable.Rows(j).Delete
Next j
Next i
End If
With ThisWorkbook.Sheets("Investors Database")
firstName = .Range("B" & rw)
lastName = .Range("A" & rw)
titleName = lastName & ", " & firstName
End With
Set tRng = wdDoc.Bookmarks("TitlePageName").Range
tRng.Text = wdDoc.Bookmarks("TitlePageName").Range.Text & titleName
wdDoc.Bookmarks.Add "TitlePageName", tRng
d = "Information Up-To-Date as of " & Date
Set dRng = wdDoc.Bookmarks("TitlePageDate").Range
dRng.Text = wdDoc.Bookmarks("TitlePageDate").Range.Text & d
wdDoc.Bookmarks.Add "TitlePageDate", dRng
Set wRng = wdApp.ActiveDocument.Bookmarks("FundCommentary").Range
wRng.Collapse wdCollapseStart
wRng.InsertBreak wdPageBreak
Set sRng = wdApp.ActiveDocument.Bookmarks("Disclaimer").Range
sRng.Collapse wdCollapseStart
sRng.InsertBreak wdPageBreak
wdDoc.TablesOfContents(1).Update
wdDoc.Repaginate
With wdApp.ActiveDocument
.SaveAs2 "https://websitefolder//document.docx"
.Close
End With
wdApp.Visible = False
Next
Next

Answer 1

例如

：如果数组"BookMarksToDelete"中有Dog, Cat, Bird，则第二个循环似乎希望将其og, Cat, Bird下一行。

当循环第一次运行时，sTemp的值""，因此sTemp = sTemp & "," & myArray(i)在开头添加一个,，然后sTemp = Mid(sTemp, 2)从一开始就修剪,。

当代码第二次循环时，sTemp的值不是""而是下一行中的代码sTemp = Mid(sTemp, 2)第一个字母的印章。

选项 1：重置sTemp

在代码末尾添加行sTemp = ""

选项 2：如果您不想重置sTemp请检查第一个字符是否为,

将sTemp = Mid(sTemp, 2)替换为If Left(sTemp, 1) = "," Then sTemp = Mid(sTemp, 2)