Excel VBA Macro Finding data in a column, stuck in 'Do' loop if data not found

48 Views Asked by At

your textwhen I run the below code, when it find the data it comes out of the 'do' loop but when your textdon't get the data then it searching next again and again and never comes out of the 'do' loop.

Dim Found As Range, Firstfound As String
    Dim rownum As Variant
    Dim rngSearch As Range
    Dim BRM_lastRowNum As Integer
    Dim Run_JobType As Integer
    Dim Run_Area As Integer
    Dim Run_EngRow As Integer
    Dim count As Integer
    Dim Criteria As Variant
    Dim Criteria2 As Variant
    Dim Criteria3 As Variant
   
   BRM_lastJobRow = Sheets("BRM").Range("A" & Rows.count).End(xlUp).Row
   BRM_lastAreaRow = Sheets("BRM").Range("B" & Rows.count).End(xlUp).Row
   BRM_lastEngRow = Sheets("BRM").Range("C" & Rows.count).End(xlUp).Row
   Start_Date = Sheets("Dashboard").Range("C" & 2).Value
   End_date = Sheets("Dashboard").Range("C" & 3).Value

   Run_JobType = 2
   
   For Run_JobType = 2 To BRM_lastJobRow
       Criteria3 = Sheets("BRM").Range("A" & Run_JobType).Value
       
    Run_Area = 2
    For Run_Area = 2 To BRM_lastAreaRow
       Criteria2 = Sheets("BRM").Range("B" & Run_Area).Value
        total_eng = 0
       
    Run_EngRow = 2
    For Run_EngRow = 2 To BRM_lastEngRow
       Criteria = Sheets("BRM").Range("C" & Run_EngRow).Value
       Set Found = Nothing
       
    Set rngSearch = Sheets("Master Data").Range("L:L")
    Set Found = rngSearch.Find(What:=Criteria, _
                               LookIn:=xlValues, _
                               LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False)
      
    If Not Found Is Nothing Then
             
         Do
           Firstfound = Found.Address
           rownum = Range(Firstfound).Row
            
            If Sheets("Master Data").Range("H" & rownum).Value = Criteria2 And _
               Sheets("Master Data").Range("I" & rownum).Value = Criteria3 And _
               Sheets("Master Data").Range("F" & 2).Value >= Start_Date And _
               Start_Date = Sheets("Dashboard").Range("F" & 3).Value <= End_date Then
             
             total_eng = total_eng + 1
             EngName = Sheets("Master Data").Range("L" & rownum).Value
             Exit Do 'Match found
          Else
            
           Set Found = rngSearch.FindNext(After:=Found)
           If Found.Address = Firstfound Then Set Found = Nothing 'here is the issue
 
        End If
           
        Loop Until Found Is Nothing
          
    If Not Found Is Nothing Then
       ' Application.Goto Found.EntireRow
    Else
        MsgBox "Nothing matched all four criteria. ", , "No Match Found"
    
   End If
   End If
   
   Next
   Next
   Next
End Sub

'when I run the below code, when it find the data it comes out of the 'do' loop but when don't get 'the data then it searching next again and again and never comes out of the 'do' loop.

'could anybody help me to sort this issue?

0

There are 0 best solutions below