时间戳

首页 > 解决方案 > 如何退出“do while”语句

excel - 如何退出“do while”语句

问题描述

Sub FindValue()

Dim firstAddress As String
Dim Expense As String
Dim rRange As Range
Dim FirstrngFnd As Range
Dim x As Integer
Dim y As Integer
Dim z As Integer

Workbooks.Open FileName:= _
    "D:\My Documents\Excel Files\AA Credit Card\1008.xlsx"        'A worksheet with several columns - Column G (Column 7) is a list of "expenses".  
Workbooks.Open FileName:= _
    "D:\My Documents\Excel Files\Credit Card Analysis.xlsx"       'A worksheet with "expenses" listed in random order in Column A (Column1)
Windows("Credit Card Analysis.xlsx").Activate
ActiveWindow.Panes(1).Activate

Expense = Application.InputBox("Select the required expense from Column A")     'Pick an "expense" from a list in Column A

'Open the first file in the first folder
Windows("1008.xlsx").Activate
ActiveWindow.Panes(1).Activate

'Establish the first and last rows in Column A (which contain a list of dates by increasing date): required to establish the search range
Set rRange = Range("A1", Cells(Rows.Count, 1).End(xlUp))
    For Each rCell In rRange
        If IsDate(rCell) Then
            rCell(2, 1).Select
    Exit For
        End If
    Next rCell

x = (ActiveCell.Row - 1)                          'This finds the FIRST row in the file with a date in it
y = Cells(Rows.Count, 1).End(xlUp).Row            'This finds the LAST row in the file with a date in it
My_Workbook = ActiveWorkbook.Name                                               'Holds the current Workbook name

'Move over to the "analysis" column (G) (Column 7)
With Worksheets(1).Range(Cells(x, 7), Cells(y, 7))
    Set FirstrngFnd = .Find(Expense, LookIn:=xlValues, LookAt:=xlPart)          'Finds the first occurrence of "expense"
        If Not FirstrngFnd Is Nothing Then                                      'if the "expense" isn't listed then goto Line400
            firstAddress = FirstrngFnd.Address
        Do           'DO WHATEVER IS REQUIRED IN THIS SECTION: FROM "DO" TO "Set FirstrngFnd = .FindNext(FirstrngFnd)"
            z = FirstrngFnd.Row
            FirstrngFnd.Value = "Mike"        'IF YOU OMIT THIS LINE THEN ALL THE VALUES REMAIN AT "expense", SO THE PROGRAM JUST GOES ROUND (AND ROUND) AGAIN.
            Set FirstrngFnd = .FindNext(FirstrngFnd)
        Loop While Not FirstrngFnd Is Nothing
    End If
End With
End Sub

如果我删除 "FirstrngFnd.Value = "Mike" 行，那么 Golumn G 中的值永远不会改变，所以当程序到达文件末尾时，它会再次循环。

我怎样才能让它识别出它已经通过文件一次，然后继续前进？

标签： excelvba

解决方案

请修改：

Loop While Not FirstrngFnd Is Nothing

这样：

Loop While Not FirstrngFnd Is Nothing And FirstrngFnd.Address <> firstAddress

推荐阅读