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?