Find all and get offset value in new sheet(code error)

[sarao18592]

Hi,
I've currently written the code below and I'm trying to get it to run through the different sheets in the workbook but it will loop only through the first one and keep giving me values from the first one(hence causing it to crash). Could someone try helping me with it?

Sub FindAndExecute()

Dim Sh As Worksheet
Dim Loc As Range
Dim i,j As Integer

i = 5
For Each Sh In ThisWorkbook.Worksheets
    With Sh.UsedRange
    Set Loc = .Cells.Find(What:="Employee Code:-")
        If Not Loc Is Nothing Then
            Do Until Loc Is Nothing
                If Loc.Offset(0, 9).Value <> Loc.Offset(0, 23).Value Then
                Sheets("Result2").Cells(i, 1).Value = Loc.Offset(0, 9).Value
                Sheets("Result2").Cells(i, 2).Value = Loc.Offset(0, 23).Value
                j = 3
                Do
                Sheets("Result2").Cells(i, j).Value = Loc.Offset(3, j - 1).Value
                Sheets("Result2").Cells(i + 1, j).Value = Loc.Offset(4, j -
                1).Value
                j = j + 1
                Loop Until j > 35
                i = i + 3
                Else
                End If
            Set Loc = .FindNext(Loc)
            Loop
        End If
    End With
    Set Loc = Nothing
Next

End Sub

 

[mart37]

Your code don't stop after find the last occurrence. FIND.NEXT start again the search from beginning and shall never be NOTHING.

Maybe you can use this:

Sub FindAndExecute()
    Dim Sh As Worksheet
    Dim Loc As Range
    For Each Sh In ThisWorkbook.Worksheets
        With Sh.UsedRange
            Set Loc = .Cells.Find(What:="Employee Code:-")
            If Not Loc Is Nothing Then
                FirstCell = Loc.Address
                Do
.
.
.
                Set Loc = .FindNext(Loc)
                Loop While FirstCell <> Loc.Address
            End If
        End With
        Set Loc = Nothing
    Next
End Sub