Loop inside loop. Stop second loop if condition is met

Question

I've written below code. Its a loop inside loop. First loop runs to search 1st range's value (sheet1) in all second loop's all ranges (sheet2). I want second loop to stop if condition is met and proceed with next value.

I'm using "Exit For" to stop 2nd loop if condition is met. but the Problem is....

When First loop = 2, second loop is also 2 and condition is met, second loop stops. First loop becomes = 3 but second loop still remains = 2 which is causing the mismatch and failure to all. Please help.

    For I = 2 To REND        
         For P = 2 To ENDROW
          If Range("D" & I) = D.Range("c" & P) Then
            If Range("H" & I) = D.Range("F" & P) Then
                Range("A" & I) = "MATCHED"
                Exit For

            Else
                Range("A" & I) = "DIFFERENCE OF " & Range("G" & I) - D.Range("F" & P)
            End If
          Else
            Range("A" & I) = "AP NOT FOUND"
          End If
         Next P
Next I

Where declarations are:

Dim D As Worksheet, F As Worksheet, R As WorksheetDim X As String
Dim I As Integer, ENDROW As Integer, FEND As Integer
Dim P As Integer, REND As      Integer, L As Integer 
Set D = ActiveWorkbook.Sheets("DWAC")
Set F = ActiveWorkbook.Sheets("FPS")
Set R = ActiveWorkbook.Sheets("RAW")
ENDROW = D.Range("C2").End(xlDown).Row
FEND = F.Range("C2").End(xlDown).Row
REND = R.Range("C2").End(xlDown).Row

Answer 1

From your question, I assume there will be only one result in Sheets("DWAC") column C. With that in mind, we can use find instead of looping until you get a match.

If we cleaned up your Dim statements(there is one sheet not being used in the code & Row variables should be long.

    Dim D As Worksheet, R As Worksheet
    Dim ENDROW As Long, REND As Long
    Dim rng1 As Range, rng2 As Range
    Dim c As Range, FndC As Range

Now of course set the worksheets

Set D = Sheets("DWAC")
Set R = Sheets("RAW")

And then find the last rows and set the ranges.

This will find the last row in column c Sheets("DWAC"), the set range is where we want to find the original value from Sheets("RAW")

 With D
    ENDROW = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set rng2 = .Range("C2:C" & ENDROW) 'find value in column C sheet D
End With

Then set the loop range from sheets R,this is the range we will be looping through.

With R
        REND = .Cells(.Rows.Count, "D").End(xlUp).Row
        Set rng1 = .Range("D2:D" & REND) 'Loop through column D in sheet R
    End With

Now we can start the loop. c will loop through rng1 and find itself in rng2, FndC will be the range if the c is found.

 For Each c In rng1.Cells
        Set FndC = rng2.Find(what:=c, lookat:=xlWhole)

If c in found then do something.

   If Not FndC Is Nothing Then

            If c.Offset(, 4) = FndC.Offset(, 3) Then
                c.Offset(, -3) = "Match"
            Else
                c.Offset(, -3) = "DIFFERENCE OF " & c.Offset(, 3) - FndC.Offset(, 3)
            End If
        Else: c.Offset(, -3) = "Not Found"

        End If
    Next c

If I have the Offsets in the wrong order, you can edit to your requirements. This is what the offsets represent.

c.Offset(, 4)'=Column H Sheet "R"
FndC.Offset(, 3)'=Column F Sheet "D"
c.Offset(, -3)'=Column A Sheet "R"
c.Offset(, 3)'=Column G Sheet "R"

Here's is the complete code.

Sub FindAndStuff()
    Dim D As Worksheet, R As Worksheet
    Dim ENDROW As Long, REND As Long
    Dim rng1 As Range, rng2 As Range
    Dim c As Range, FndC As Range

    Set D = Sheets("DWAC")
    Set R = Sheets("RAW")

    With D
        ENDROW = .Cells(.Rows.Count, "C").End(xlUp).Row
        Set rng2 = .Range("C2:C" & ENDROW) 'find value in column C sheet D
    End With


    With R
        REND = .Cells(.Rows.Count, "D").End(xlUp).Row
        Set rng1 = .Range("D2:D" & REND) 'Loop through column D in sheet R
    End With

    For Each c In rng1.Cells
        Set FndC = rng2.Find(what:=c, lookat:=xlWhole)

        If Not FndC Is Nothing Then

            If c.Offset(, 4) = FndC.Offset(, 3) Then
                c.Offset(, -3) = "Match"
            Else
                c.Offset(, -3) = "DIFFERENCE OF " & c.Offset(, 3) - FndC.Offset(, 3)
            End If
        Else: c.Offset(, -3) = "Not Found"

        End If
    Next c

End Sub