VBA-添加字符环路.findnext不退出循环

Question

此代码让我们更改一个表中的值，然后在另一个范围内寻找原始值（“oldcode”），如果有一个或多个出现'其他范围'，它用newcode替换所有旧代码。

从newcode带走字符时这工作完全，但是，即使添加一个字符newcode时，循环永远不会停止。例如，如果当前（oldcode）是“Test”，并且输入“tes”，则代码将激发，并且所有“test”都将更改为“tes”。如果我将“测试”更改为“测试1”，则全部更改为“测试1”，但即使C没有任何变化，循环仍继续运行。如果在这样做似乎没有帮助。

我还要提到oldcode不是直接“测试”，Oldcode实际上来自第1列，它concats“测试”和计数有多少人在那里，所以“测试1”。

任何帮助将不胜感激！

Private Sub worksheet_change(ByVal target As Range) 

Dim row As Integer 
Dim column As Integer 
Dim i As Integer 
Dim oldcode As String 
Dim newcode As String 
Dim IssueLogSheet As Worksheet 
Dim FailureModeTable As Range 
Dim max As Integer 


Set IssueLogSheet = Sheets("Issue Log") 
Set FailureModeTable = IssueLogSheet.Range("FMCODE") 

row = target.row 
column = target.column 



    If Not Intersect(target, FailureModeTable) Is Nothing And (target.column <> 1 Or target.column <> 4) Then 


     Application.EnableEvents = False 
     Application.Undo 
     oldcode = Cells(row, 1).Value 
     oldcode = WorksheetFunction.Proper(oldcode) 
     Application.Undo 
     Application.EnableEvents = True 
     MsgBox oldcode 


      With IssueLogSheet.Range("IssueLogFailureName") 
      Set c = .Find(oldcode, LookIn:=xlValues) 

       If Not c Is Nothing Then 
       newcode = Cells(row, 1).Value 
       newcode = WorksheetFunction.Proper(newcode) 


        Do 
         If c Is Nothing Then 
         Exit do 
         End If 
        c.Value = newcode 
        Set c = .FindNext(c) 
        Loop While Not c Is Nothing 


       End If 

      End With 

     End If 

End Sub 

Answer 1

添加

Dim firstAddress As String 

和改变循环如下：

With IssueLogSheet.Range("IssueLogFailureName") 
     Set c = .Find(oldcode, LookIn:=xlValues) 

     If Not c Is Nothing Then 
      firstAddress = c.Address '<--| store first occurrence address 
      newcode = WorksheetFunction.Proper(Cells(row, 1).Value) 
      Do 
       c.Value = newcode 
       Set c = .FindNext(c) 
      Loop While Not c Is Nothing And c.Address <> firstAddress '<--| exit should 'Find()' wrap back to first occurrence 
     End If 
    End With 

否则只是改变了循环如下

With IssueLogSheet.Range("IssueLogFailureName") 
     Set c = .Find(oldcode, LookIn:=xlValues, lookat:=xlWhole) '<--| impose a full match 

     If Not c Is Nothing Then 
      newcode = WorksheetFunction.Proper(Cells(row, 1).Value) 
      Do 
       c.Value = newcode 
       Set c = .FindNext(c) 
      Loop While Not c Is Nothing 
     End If 
    End With